Transactions and ACID
A transaction groups several statements into one all-or-nothing unit: BEGIN, then the changes, then COMMIT to keep them or ROLLBACK to undo them. ACID names the four guarantees it gives: Atomic, Consistent, Isolated, Durable.
Why it matters
Moving money means taking £100 from one account and adding it to another. If the system crashes between the two, £100 vanishes. Transactions make sure either both changes happen or neither does — and that two people working at once don't corrupt each other's changes.
The idea
BEGIN starts a transaction. Changes made inside are visible to you but not yet permanent. COMMIT makes them all permanent at once; ROLLBACK throws them all away.
ACID: Atomic — all or nothing.
Consistent — the data moves from one valid state to another, never breaking a constraint.
Isolated — transactions running at the same time don't see each other's half-finished work (how strictly depends on the isolation level).
Durable — once committed, it survives a crash.
A bank transfer slip that only counts when it is stamped. You can fill in both lines — take from here, add to there — but nothing happens until the stamp (COMMIT). Tear up the slip (ROLLBACK) and it is as if you never started.
A torn-up slip leaves no trace. Inside a database, a long open transaction holds locks that can make other people wait. Keep transactions short: never wait for user input inside one.
Syntax
BEGIN;
UPDATE ... ;
UPDATE ... ;
COMMIT; -- or ROLLBACK;How it works
- BEGIN opens the transaction.
- Two UPDATEs move Gus into HR and give him a raise. Only this transaction can see the changes so far.
- COMMIT makes both changes permanent together. A crash before COMMIT would undo both.
- In the second example, DELETE removes every item — then ROLLBACK undoes it, and all 14 rows are back.
- If any statement fails, you roll back, and the data is exactly as it was before BEGIN.
Key points
- BEGIN … COMMIT makes several statements one all-or-nothing unit; ROLLBACK cancels it.
- Atomic: all or nothing. Consistent: constraints always hold. Isolated: concurrent transactions don't interfere. Durable: committed means saved.
- Isolation levels trade safety for speed: READ COMMITTED, REPEATABLE READ, SERIALIZABLE.
- Anomalies the levels prevent: dirty reads, non-repeatable reads, phantom reads.
- Keep transactions short; long ones hold locks and block others.
- Locks and write skew: ACID transactions and isolation levels. Transactions across several services: Distributed transactions: sagas and the outbox pattern. Both are in System Design.
Common mistakes
- Running related changes without a transaction, so a failure halfway leaves the data inconsistent.
- Leaving a transaction open (forgetting COMMIT), which holds locks.
- Assuming the default isolation level prevents every race. Two transactions can still both read a balance and both write it.
Not true: Default isolation level: it stops two transactions from overwriting each other's work.
In fact: Under the usual default, two transactions can both read the same balance and both write it back, so one change is lost. Lock the row first, or use a stricter level.
In the interview
A transaction groups statements into an atomic unit — BEGIN, changes, then COMMIT or ROLLBACK. ACID means atomicity, all or nothing; consistency, constraints hold before and after; isolation, concurrent transactions don't see each other's uncommitted work; and durability, committed changes survive crashes. Isolation is tunable: read committed, repeatable read, serializable. Each level trades anomalies, like non-repeatable or phantom reads, against concurrency. For something like a balance update, I lock the row with SELECT FOR UPDATE or use serializable.
Part of Unit 6: Building tables and changing data in the free SQL course.