Transactions & ACID
The problem
Some operations require multiple related writes to all succeed or all fail together — transferring money means debiting one account and crediting another, and a crash or concurrent conflict between those two writes must never leave the data in a half-finished, contradictory state.
Why now
Concurrency and synchronization already established that shared mutable state accessed by multiple actors at once produces race conditions; transactions are the database's answer to that exact problem, providing guarantees (ACID) that let concurrent, potentially-interrupted operations behave as if they ran safely, one at a time.
Mental model
ACID is four separate promises bundled together: Atomicity (all-or-nothing, no half-finished writes), Consistency (rules like constraints are never violated), Isolation (concurrent transactions don't see each other's half-finished work, addressing the exact race-condition risk from concurrency-and-synchronization), and Durability (once confirmed, a write survives a crash). A transaction is a boundary you draw around a set of operations and ask the database to keep all four promises for.
Requires
- The Relational Model & SQLDatabase Engineering
Transactions wrap a sequence of SQL operations as a single unit; you need to already be writing multi-step SQL operations before bundling them into an atomic unit has a purpose.
- Concurrency & SynchronizationOperating Systems
Isolation, one of the four ACID guarantees, is solving precisely the race-condition problem concurrency-and-synchronization already introduced — two transactions racing on the same rows — just enforced by the database instead of application-level locks.
Unlocks
Used in
- Financial transactions, inventory systems, and any multi-step operation that must be all-or-nothing
- BEGIN/COMMIT/ROLLBACK in every relational database
- Isolation levels (read committed, serializable) trading consistency guarantees for concurrency performance
- Understanding deadlocks between database transactions competing for the same locked rows
Projects
- Implement a money-transfer function wrapped in a transaction, and simulate a crash mid-transfer to confirm the database rolls back to a consistent state
- Reproduce a race condition between two concurrent transactions updating the same row, then fix it using an appropriate isolation level or explicit locking
Examples
- BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; ensures both updates happen or neither does
- Two transactions both reading and incrementing the same counter without proper isolation can lose one increment — the identical race condition from concurrency-and-synchronization, now inside the database
Resources
Mastery checklist
- I can explain each of the four ACID guarantees with a concrete example
- I can wrap a multi-step operation in a transaction with correct commit/rollback behavior
- I can explain what an isolation level controls and name at least two
- I can reproduce and explain a race condition between two concurrent transactions