DBMS is a practical DBMS topic that becomes clear when you connect the definition to a small working example.
Use this page to understand what happens, why it happens, how to verify it, and what mistake usually breaks the concept.
After reading, practice DBMS with a normal case, a boundary case, and a broken case so the idea becomes usable instead of memorized.
DBMS Transactions ACID Properties should be studied as a practical database design lesson, not as a label. Start by naming the input, the rule that changes the input, and the result a learner should be able to predict after reading the page.
In the dbms > transactions page, the notes should connect the definition with a working scenario, a mistake that beginners actually make, and the exact check that proves the fix. That makes the topic useful for coding, debugging, and interview revision.
A transaction is a logical unit of database work that must be treated as one complete action. It may contain one SQL statement or many SQL statements, but from the DBMS point of view it should either complete fully or leave the database unchanged.
Transactions are needed because real database operations often involve multiple related changes. If one part succeeds and another part fails, the database may enter an incorrect state. Transaction management protects data correctness during failures, concurrent access, and complex updates.
The classic example is a money transfer from Account A to Account B. The database must debit one account and credit the other account. If only one operation is completed, the total money becomes incorrect.
START TRANSACTION;
UPDATE accounts
SET balance = balance - 500
WHERE account_id = 101;
UPDATE accounts
SET balance = balance + 500
WHERE account_id = 202;
INSERT INTO transfer_log (from_account, to_account, amount, transfer_time)
VALUES (101, 202, 500, CURRENT_TIMESTAMP);
COMMIT;
-- If any statement fails before COMMIT:
-- ROLLBACK;A reliable transaction must follow the ACID properties: Atomicity, Consistency, Isolation, and Durability.
| Property | Meaning | Example |
|---|---|---|
| Atomicity | All operations in the transaction are completed, or none of them are applied. | Debit and credit both happen, or both are rolled back. |
| Consistency | The database moves from one valid state to another valid state. | Balance cannot become negative if a CHECK constraint or business rule prevents it. |
| Isolation | Concurrent transactions should not incorrectly interfere with one another. | Two transfers should not overwrite each other's balance update. |
| Durability | Once committed, changes survive crashes, power failures, or restarts. | A committed transfer remains saved after the database server restarts. |
Transaction Control Language commands are used to begin, save, commit, and roll back transaction work. The exact command names can vary slightly across DBMS products, but the ideas are the same.
| Command | Purpose |
|---|---|
| START TRANSACTION or BEGIN | Marks the start of a transaction. |
| COMMIT | Permanently saves all changes made in the transaction. |
| ROLLBACK | Cancels all uncommitted changes made in the transaction. |
| SAVEPOINT | Creates a named point inside a transaction. |
| ROLLBACK TO SAVEPOINT | Cancels changes after a savepoint without cancelling the whole transaction. |
START TRANSACTION;
UPDATE products
SET stock = stock - 2
WHERE product_id = 10;
SAVEPOINT after_stock_update;
INSERT INTO orders (customer_id, order_date)
VALUES (501, CURRENT_DATE);
-- Suppose order item insertion fails.
ROLLBACK TO SAVEPOINT after_stock_update;
-- Continue with corrected statements, then save permanently.
COMMIT;A transaction passes through several states during execution. These states help explain what the DBMS should do after normal completion, statement failure, or system crash.
| Point | COMMIT | ROLLBACK |
|---|---|---|
| Meaning | Accepts and saves the transaction changes. | Cancels uncommitted transaction changes. |
| Effect on data | Changes become visible according to isolation rules. | Database returns to the state before the transaction or savepoint. |
| Recovery | Committed changes may need REDO after a crash. | Uncommitted changes may need UNDO after a crash. |
| Common use | Order placed, payment posted, record approved. | Validation failed, deadlock occurred, user cancelled operation. |
When multiple transactions execute at the same time, their read and write operations may be interleaved. This interleaved order is called a schedule.
| Schedule Type | Meaning |
|---|---|
| Serial Schedule | One transaction completes before the next transaction starts. It is simple and correct but does not use concurrency well. |
| Non-Serial Schedule | Operations of different transactions are interleaved. It improves performance but may cause conflicts. |
| Serializable Schedule | A non-serial schedule that gives the same final result as some serial schedule. |
| Recoverable Schedule | If T2 reads data written by T1, then T2 commits only after T1 commits. |
| Cascadeless Schedule | Transactions read only committed data, so one rollback does not force many other rollbacks. |
In theory, transaction schedules are often described using simple operations such as R1(X) and W2(X).
Serial schedule:
R1(A), W1(A), R1(B), W1(B), C1,
R2(A), W2(A), C2
Non-serial schedule:
R1(A), R2(A), W1(A), W2(A), C1, C2
The non-serial schedule may be unsafe if W2(A) overwrites W1(A).Serializability is the correctness goal for concurrent transactions. A schedule is serializable if its result is equivalent to a serial execution of the same transactions.
Two operations conflict if they belong to different transactions, access the same data item, and at least one operation is a write.
| Type | Meaning | Important Point |
|---|---|---|
| Conflict Serializability | A schedule can be converted to a serial schedule by swapping non-conflicting operations. | Tested using a precedence graph. |
| View Serializability | A schedule has the same read-from relationships and final writes as a serial schedule. | More general than conflict serializability but harder to test. |
Without proper transaction isolation, concurrent transactions may produce incorrect results. These problems are also called transaction anomalies.
| Problem | Description | Example |
|---|---|---|
| Lost Update | Two transactions update the same value, and one update overwrites the other. | T1 and T2 both read stock 10. T1 writes 8, T2 writes 7. One change is lost. |
| Dirty Read | A transaction reads data written by another transaction that has not committed. | T2 reads salary changed by T1, but T1 later rolls back. |
| Unrepeatable Read | A transaction reads the same row twice and gets different values because another transaction updated it. | T1 reads balance 5000, T2 updates it to 4500, T1 reads again and sees 4500. |
| Phantom Read | A transaction reruns a condition-based query and sees new rows inserted by another transaction. | T1 counts 5 orders, T2 inserts another matching order, T1 counts 6. |
| Incorrect Summary | A report reads data while another transaction is updating related rows. | A total balance report reads some old rows and some new rows during a transfer. |
Isolation levels control how much one transaction can see of another transaction's work. Higher isolation provides stronger correctness but may reduce concurrency.
| Isolation Level | Dirty Read | Unrepeatable Read | Phantom Read | Use Case |
|---|---|---|---|---|
| READ UNCOMMITTED | Possible | Possible | Possible | Fast reports where temporary inconsistency is acceptable. |
| READ COMMITTED | Prevented | Possible | Possible | Common default in many DBMS products. |
| REPEATABLE READ | Prevented | Prevented | May be possible, depending on DBMS implementation. | Transactions that must re-read the same rows reliably. |
| SERIALIZABLE | Prevented | Prevented | Prevented | Strict financial, inventory, and rule-sensitive operations. |
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT balance
FROM accounts
WHERE account_id = 101;
UPDATE accounts
SET balance = balance - 500
WHERE account_id = 101;
COMMIT;Locking is a common way to control concurrent transactions. A transaction must acquire a suitable lock before reading or writing a data item.
Two-Phase Locking (2PL) is a protocol that guarantees conflict serializability. It has two phases.
| Lock Type | Also Called | Purpose |
|---|---|---|
| Shared Lock | Read lock | Allows multiple transactions to read the same data item. |
| Exclusive Lock | Write lock | Allows one transaction to modify the data item and blocks other access. |
| Intent Lock | Hierarchy lock | Shows that a transaction plans to lock lower-level objects such as rows inside a table. |
Not every DBMS relies only on traditional locking. Many systems also use timestamps or multiple row versions to improve concurrency.
| Technique | How it Works | Strength | Limitation |
|---|---|---|---|
| Timestamp Ordering | Each transaction receives a timestamp, and conflicting operations are ordered using those timestamps. | No deadlock because transactions do not wait in cycles. | Older transactions may be aborted and restarted. |
| MVCC | The DBMS keeps multiple versions of rows so readers can see a consistent snapshot while writers continue. | Readers and writers block each other less often. | Old row versions must be cleaned up later. |
| Optimistic Control | Transactions execute first and conflicts are checked before commit. | Works well when conflicts are rare. | High-conflict workloads may cause many rollbacks. |
A deadlock occurs when transactions wait for each other in a cycle. None of them can proceed unless the DBMS breaks the cycle.
| Technique | Meaning |
|---|---|
| Deadlock Prevention | Design rules so deadlocks cannot occur, such as lock ordering or timestamp-based schemes. |
| Deadlock Detection | Build a wait-for graph and look for cycles. |
| Deadlock Recovery | Abort one transaction, release its locks, and allow other transactions to continue. |
| Timeout | Abort a transaction if it waits too long for a lock. |
T1 locks Account A.
T2 locks Account B.
T1 waits for Account B.
T2 waits for Account A.
Result: circular wait, so a deadlock occurs.Recoverability is about whether a schedule can be safely recovered if a transaction fails. This matters when one transaction reads data written by another transaction.
| Schedule Property | Meaning | Quality |
|---|---|---|
| Recoverable | A transaction commits only after the transaction it read from has committed. | Acceptable minimum requirement. |
| Cascadeless | Transactions read only committed data. | Better because rollbacks do not cascade. |
| Strict | A transaction cannot read or write a data item until the last transaction that wrote it has committed or aborted. | Best for easy recovery. |
Recovery ensures that ACID properties are preserved after transaction failure, system crash, or media failure. The DBMS commonly uses logs to recover safely.
<START T1>
<T1, Account101, old=5000, new=4500>
<T1, Account202, old=3000, new=3500>
<COMMIT T1>
If the system crashes after COMMIT, the DBMS can REDO T1.
If the system crashes before COMMIT, the DBMS can UNDO T1.Many DBMS tools run in autocommit mode by default. In autocommit mode, each individual SQL statement is automatically committed if it succeeds. For multi-step business operations, disable autocommit or explicitly start a transaction.
| Mode | Behavior |
|---|---|
| Autocommit ON | Every statement is its own transaction. |
| Autocommit OFF | Changes remain pending until COMMIT or ROLLBACK. |
| Explicit transaction | The developer controls the transaction boundary using START, COMMIT, and ROLLBACK. |
| Mistake | Problem | Better Approach |
|---|---|---|
| Forgetting WHERE in an update | Too many rows may be changed. | Preview with SELECT and use key-based conditions. |
| Keeping transactions open too long | Locks remain active and block other users. | Do only required database work inside the transaction. |
| Using very high isolation everywhere | Concurrency and performance may drop. | Use SERIALIZABLE only where strict correctness requires it. |
| Ignoring rollback paths | Partial changes or locked resources may remain during errors. | Use try-catch or equivalent error handling in application code. |
A transaction is a sequence of database operations treated as one logical unit of work. It should either commit completely or roll back completely.
ACID properties ensure that transactions remain correct, reliable, isolated from harmful interference, and durable after commit.
COMMIT permanently saves transaction changes, while ROLLBACK cancels uncommitted changes and restores the previous consistent state.
A serializable schedule is a concurrent schedule that produces the same final result as some serial order of the transactions.
A dirty read happens when a transaction reads uncommitted data written by another transaction. If the writer rolls back, the reader has used invalid data.
A deadlock happens when transactions wait for each other in a circular chain, so none of them can continue until the DBMS aborts one transaction or resolves the wait.
DBMS should be learned as a practical DBMS skill, not only as a definition. Start by asking what problem the topic solves, what input or state it receives, what rule it applies, and what visible result proves it worked.
A strong explanation of DBMS includes the normal case, a boundary case, and a failure case. When you practice, write down the before-state, the operation, the after-state, and the reason the result changed.
This lesson was expanded because the audit reported: limited checklist/practice/mistake/FAQ notes . The added notes below focus on clearer explanation, more examples, and concrete practice so the topic is easier to understand from the page itself.
Imagine you are adding DBMS to a small learning project. The first step is to choose the smallest scenario that still shows the main idea. Avoid starting with a large production design; it hides the concept behind too many details.
Next, isolate the moving parts. Name the input, the rule, the output, and the possible error. This habit makes the topic easier to debug because you can see whether the problem is caused by bad data, wrong configuration, incorrect syntax, timing, permissions, or misunderstanding of the rule.
Finally, compare two versions: one correct version and one intentionally broken version. The broken version is valuable because it teaches you how the topic fails in real work, which is usually what interviews and debugging tasks test.
CREATE TABLE lesson_dbms (
id INT PRIMARY KEY,
description VARCHAR(120),
amount DECIMAL(10,2),
status VARCHAR(20)
);
INSERT INTO lesson_dbms VALUES
(1, 'DBMS normal case', 1000.00, 'active'),
(2, 'DBMS boundary case', 0.00, 'review');
SELECT * FROM lesson_dbms;BEGIN;
UPDATE lesson_dbms
SET status = 'checked'
WHERE amount >= 0;
SELECT status, COUNT(*) AS rows_seen
FROM lesson_dbms
GROUP BY status;
ROLLBACK;
-- Explanation: ROLLBACK lets you test the concept safely before committing changes.Memorizing DBMS as a definition only.
Pair the definition with a small working example and a failure example.
Copying syntax without checking the state before and after.
Write the input state, apply the rule, then inspect the output state.
Ignoring the error path for DBMS.
Create one intentionally broken version and document the symptom and fix.
Memorizing DBMS Transactions ACID Properties without the situation where it is useful.
Connect DBMS Transactions ACID Properties to a concrete database design task.
Understand the problem it solves, the input or state it works on, and the visible result that proves the concept is working.
Use one tiny correct example, one boundary example, and one broken example. Compare the output or state after each change.
They often memorize the term without tracing the behavior. Tracing makes the rule easier to remember and debug.
Remember the problem it solves in database design, then attach the syntax or steps to that problem.
Explore 500+ free tutorials across 20+ languages and frameworks.