transactions & locking
ACID & Transactions
Group multiple statements so they succeed or fail as a unit — protecting consistency in the face of crashes and concurrency.
A transaction groups several statements so they take effect as one. Either all of them happen or none of them do.
A bank wire — debit one account, credit another. Both happen, or neither. A power outage mid-transfer leaves both accounts as if nothing happened.
Key Concepts
1
The standard example is a transfer: debit one account, credit another. If the machine dies between those
two statements, money must not vanish.
2
The four letters.Atomicity — all or nothing. A crash mid-transaction leaves no half-finished state.
3
Consistency — the transaction moves the database from one valid state to another. Every constraint,
foreign key and check still holds afterwards; a transaction that would break one is rejected whole.
4
Isolation — concurrent transactions do not corrupt each other. How much they can still see of each
other is set by the isolation level.
5
Durability — once commit returns, the change survives a power cut.
6
How atomicity and durability are actually delivered. Both come from one mechanism: the write-ahead log.
7
Before changing a data page, the database appends a record of the change to a log file and flushes that log
to disk. Only then does it acknowledge the commit.
8
The order is the whole trick. On restart the database reads the log. Changes belonging to committed
transactions are re-applied — that is durability. Changes belonging to transactions with no commit record
are undone — that is atomicity. One log, both guarantees.
9
It is also why commit costs a disk flush, and why a thousand single-row transactions are far slower than
one transaction of a thousand rows.
10
The one you actually tune. Atomicity and durability are not really dials — you get them or you have
chosen a different database. Isolation is the one you trade against throughput, which is why isolation
levels are a topic of their own.
11
A trap in the wording. The C in ACID means "your constraints still hold". The C in CAP means "every
node returns the same data". Different ideas, same word. Interviewers listen for someone blending them.
12
Keep transactions short. A transaction holds locks until it ends. Opening one, calling an external
payment API, and committing afterwards means holding locks for the length of a network call — and every
other transaction touching those rows waits. Do the slow work outside; keep the transaction to the writes.
13
What the interviewer is probing.1. "What does each letter mean, concretely?" Probing: precision. Stalls: "It is safe."
Moves up: atomicity all-or-nothing, consistency constraints hold at commit, isolation concurrent
transactions do not corrupt each other, durability committed data survives a crash.
14
2. "Which property is actually relaxed most often?" Probing: the practical answer. Stalls:
"None." Moves up: isolation — most systems run at read committed rather than serialisable, trading
anomalies for concurrency.
15
3. "How is durability achieved?" Probing: the mechanism. Stalls: "It writes to disk." *Moves
up:* a write-ahead log flushed before commit acknowledges, so recovery can replay it.
16
4. "What makes long transactions dangerous?" Probing: the cost. Stalls: "Nothing." *Moves
up:* they hold locks and prevent cleanup of old row versions, which bloats storage and blocks other
work.