Transactions and ACID Properties

A transaction is a logical unit of work, a sequence of operations that must be executed completely or not at all.

Property	Meaning	Ensured by
Atomicity	All or nothing	Recovery manager (undo logs)
Consistency	Database moves from one valid state to another	Constraints + programmer
Isolation	Concurrent transactions don't interfere	Concurrency control
Durability	Committed changes survive crashes	Recovery manager (redo logs)

SQL

START TRANSACTION;
UPDATE account SET balance = balance - 1000 WHERE acc_no = 101;
UPDATE account SET balance = balance + 1000 WHERE acc_no = 202;
COMMIT;   -- or ROLLBACK; to undo both

Transaction States

Text

Active → Partially Committed → Committed → Terminated
   │               │
   └──→ Failed ←───┘ → Aborted (rolled back) → Terminated

Concurrency Problems

Lost update (W-W conflict)
Dirty read: reading uncommitted data (W-R)
Unrepeatable read (R-W)
Phantom read: new rows appear in a repeated query

Track your progress through the DBMS course

← Prev Next →