Transactions and ACID
A transfer is two writes that must succeed or fail together. Step it through, with a second session watching, and fail it on purpose.
Session A: Transfer $30
Press Step to BEGIN the transaction.
Session B's Isolation
what B is allowed to see while A is mid-transaction
Two Sessions, One Table
—Session A's SQL, run so far
ACID, As It Happens
Transactions and ACID: A Practical Guide
Four guarantees, wrapped around every BEGIN...COMMIT.
Quick Context
A transfer between two accounts is two separate UPDATE statements. If the database crashes between them, one account has lost money and the other never received it — unless the two are wrapped in a transaction, which guarantees they happen together or not at all.
The four letters
- Atomicity — the whole transaction commits or none of it does. A failure midway rolls everything back, including steps that already ran cleanly.
- Consistency — a transaction moves the database from one valid state to another; it cannot leave money existing that came from nowhere.
- Isolation — concurrent transactions do not see each other's half-finished work, to a degree controlled by the isolation level.
- Durability — once COMMIT returns, the change survives a crash the instant after.
Isolation levels, briefly
READ UNCOMMITTED lets one session see another's uncommitted changes — a "dirty read". READ COMMITTED, the default in most engines, blocks that: a session only ever sees data that has actually been committed. Stricter levels (REPEATABLE READ, SERIALIZABLE) exist to stop other classes of anomaly, at a cost in concurrency.
Interactive Exploration Guide
- Step through a clean transfer. BEGIN, debit Alice, credit Bob, COMMIT. Session B's balances only change at the very last step.
- Switch to READ UNCOMMITTED and step again. Now B sees Alice's balance drop the instant A's UPDATE runs — before COMMIT. That is a dirty read, and it is why READ UNCOMMITTED is rarely the default.
- Tick "Fail after the second UPDATE" and step through again. Both writes ran — Alice was debited, Bob was credited — but the transaction rolls back instead of committing, and Session A's own view snaps back to the original balances. Atomicity in one demonstration: a completed write is not a committed write.
Key Takeaway
A transaction makes a group of writes behave as one unit: Atomicity guarantees all-or-nothing, Consistency guarantees the result is valid, Isolation controls what concurrent sessions can see of work in progress, and Durability guarantees a commit survives a crash. Isolation is a dial, not a switch — READ UNCOMMITTED trades correctness for speed by permitting dirty reads, which is why READ COMMITTED is the default almost everywhere.