DBMS Notes
A transaction is a logical unit of work consisting of one or more database operations (reads and writes) that must execute as a single, indivisible unit....
What is a Transaction?
A transaction is a logical unit of work consisting of one or more database operations (reads and writes) that must execute as a single, indivisible unit. The fundamental guarantee is: either ALL operations within the transaction complete successfully (COMMIT), or NONE of them take effect (ROLLBACK). There is no in-between state visible to other users.
ACID Properties
| Property | Meaning | Enforcement Mechanism |
|---|---|---|
| Atomicity | All or nothing — complete transaction or no effect | Recovery system (undo logs) |
| Consistency | Database moves from one valid state to another | Integrity constraints + application logic |
| Isolation | Concurrent transactions don't interfere | Concurrency control (locks, timestamps) |
| Durability | Once committed, changes survive crashes | Write-ahead logging, checkpoints |
Atomicity in Detail
BEGIN TRANSACTION;
UPDATE Account SET balance = balance - 5000 WHERE acc_id = 'A';
UPDATE Account SET balance = balance + 5000 WHERE acc_id = 'B';
-- If ANYTHING goes wrong before this point:
-- Both updates are rolled back automatically
COMMIT;Consistency in Detail
The database must satisfy all integrity constraints before and after the transaction:
- Sum of all account balances remains constant (business rule)
- No account goes below zero (CHECK constraint)
- Foreign keys remain valid
Isolation in Detail
Even though T1 and T2 run concurrently, the result must be equivalent to running them one after the other (serial execution). Isolation levels provide different guarantees:
| Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible |
| Read Committed | Prevented | Possible | Possible |
| Repeatable Read | Prevented | Prevented | Possible |
| Serializable | Prevented | Prevented | Prevented |
Durability in Detail
Once the DBMS returns "COMMIT successful," the data is guaranteed to persist even if the power fails one millisecond later. This is achieved through:
- Write-ahead logging (WAL): log records written to disk before data
- Checkpoints: periodic flushing of dirty pages
- Transaction logs on separate physical disk for safety
Transaction Operations
-- Start a transaction
BEGIN TRANSACTION; -- or START TRANSACTION;
-- Perform operations
INSERT INTO Orders VALUES (1001, 'C1', '2026-06-14', 2500);
UPDATE Inventory SET quantity = quantity - 1 WHERE product_id = 'P1';
-- End successfully
COMMIT;
-- OR end unsuccessfully (undo all changes since BEGIN)
ROLLBACK;Savepoints (Partial Rollback)
BEGIN TRANSACTION;
INSERT INTO Orders VALUES (1001, 'C1', '2026-06-14', 2500);
SAVEPOINT sp1;
UPDATE Inventory SET quantity = quantity - 1 WHERE product_id = 'P1';
-- Oops, wrong product!
ROLLBACK TO sp1; -- undoes only the UPDATE, keeps the INSERT
UPDATE Inventory SET quantity = quantity - 1 WHERE product_id = 'P2';
COMMIT;Transaction States
A transaction progresses through well-defined states:
| Active | Executing read/write operations |
| Partially Committed | Final statement executed, waiting for flush to disk |
| Committed | All changes permanently saved (durable) |
| Failed | Error detected, cannot proceed |
| Aborted | All changes undone (rolled back) |
After abort, the system may:
- Restart the transaction (if failure was due to concurrency conflict)
- Kill the transaction (if failure was due to application logic error)
Schedule and Serializability
A schedule is the chronological order of operations from multiple concurrent transactions. A schedule is serializable if its outcome is equivalent to some serial (one-at-a-time) execution.
Serial Schedule (always correct but slow)
T1: R(A) W(A) R(B) W(B) | T2: R(A) W(A) R(C) W(C)
Concurrent Schedule (must verify serializability)
T1: R(A) W(A) R(B) W(B)
T2: R(A) W(A) R(C) W(C)
Practical Example — E-Commerce Order
BEGIN TRANSACTION;
-- Check inventory
SELECT quantity INTO @qty FROM Products WHERE id = 101;
IF @qty < 1 THEN
ROLLBACK; -- insufficient stock
ELSE
-- Place order
INSERT INTO Orders(customer_id, product_id, amount)
VALUES (501, 101, 1);
-- Reduce inventory
UPDATE Products SET quantity = quantity - 1 WHERE id = 101;
-- Charge payment
UPDATE Customers SET balance = balance - 999 WHERE id = 501;
COMMIT;
END IF;This transaction ensures that an order is placed ONLY if inventory exists, and all three tables are updated atomically.
Summary
Transaction management is the foundation of database reliability. Through ACID properties, a DBMS guarantees that concurrent operations never corrupt data and that committed changes survive any failure. Understanding transactions is essential for building correct, reliable applications — every database interaction in production should be wrapped in appropriate transaction boundaries.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Transaction Management.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this Database Management Systems (DBMS) topic.
Search Terms
dbms, database management systems (dbms), unit, transaction, management, transaction management
Related Database Management Systems (DBMS) Topics