Transactions: ACID, Isolation Levels, MVCC and Locks
What each ACID letter is built from, the anomalies isolation levels allow, how MVCC and locks work, and why write skew needs serializable.
- 4 propertiesAtomicity, consistency, isolation, durability
- 5 anomaliesDirty, non-repeatable, phantom, lost update, write skew
- 1 fixRetry the whole transaction on failure
Show what to say
“ACID is atomicity from the undo information and log, consistency from constraints plus application code, isolation from locks or MVCC, and durability from a write-ahead log flushed before commit. Isolation is a dial: read committed stops dirty reads, repeatable read gives a snapshot, and only serializable stops write skew, so I retry on failure. MVCC lets readers and writers proceed without blocking.”
Trade-offs
- Read committed vs serializable: read committed is fast and the usual default; serializable is correct for cross-row rules but aborts and needs retries.
- MVCC vs locking readers: MVCC never blocks readers on writers but keeps old versions; read locks are simple but cause waiting.
- Optimistic vs pessimistic: a version check holds no locks and suits rare conflicts; row locks suit hot rows.
Bring it up before they ask
Write skew passes under snapshot isolation, so name it and the fix (serializable with retry, or lock the rows you read).
A groups changes so they happen all or nothing, and say how much concurrent transactions may see of each other. Databases mostly use (keep old versions so readers never block writers) plus locks, and the anomaly that survives is .
ACID, one letter at a time
Read more · 166 words
| Property | Meaning | Provided by |
|---|---|---|
| Atomicity | All the changes happen, or none | Undo information and the log |
| Consistency | Rules (constraints) hold before and after | Constraints, plus your code |
| Isolation | Concurrent transactions do not trip over each other | Locks or MVCC |
| Durability | A committed change survives a crash | Write-ahead log, flushed to disk |
Consistency is the odd one: the database enforces only the rules you declared (keys, uniqueness, checks). The rest is your code's job. More in the HLD path.
The anomalies
Isolation is about which of these your may see.
Read more · 89 words
- Dirty read. You read another transaction's uncommitted change, which may be rolled back.
- Non-repeatable read. A row you read twice changed in between.
- Phantom. The same range query returns new rows the second time.
- Lost update. Two transactions read a value, both write, and one write vanishes.
- . Two transactions read the same data, then change different rows so that together they break a rule.
MVCC: readers do not block writers
With , a write does not overwrite a row.
Read more · 91 words
It adds a new version, and every reads the newest version committed before its snapshot. So a long report can run while updates continue, and neither waits.
The tests show it: a reader keeps its old view, uncommitted writes are invisible, and two transactions changing one row conflict (first committer wins). Old versions are cleaned up later (vacuum in Postgres), and long transactions hold that back.
// A tiny multi-version store with snapshot isolation. A write never overwrites: it adds a new version
// stamped with a commit number. A transaction reads the newest version committed before it began.
class Tx {
constructor(store) {
this.store = store;
this.snapshot = store.clock; // the world as of this moment
this.writes = new Map(); // private until commit: nobody else can see it (no dirty reads)
}
get(key) {
if (this.writes.has(key)) return this.writes.get(key);
const versions = this.store.versions.get(key) ?? [];
for (let i = versions.length - 1; i >= 0; i--) if (versions[i].at <= this.snapshot) return versions[i].value;
return undefined;
}
set(key, value) {
this.writes.set(key, value);
}
commit() {
for (const key of this.writes.keys()) {
const versions = this.store.versions.get(key) ?? [];
// first committer wins: someone changed this key after our snapshot, so our write is based on stale data
if (versions.length && versions.at(-1).at > this.snapshot) throw new Error(`write conflict on ${key}`);
}
const at = ++this.store.clock;
for (const [key, value] of this.writes) {
if (!this.store.versions.has(key)) this.store.versions.set(key, []);
this.store.versions.get(key).push({ value, at });
}
return at;
}
}
export class Store {
constructor() {
this.versions = new Map(); // key -> [{ value, at }], oldest first
this.clock = 0;
}
begin() {
return new Tx(this);
}
}Locks
Writes still take row locks, so the second writer of a row waits or fails.
Read more · 107 words
SELECT ... FOR UPDATE locks the rows you read. Under two-phase locking a takes locks as it goes and releases them all at commit.
Locks bring : two transactions wait on each other's rows. The database finds the cycle, aborts one, and the application retries. When conflicts are rare, optimistic concurrency (a version column checked at update time) holds no locks at all.
Write skew and serializable
Two doctors are on call, and at least one must be.
Read more · 112 words
Each sees a count of 2 and takes its doctor off. They write different rows, so sees no conflict.
Serializable isolation prevents it: the database behaves as if transactions ran one at a time and aborts one of two that conflict. The code must retry. Cheaper options are to lock the rows you read, or make both transactions write one shared row so they collide.
- Client
- Database
Text description
- Alice's tx → Database: count doctors on call
- Database replies to Alice's tx: 2
- Bob's tx → Database: count doctors on call
- Database replies to Bob's tx: 2
- Alice's tx → Database: Alice goes off call
- Bob's tx → Database: Bob goes off call
- Alice's tx → Database: COMMIT
- Bob's tx → Database: COMMIT (different rows, no conflict)
Choosing an isolation level
The default differs by database: Postgres and Oracle use read committed, MySQL InnoDB uses repeatable read. Check yours before you rely on it.
Does a rule span several rows that different transactions read and change (a count, a balance across accounts)?
The isolation level matrix
Read more · 82 words
| Level | Prevents | Still allows |
|---|---|---|
| Read uncommitted | Nothing reliably | Dirty reads |
| Read committed | Dirty reads | Non-repeatable reads, phantoms, write skew |
| Repeatable read | Plus non-repeatable reads | Write skew (and phantoms in some systems) |
| Serializable | All of them | Aborts you must retry |
Names are standardised but implementations differ. Postgres repeatable read is and does prevent phantoms; InnoDB uses next-key locks to cover gaps.
Distributed transactions
One database gives you a .
Read more · 108 words
Across services you do not have one. asks every participant to prepare and then commit, but a failed coordinator leaves participants blocked holding locks. Most systems use a instead: a series of local transactions with compensating actions (refund the charge) if a later step fails. See the payment system question.
Practical habits
Read more · 159 words
- Retry on and serialization failures, with the whole transaction in the loop and side effects.
- Lock in a consistent order across code paths.
- Use constraints (unique, foreign key, check) so the database enforces what it can.
- Prefer atomic updates:
UPDATE accounts SET balance = balance - 50 WHERE id = 1instead of read, compute, write.
Follow-up questions
Explain ACID and which mechanism provides each part.
Atomicity via undo and the log, consistency via constraints plus application logic, isolation via locks or , durability via a flushed to disk before commit is acknowledged.
What anomalies exist and which isolation level stops each?
Read committed stops dirty reads. Repeatable read also stops non-repeatable reads. Only serializable stops , and phantoms depend on the database.
How does MVCC work and what does it cost?
Each write creates a new row version and readers use the newest version from their snapshot, so readers and writers do not block each other. The cost is storage for old versions and the cleanup of them.
What should application code do when a transaction fails with a deadlock or serialization error?
Retry the whole , a bounded number of times with , and make side effects outside the database .
Check yourself
Question 1 of 7Score 2 out of 3 or better to mark this page complete.