SQL Transactions & Isolation Levels: MVCC, Locking & Serializable in 2026

sql isolation levels mvcc is the primitive every backend engineer, DBA, and senior data engineer eventually confronts at 3 a.m. when two concurrent transactions produce a state their code should have made impossible — a bank balance below zero, a duplicated invoice, an inventory count that reads negative, or a feature-store row that appears to have two conflicting versions. The gap between "I know BEGIN and COMMIT" and "I can explain why my REPEATABLE READ transaction still lost an update and what changing to SERIALIZABLE costs in throughput" is the gap between a mid-level engineer and a senior one. This guide is the honest tour of what actually happens inside the engine when you set an isolation level, how MVCC and locking implement it differently, and why the same SET TRANSACTION ISOLATION LEVEL statement gives you different guarantees on Postgres, MySQL, and SQL Server.

The tour walks the four ANSI isolation levels — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE — and the four canonical anomalies each level does or doesn't prevent (dirty read, non-repeatable read, phantom read, lost update), plus write skew and read-only-tx anomaly that the 1995 Berenson et al. critique of ANSI SQL surfaced. It walks the two big families of concurrency-control implementations — two-phase locking (2PL) where every read grabs a shared lock and every write grabs an exclusive lock (SQL Server default), and multi-version concurrency control (MVCC) where each transaction reads its own snapshot without locking (Postgres, MySQL InnoDB, Snowflake, Oracle). It walks the engine-specific implementations — Postgres's per-tuple xmin/xmax + VACUUM, MySQL InnoDB's undo log + hidden columns, Snowflake's versioned micro-partitions + Time Travel — and finally the modern serializable snapshot isolation (SSI) implementations on Postgres 9.1+ and CockroachDB that combine MVCC's read throughput with commit-time validation for serializable correctness. Every section ships a teaching block followed by a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown.

When you want hands-on reps immediately after reading, drill the SQL practice library →, sharpen the SQL optimization drills → for reading concurrent-write plans, and layer SQL indexing drills → — because the correct isolation level plus the correct unique index is what actually stops the lost-update bug.

On this page

• Why isolation matters in 2026
• ANSI isolation levels + the anomalies
• MVCC deep dive — Postgres, MySQL InnoDB, Snowflake
• Locking vs snapshot isolation
• Serializable snapshot isolation + dialect matrix
• Cheat sheet — transactions + isolation recipe list
• Frequently asked questions
• Practice on PipeCode

1. Why isolation matters in 2026

The sql isolation levels mental model — why BEGIN; COMMIT; is only half the story, and how each concurrent workload maps to a specific correctness bug

The one-sentence invariant: an isolation level is a contract about what anomalies concurrent transactions can and cannot observe — every dollar you gain in throughput by relaxing the level, you pay for in extra correctness burden inside your application code, and every dollar you spend tightening the level, you get back in guarantees the engine now enforces for you. Every real system eventually has concurrent writers; only systems where the isolation contract matches the business rules survive contact with production concurrency.

Where concurrency anomalies actually show up in production.

• Banking transfers. debit A, credit B — if the debit runs at READ COMMITTED and A's balance is checked separately, two concurrent transfers can both pass the check and overdraw A.
• Inventory counts. check stock >= qty; decrement stock. Under READ COMMITTED, two concurrent orders can both read stock=1, both pass the check, and stock ends at -1.
• E-commerce coupon redemption. INSERT into redemption if not exists. Under insufficient isolation, two concurrent redemptions of a single-use coupon both succeed.
• Feature-store updates. ML pipelines that upsert a feature row plus a downstream cache row. Without serialization guarantees, the two writes can commit in the wrong order.
• Multi-row invariants. "Sum of team members' hours must not exceed 40". Two concurrent inserts each check the sum, each see it under 40, both commit — sum is now 60.
• CDC replication. Debezium reading a Postgres logical replication slot needs snapshot isolation to produce a consistent stream. Wrong isolation = torn transactions in the CDC feed.
• Analytics-on-OLTP. Long-running analytical queries on a busy OLTP system need snapshot isolation to avoid dirty / non-repeatable reads. Without snapshot, the analytics query sees writes flicker through.

The four ANSI anomalies (plus the two Berenson-et-al additions).

• Dirty read (P0). T1 reads a row modified by T2 before T2 commits. If T2 rolls back, T1 read a value that never existed. Prevented at RC and above.
• Non-repeatable read (P1). T1 reads row X. T2 modifies X and commits. T1 re-reads X and sees a different value. Prevented at RR and above.
• Phantom read (P2). T1 runs SELECT ... WHERE cond, gets N rows. T2 inserts a new row matching cond and commits. T1 re-runs the same query, gets N+1 rows. Prevented at SERIALIZABLE (mostly; some engines prevent at RR too).
• Lost update (P3). T1 reads row X, computes a new value, writes it. T2 concurrently does the same. One update overwrites the other silently. Prevented at SERIALIZABLE (or with explicit SELECT FOR UPDATE).
• Write skew (A5B in Berenson). T1 reads a set of rows and writes based on their sum. T2 concurrently reads the same set and also writes based on the sum. The joint invariant is violated. Not prevented by REPEATABLE READ (snapshot isolation); only prevented at true SERIALIZABLE / SSI.
• Read-only tx anomaly. A read-only transaction under snapshot isolation can observe an inconsistent state — even without writing. Fixed by SSI in Postgres.

What senior interviewers actually probe.

• Do you know the four levels? RU, RC, RR, SERIALIZABLE. Which anomalies each prevents.
• Do you know the ANSI-vs-Berenson critique? ANSI defined isolation by "phenomena preventable"; Berenson et al. showed the definitions were ambiguous and proposed the anomaly-based definitions the industry uses today.
• Do you know MVCC vs 2PL? MVCC lets readers not block writers; 2PL blocks. Both can implement serializable correctness (SSI vs SS2PL).
• Do you know the engine defaults? Postgres = RC. MySQL InnoDB = RR. SQL Server = RC (no snapshot by default). Oracle = RC. Snowflake = RC. CockroachDB = SERIALIZABLE.
• Do you know SELECT FOR UPDATE? Explicit row-lock to prevent lost update. Every engineer should have this in their toolkit for the "read-check-write" pattern.
• Do you know the write skew example? Two doctors on call trying to swap shifts — both check "is at least one other on call" — both pass — both commit — no one on call.
• Do you know when to pay for SERIALIZABLE? When application-level invariants are hard to enforce; you'd rather retry conflicting transactions than write manual locking code.
• Do you know the cost story? Serializable adds ~10–30% overhead typically on Postgres SSI, more under high contention. On CockroachDB it's the default (they optimised for it).

The mental model — what a transaction actually is.

• A transaction is a sequence of statements that appear atomic, consistent, isolated, and durable (ACID). Atomicity: all-or-nothing. Consistency: invariants preserved. Isolation: the anomaly axis we're talking about. Durability: committed writes survive crashes.
• Isolation is defined via anomalies. Not "what the tx sees" but "what anomalies it can observe."
• Higher isolation = stronger guarantees = lower throughput. Under contention, higher isolation means more rollbacks or more waiting.
• Snapshot isolation is a specific implementation. It's what most engines call "REPEATABLE READ" but it's not the same as the ANSI RR — snapshot allows write skew that RR technically shouldn't. Terminology matters.

Worked example — the lost update bug under READ COMMITTED

Detailed explanation. The classic: two workers concurrently increment a counter. Under RC, both read the current value, both compute new = current + 1, both write — one increment is lost.

Question. Given a counters(id, n) table, show the concurrency schedule that causes a lost update when both workers use READ COMMITTED and the increment is done via SELECT-then-UPDATE.

Input. counters row (id=1, n=0).

Code.

-- Worker A (READ COMMITTED)
BEGIN;
SELECT n FROM counters WHERE id = 1;   -- reads 0
-- application computes: new = 0 + 1
UPDATE counters SET n = 1 WHERE id = 1;
COMMIT;

-- Worker B (concurrent, READ COMMITTED)
BEGIN;
SELECT n FROM counters WHERE id = 1;   -- reads 0 (before A commits)
-- application computes: new = 0 + 1
UPDATE counters SET n = 1 WHERE id = 1;
COMMIT;

Step-by-step explanation.

1. Worker A begins tx, reads n=0.
2. Worker B begins tx, reads n=0 (A hasn't committed).
3. Worker A updates n=1, commits.
4. Worker B updates n=1, commits (overwriting A's write).
5. Final n=1 — but two increments should have made it n=2. One is lost.

Output.

Rule of thumb. Read-then-modify-then-write across separate SQL statements is the classic lost-update pattern. Fix — atomic UPDATE n = n + 1, or SELECT FOR UPDATE to lock the row, or upgrade to SERIALIZABLE.

Worked example — the atomic increment fix

Detailed explanation. Instead of read-modify-write in application code, do the modify atomically in SQL. No race.

Question. Rewrite the counter increment atomically so no isolation-level upgrade is needed.

Code.

BEGIN;
UPDATE counters SET n = n + 1 WHERE id = 1
RETURNING n;
COMMIT;

Step-by-step explanation.

1. UPDATE ... SET n = n + 1 reads and writes in one statement.
2. Under any isolation level, the UPDATE acquires an exclusive row lock and computes n + 1 from the current committed value.
3. Two concurrent workers both do UPDATE. One acquires the lock first, computes 0+1=1, releases. The other acquires the lock, sees n=1, computes 1+1=2.
4. RETURNING n gives the caller the new value.
5. No lost update. Works at RC. No SELECT FOR UPDATE needed.

Output.

Rule of thumb. Always prefer atomic UPDATE with expressions over read-modify-write in application code. The engine's row-lock during UPDATE prevents lost updates for free.

Worked example — the SELECT FOR UPDATE explicit lock

Detailed explanation. When you must read a value, do some application-side logic, then write — use SELECT FOR UPDATE to lock the row while you decide.

Question. Show the SELECT FOR UPDATE pattern for a check-then-decrement inventory update.

Code.

BEGIN;
SELECT qty FROM inventory WHERE product_id = 42 FOR UPDATE;   -- locks the row
-- application: if qty >= requested then decrement
UPDATE inventory SET qty = qty - :requested WHERE product_id = 42;
COMMIT;

Step-by-step explanation.

1. SELECT FOR UPDATE acquires an exclusive row lock. No other transaction can UPDATE or DELETE this row until commit.
2. Application reads qty (say 10), checks vs requested (say 3), decides to decrement.
3. UPDATE decrements the row while still under the lock.
4. COMMIT releases the lock.
5. A second concurrent worker's SELECT FOR UPDATE blocks at step 1 until this worker's COMMIT. When it unblocks, it sees the new qty (7) and makes its own decision.

Output.

Rule of thumb. Any "read, decide, write" pattern needs SELECT FOR UPDATE (or SERIALIZABLE). Without it, the read is stale by the time the write executes.

Common beginner mistakes

• Assuming BEGIN + COMMIT alone protect from concurrent writers.
• Using read-modify-write in application code without SELECT FOR UPDATE.
• Confusing READ COMMITTED (default) with actual serializable semantics.
• Assuming REPEATABLE READ = SERIALIZABLE (they're not — write skew).
• Setting SERIALIZABLE globally without understanding the retry cost.

sql isolation levels mvcc interview question on the read-modify-write bug

A senior interviewer asks: "You have a wallet(user_id, balance) table on Postgres RC. Users can transfer money via SELECT balance; if enough, UPDATE. Show the anomaly under 100 concurrent transfers, then give three fixes in order of preference."

Solution Using atomic UPDATE with balance check + SELECT FOR UPDATE + SERIALIZABLE

-- Fix 1 (best): atomic UPDATE with WHERE guard
UPDATE wallet SET balance = balance - :amt
WHERE user_id = :uid AND balance >= :amt
RETURNING balance;
-- If RETURNING is empty, insufficient funds.

-- Fix 2: SELECT FOR UPDATE
BEGIN;
SELECT balance FROM wallet WHERE user_id = :uid FOR UPDATE;
-- application check
UPDATE wallet SET balance = balance - :amt WHERE user_id = :uid;
COMMIT;

-- Fix 3: SERIALIZABLE + retry on 40001
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT balance FROM wallet WHERE user_id = :uid;
UPDATE wallet SET balance = balance - :amt WHERE user_id = :uid;
COMMIT;
-- On serialization_failure (SQLSTATE 40001) retry the whole tx.

Step-by-step trace.

Output:

Why this works — concept by concept:

• Atomic UPDATE with WHERE guard — the check balance >= :amt is inside the UPDATE. Row-lock during UPDATE ensures the check and the write are atomic. No lost update possible. Prefer this always.
• SELECT FOR UPDATE — explicit row-lock in a transaction. Any concurrent tx wanting to write this row must wait. Useful when the application logic between read and write is complex.
• SERIALIZABLE + retry — engine promises serialisable execution; on conflict, one of the two txs aborts with serialization_failure and application retries. Simpler mental model, higher tail latency.
• Cost — Fix 1 is O(1) with no lock overhead beyond the UPDATE's row lock. Fix 2 adds a round-trip and a lock. Fix 3 pays retry cost proportional to contention.

SQL
Topic — SQL
SQL practice library

Practice →

Practice →

2. ANSI isolation levels + the anomalies

read committed, repeatable read, serializable — the four ANSI levels and which anomalies each prevents

The mental model in one line: ANSI SQL defines four isolation levels — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE — as a strict hierarchy where each higher level prevents everything the lower one does plus one additional anomaly, but the 1995 Berenson et al. critique showed the ANSI definitions were ambiguous and that snapshot isolation (what most engines actually implement as "RR") sits somewhere between RR and SERIALIZABLE with its own peculiar write-skew anomaly.

Slot 1 — READ UNCOMMITTED (RU).

• What it allows. Every anomaly including dirty reads.
• Where it's useful. Analytics on an OLTP DB when approximate is OK — reading uncommitted writes gives fresher (but potentially wrong) numbers.
• Postgres. Doesn't actually implement RU — treats it as RC. Setting RU on Postgres = RC.
• MySQL InnoDB. Real RU. Can see uncommitted writes.
• SQL Server. Real RU. Also supports NOLOCK hint per-query as a per-statement RU.
• Rarely justified in modern OLTP. Postgres's decision to alias to RC is defensible.

Slot 2 — READ COMMITTED (RC).

• What it prevents. Dirty reads.
• What it allows. Non-repeatable reads, phantoms, lost updates.
• Default on. Postgres, SQL Server, Oracle, Snowflake.
• Semantic. Every SELECT sees only committed data, but successive SELECTs in the same tx can see different values.
• Best for. Most OLTP workloads. Cheap concurrency; application-level tricks (atomic UPDATE, SELECT FOR UPDATE) handle the anomalies.
• The gotcha. Non-repeatable reads inside a tx are fine for many workloads but surprising if the developer doesn't know.

Slot 3 — REPEATABLE READ (RR).

• What it prevents (ANSI). Dirty reads, non-repeatable reads.
• What it allows (ANSI). Phantoms.
• What it actually prevents on Postgres, InnoDB. Everything RC does, plus non-repeatable reads AND phantoms. Because these engines implement RR as snapshot isolation, phantoms don't happen.
• What SI still allows. Write skew, read-only-tx anomaly.
• Default on. MySQL InnoDB.
• Semantic. The tx sees a consistent snapshot as of BEGIN. Re-reading same row returns the same value.
• Best for. Long-running transactions that need consistency across multiple reads (analytics, reports).

Slot 4 — SERIALIZABLE.

• What it prevents. Everything, including write skew.
• How it's implemented. SS2PL (SQL Server default at SERIALIZABLE), SSI (Postgres 9.1+, CockroachDB).
• Guarantee. Result is equivalent to some serial execution of all txs.
• Cost. Higher rollback rate under contention (SSI) or higher blocking (SS2PL).
• Default on. CockroachDB, YugabyteDB.
• Best for. Business-critical invariants across multiple rows — bank transfers, inventory constraints, doctor-on-call scheduling.

Slot 5 — the four ANSI anomalies + write skew in detail.

• Dirty read. T2 modifies row X but doesn't commit. T1 reads X, sees T2's uncommitted value. If T2 rolls back, T1 saw a phantom value.
• Non-repeatable read. T1 reads X = 100. T2 modifies X to 200 and commits. T1 re-reads X and now sees 200. The row changed under T1's feet.
• Phantom read. T1 runs SELECT COUNT(*) WHERE ..., gets 5. T2 inserts a new matching row and commits. T1 re-runs the same query, gets 6. New rows appeared.
• Lost update. T1 reads X. T2 reads X. Both compute new values based on read. Both write. One write silently overwrites the other.
• Write skew. T1 reads a set of rows. Based on set property, T1 writes X. T2 concurrently reads the same set, based on same property, writes Y. Each tx sees the pre-state consistent; joint result violates the invariant.

Slot 6 — the Berenson et al. critique.

• 1995 paper "A Critique of ANSI SQL Isolation Levels". Berenson, Bernstein, Gray, Melton, O'Neil, O'Neil.
• The critique. ANSI defined isolation via "phenomena" (dirty read, etc.) but the definitions were ambiguous — they could be read as "allowed" or "prohibited" in edge cases.
• The fix. Define isolation via anomalies precisely: A0 (write-dirty write), A1 (dirty read), A2 (non-repeatable read), A3 (phantom), A5A (read skew), A5B (write skew), plus the intended history-serial-equivalent semantic for SERIALIZABLE.
• New level introduced. Snapshot isolation (SI). SI prevents A1, A2, A3 (via snapshot reads), but allows A5B (write skew).
• Modern engines. Postgres, MySQL InnoDB, Snowflake, Oracle — all implement "RR" as SI, not the ANSI RR.

Slot 7 — snapshot isolation semantics.

• Read semantic. Every read in the tx sees the state as of BEGIN. Consistent across the tx.
• Write semantic. Writes are visible to the writing tx immediately, invisible to other concurrent txs until commit.
• Write-write conflict. Two concurrent txs writing the same row — one commits first, the other must abort with serialization_failure (Postgres) or wait for the first (InnoDB, depending on config).
• Read-write conflict. Reader sees the snapshot; writer proceeds. No blocking.
• Deadlock free (for reads). Since reads don't lock, they can't participate in deadlocks.
• What SI still allows. Write skew across separate row sets.

Slot 8 — write skew example.

• Doctors on call. Table doctors(id, on_call BOOL). Invariant: at least one doctor must be on call at all times.
• Two txs try to change on_call to FALSE. T1 reads count(on_call=TRUE) = 2, sees another doctor on call, sets self to FALSE. T2 concurrently reads count = 2 (same snapshot), sees another on call, sets self to FALSE. Both commit. Now zero doctors on call.
• RC would fail too. Actually RC in this case works because both txs see the intermediate committed states via their SELECTs.
• SI (RR on Postgres) allows it. Both txs are reading the snapshot as of BEGIN — before either wrote.
• SERIALIZABLE (SSI on Postgres, or SET LOCK / advisory locks) prevents. SSI validates at commit: T1 wrote based on a read of the doctors table; T2 read the doctors table concurrently and wrote; conflict.

Slot 9 — the isolation-level ↔ anomaly matrix.

Slot 10 — engine defaults.

• Postgres 15+. Default RC. Maximum SERIALIZABLE (SSI since 9.1).
• MySQL InnoDB. Default REPEATABLE READ (actually SI). Maximum SERIALIZABLE (2PL-based, expensive).
• SQL Server. Default RC. RCSI opt-in for snapshot-based RC. Maximum SERIALIZABLE (SS2PL).
• Oracle. Default RC (they call it "read consistent"). Maximum SERIALIZABLE (snapshot-based).
• Snowflake. Default RC. Maximum SERIALIZABLE.
• BigQuery. Snapshot-based tx model, no explicit levels.
• CockroachDB. Default SERIALIZABLE (SSI).
• YugabyteDB. Default SERIALIZABLE.

Common beginner mistakes

• Assuming "REPEATABLE READ" gives you serializable semantics — it doesn't; snapshot isolation allows write skew.
• Setting READ UNCOMMITTED on Postgres — silently upgraded to RC.
• Not knowing your engine's default (Postgres RC vs InnoDB RR).
• Confusing SQL Server RCSI (row-versioned) with default RC.

Worked example — reproducing the non-repeatable read anomaly

Detailed explanation. T1 reads a row; T2 modifies and commits; T1 re-reads. On RC, the second read differs.

Question. Show the schedule that reproduces non-repeatable read under RC.

Input. Postgres RC (default), accounts(id, balance).

Code.

-- Session A (RC — default)
BEGIN;
SELECT balance FROM accounts WHERE id = 1;   -- Returns 100
-- ... time passes ...
SELECT balance FROM accounts WHERE id = 1;   -- Returns 200 (different!)
COMMIT;

-- Session B (concurrent)
BEGIN;
UPDATE accounts SET balance = 200 WHERE id = 1;
COMMIT;

Step-by-step explanation.

1. Session A starts tx, reads balance=100 at time t1.
2. Session B starts tx, updates balance to 200 at time t2, commits.
3. Session A re-reads at time t3. Under RC, sees committed value 200.
4. Same row, same tx, two different values seen — non-repeatable read.
5. Fix — upgrade to REPEATABLE READ. Session A's second read still returns 100 (the snapshot at BEGIN).

Output.

Rule of thumb. RC is fine for stateless reads. If a tx re-reads the same row, upgrade to RR (or use SELECT FOR UPDATE if you also plan to write).

Worked example — reproducing phantom read on ANSI RR (not SI)

Detailed explanation. Under strict ANSI RR, phantom reads are allowed. Under SI (Postgres, InnoDB "RR"), they're not — the snapshot fixes the row set.

Question. On Postgres RR, verify that phantom reads DON'T occur (because it's SI, not ANSI RR).

Input. Postgres RR, orders(id, status) table.

Code.

-- Session A (Postgres REPEATABLE READ — actually SI)
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM orders WHERE status = 'pending';   -- Returns 5

-- Session B (concurrent)
BEGIN;
INSERT INTO orders (status) VALUES ('pending');
COMMIT;

-- Back to A
SELECT COUNT(*) FROM orders WHERE status = 'pending';   -- Still returns 5 (SI)
COMMIT;

Step-by-step explanation.

1. Session A starts a REPEATABLE READ tx. Takes a snapshot at BEGIN.
2. Session B inserts a new pending order, commits.
3. Session A re-reads the count. Under SI (Postgres RR), the snapshot doesn't include B's insert — count stays 5.
4. If this were strict ANSI RR (SQL Server SERIALIZABLE using 2PL), phantoms would still occur without range locks.
5. Postgres's RR is safer than the ANSI RR by preventing phantoms. But it still allows write skew.

Output.

Rule of thumb. Postgres "REPEATABLE READ" is snapshot isolation. Phantoms are prevented (unlike ANSI RR). Use it for consistent multi-read reports. But it's not SERIALIZABLE.

Worked example — the doctor-on-call write skew

Detailed explanation. The canonical write skew example — two doctors trying to go off call while relying on the invariant that at least one other is on call.

Question. Show the write skew schedule under Postgres RR (SI). Then show what SERIALIZABLE prevents.

Input.

CREATE TABLE doctors (id INT PRIMARY KEY, on_call BOOLEAN NOT NULL);
INSERT INTO doctors VALUES (1, TRUE), (2, TRUE);

Code.

-- Both sessions Postgres REPEATABLE READ

-- Session A: doctor 1 wants to go off call
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM doctors WHERE on_call;   -- Returns 2
-- Application checks: 2 >= 1, safe to go off
UPDATE doctors SET on_call = FALSE WHERE id = 1;
-- (does not commit yet)

-- Session B: doctor 2 concurrently
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM doctors WHERE on_call;   -- Still returns 2 (snapshot)
-- Application checks: 2 >= 1, safe to go off
UPDATE doctors SET on_call = FALSE WHERE id = 2;
COMMIT;   -- succeeds

-- Session A commits
COMMIT;   -- succeeds under RR (both wrote different rows)

-- Final state: both doctors off call. Invariant violated.

Step-by-step explanation.

1. Both sessions see 2 doctors on call (snapshot at BEGIN).
2. Session A writes doctor 1 off. Session B writes doctor 2 off. Different rows — no write-write conflict.
3. Both commit successfully under RR (SI).
4. Final state has 0 doctors on call — invariant violated.
5. Under SERIALIZABLE (SSI): Postgres detects that both txs read the doctors table (as their basis for decision) and wrote to it. On commit of the second, SSI raises serialization_failure. Application retries; the retry sees only 1 doctor on call and refuses to go off. Invariant preserved.

Output.

Rule of thumb. Any invariant that spans multiple rows and depends on a set-level property (count, sum, existence) is vulnerable to write skew under SI. Use SERIALIZABLE or explicit locking.

read committed interview question on choosing the right level

A senior interviewer asks: "Design the transaction contract for an inventory-management system. Rank the isolation levels for (a) reading current stock, (b) placing an order (check-then-decrement), (c) generating a nightly report. Explain trade-offs."

Solution Using level-per-operation matched to consistency requirements

-- (a) Reading current stock — RC is fine (approximate value, no invariant)
SELECT qty FROM inventory WHERE product_id = 42;

-- (b) Placing an order — atomic UPDATE with WHERE guard (no isolation upgrade needed)
UPDATE inventory
SET qty = qty - :requested
WHERE product_id = 42 AND qty >= :requested
RETURNING qty;

-- (c) Nightly report — REPEATABLE READ for consistent snapshot
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT product_id, SUM(sales) FROM orders WHERE order_date = :yesterday GROUP BY product_id;
SELECT product_id, qty FROM inventory;
COMMIT;

Step-by-step trace.

Output:

Why this works — concept by concept:

• RC for reads — cheapest, fastest. No lock, no snapshot maintenance.
• Atomic UPDATE with WHERE guard — check-then-decrement in one statement. Row-lock during UPDATE + WHERE clause prevents both lost update and negative stock. If the UPDATE affects 0 rows, insufficient qty; retry or fail.
• RR for reports — the report queries multiple tables; each must see the same snapshot. RR gives that in one BEGIN.
• No SERIALIZABLE needed — no cross-row invariants that write skew would violate. Atomic UPDATE handles single-row invariants.
• Cost — RC ~0 overhead. Atomic UPDATE = row lock during UPDATE. RR = per-tx snapshot xmin. All manageable.

SQL
Topic — SQL
SQL practice library

Practice →

Practice →

3. MVCC deep dive — Postgres, MySQL InnoDB, Snowflake

postgres mvcc, innodb mvcc, and Snowflake versioned micro-partitions — three storage-level implementations of the same "read your own snapshot" contract

The mental model in one line: MVCC (multi-version concurrency control) is the storage technique where each row modification creates a new version rather than overwriting the old one; concurrent readers see the version that was current when their transaction started, and writes are visible to other readers only after commit — this decouples readers from writers, dramatically improving throughput at the cost of storage bloat that must be reclaimed by VACUUM (Postgres), purge (MySQL), or micro-partition compaction (Snowflake).

Slot 1 — Postgres MVCC via xmin/xmax.

• Every row has hidden columns xmin and xmax. xmin = tx-id that inserted this row version. xmax = tx-id that deleted it (or 0 if still current).
• Visibility check. For each tuple, Postgres checks: xmin is committed AND xmin < my_tx_snapshot AND (xmax is 0 OR xmax is uncommitted OR xmax > my_tx_snapshot). Only visible tuples are returned.
• UPDATE = insert new + mark old with xmax. The old tuple stays around until VACUUM.
• DELETE = mark tuple with xmax. Row stays physically present.
• INSERT = new tuple with xmin = current tx-id.
• HOT (Heap-Only Tuple) updates. When updates don't change indexed columns and the new tuple fits on the same page, Postgres uses HOT — avoids index update. Faster, less bloat.
• Storage bloat. Every UPDATE and DELETE leaves dead tuples. Table size grows until VACUUM reclaims.
• VACUUM. Reclaims dead tuple space. autovacuum runs continuously; manual VACUUM is a last resort.

Slot 2 — Postgres tuple visibility rules.

• Snapshot at BEGIN. Postgres records the current highest committed tx-id (xmax_snapshot) and the list of currently running tx-ids.
• A tuple is visible if. xmin < xmax_snapshot AND xmin not in running_txs AND xmin was committed. And also either xmax == 0 or xmax not visible per same rules.
• Read Committed vs RR. RC re-computes the snapshot for each SELECT; RR keeps the same snapshot for the whole tx.
• Serializable. Uses same snapshots plus SSI validation on commit.
• FOR UPDATE. Adds a row-lock and forces visibility of the current committed tuple, even under RR.

Slot 3 — Postgres VACUUM and autovacuum.

• What VACUUM does. Marks dead tuples (xmax committed and older than the oldest running tx snapshot) as reusable.
• What VACUUM FULL does. Rewrites the entire table with no dead tuples. Takes ACCESS EXCLUSIVE lock. Rarely appropriate.
• autovacuum. Background process that runs VACUUM automatically per table, triggered by tuple-update / delete thresholds.
• Tuning knobs. autovacuum_vacuum_scale_factor (fraction of table that triggers VACUUM), autovacuum_max_workers, autovacuum_vacuum_cost_delay. Default suits most; tune for very-write-heavy tables.
• Bloat monitoring. pg_stat_user_tables and pgstattuple extension. Weekly review of top-bloated tables.
• Transaction ID wraparound. Postgres tx-ids are 32-bit. If autovacuum falls too far behind, the DB refuses new writes until a manual VACUUM FREEZE. Terrifying incident when it happens; monitor datfrozenxid.

Slot 4 — MySQL InnoDB MVCC via undo log.

• Every row has hidden columns DB_TRX_ID and DB_ROLL_PTR. DB_TRX_ID = tx-id that last modified this row. DB_ROLL_PTR = pointer into the undo log.
• Undo log stores old versions. Instead of Postgres's tuple-versioning-in-page, InnoDB keeps the current row in the main table and old versions in a separate undo log.
• Read semantic. Reader gets a snapshot tx-id. If row's DB_TRX_ID > snapshot, follow DB_ROLL_PTR to find the old version.
• UPDATE = write new value, chain old to undo log. Old version accessible via ROLL_PTR chain.
• DELETE = mark tuple deleted, chain old to undo.
• Purge. Background thread that removes undo log entries once no active tx needs them.
• Comparison with Postgres. InnoDB's design has no in-place bloat; but undo log can bloat instead. Purge is InnoDB's VACUUM equivalent.
• innodb_purge_threads. Controls purge parallelism.

Slot 5 — MySQL InnoDB REPEATABLE READ implementation.

• Default level. MySQL InnoDB defaults to REPEATABLE READ (SI-flavoured).
• First SELECT determines snapshot. Not BEGIN — the first read establishes the snapshot's tx-id.
• START TRANSACTION WITH CONSISTENT SNAPSHOT. Establishes the snapshot at BEGIN, matching Postgres RR behaviour.
• Gap locks under RR. InnoDB uses gap locks and next-key locks to prevent phantoms — extra safety over pure SI.
• READ COMMITTED opt-in. For SELECT statements, gap locks are relaxed; each SELECT gets a fresh snapshot.
• Locking reads. SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE still lock even under RR.

Slot 6 — Snowflake MVCC via versioned micro-partitions.

• Micro-partitions are immutable. Every write creates new micro-partitions; old ones stay for the Time Travel retention window.
• Time Travel. SELECT * FROM t AT (TIMESTAMP => '2026-07-01') queries the state at that timestamp. Available for the retention period (1–90 days).
• Fail-safe. Additional 7 days after Time Travel expires. For disaster recovery only, not user-queryable.
• Concurrency. Snapshot isolation for reads. Writers create new micro-partitions; readers see the snapshot at query start.
• CLONE. CREATE TABLE t2 CLONE t — zero-copy clone via metadata pointer to same micro-partitions. Time-travel + clone are Snowflake's storage magic.
• Storage cost. Historical micro-partitions bill during their retention.

Slot 7 — Oracle MVCC via undo tablespace.

• Undo tablespace holds row versions. Same design as InnoDB.
• Read consistency. Every SELECT gets a consistent snapshot as of the tx start (RR/SERIALIZABLE) or statement start (RC).
• ORA-01555 snapshot too old. Famous Oracle error. Long-running read tries to reach an undo record that's already been overwritten. Fix — enlarge undo tablespace or shorten reads.

Slot 8 — comparing the three implementations.

Slot 9 — the trade-offs.

• Postgres. Simple design, in-place versioning. Big bloat concern if VACUUM lags. HOT updates when possible.
• InnoDB. Undo separated from data. Undo log bloat under high write. Purge thread must keep up.
• Snowflake. Versioned micro-partitions cost money per day retained. Time Travel is a killer feature for data recovery.
• Storage. All three eventually stabilise once dead versions are reclaimed / expired.
• Read latency. All three have low read latency because snapshots are efficient.

Slot 10 — long-running transactions and MVCC.

• The problem. A long-running tx keeps its snapshot alive, forcing MVCC to preserve old versions for that snapshot.
• Postgres consequence. VACUUM can't reclaim tuples deleted after the long tx started. Bloat grows.
• InnoDB consequence. Undo log grows.
• Snowflake consequence. Versioned partitions retained.
• Best practice. Keep transactions short. Batch reads. Move long-running analytics to a read replica or Snowflake.
• idle_in_transaction_session_timeout. Postgres setting to auto-abort txs that idle too long.

Common beginner mistakes

• Ignoring VACUUM on Postgres — table bloats invisibly.
• Long-running transactions on OLTP — MVCC dead-tuple retention explodes.
• Confusing InnoDB RR (SI-flavoured with gap locks) with Postgres RR (pure SI).
• Assuming Time Travel is free on Snowflake — storage cost per historical MP.
• Setting idle_in_transaction_session_timeout = 0 and letting connection-pool leak txs.

Worked example — inspecting xmin/xmax on Postgres

Detailed explanation. You can SELECT the hidden xmin and xmax columns to see MVCC state directly.

Question. Show a session that inserts, updates, and deletes rows, inspecting xmin/xmax after each step.

Code.

CREATE TABLE test_mvcc (id INT PRIMARY KEY, val TEXT);

BEGIN;
INSERT INTO test_mvcc VALUES (1, 'a');
INSERT INTO test_mvcc VALUES (2, 'b');
COMMIT;

SELECT id, val, xmin, xmax FROM test_mvcc;
-- id | val | xmin  | xmax
--  1 |  a  | 12345 |   0
--  2 |  b  | 12345 |   0

BEGIN;
UPDATE test_mvcc SET val = 'a2' WHERE id = 1;
COMMIT;

SELECT id, val, xmin, xmax FROM test_mvcc;
-- id | val | xmin  | xmax
--  1 | a2  | 12346 |   0     -- new version, new xmin
--  2 |  b  | 12345 |   0

-- The old (id=1, val='a') tuple is still in the page, waiting for VACUUM
-- but has xmax = 12346, so no active tx sees it.

Step-by-step explanation.

1. INSERT creates rows with xmin = current_tx_id, xmax = 0 (still alive).
2. UPDATE deletes the old tuple (sets its xmax) and inserts a new tuple with a new xmin.
3. The old tuple physically remains on disk until VACUUM.
4. Query pg_stat_all_tables.n_dead_tup to see how many dead tuples are pending vacuum.
5. Autovacuum eventually reclaims. Meanwhile, snapshots that were started before the UPDATE can still see the old tuple.

Output. See the tuple states in the code above.

Rule of thumb. Postgres tuple versioning is visible if you look for it. VACUUM makes dead tuples reusable.

Worked example — reading old state via InnoDB undo log

Detailed explanation. InnoDB's REPEATABLE READ reads through undo log when needed.

Question. Show two sessions where session A's RR read still sees the old value after session B commits an update.

Code.

-- MySQL InnoDB (default REPEATABLE READ)

-- Session A
START TRANSACTION WITH CONSISTENT SNAPSHOT;
SELECT * FROM accounts WHERE id = 1;  -- returns balance=100

-- Session B
UPDATE accounts SET balance = 200 WHERE id = 1;
COMMIT;

-- Session A continues
SELECT * FROM accounts WHERE id = 1;  -- STILL returns 100 (from undo log)
COMMIT;

Step-by-step explanation.

1. Session A starts a tx with consistent snapshot. Snapshot tx-id = X.
2. Session B updates the row and commits. The row's current version is now (balance=200, DB_TRX_ID=Y where Y > X).
3. Session A reads. InnoDB sees DB_TRX_ID > snapshot X, follows DB_ROLL_PTR to the undo log, retrieves the old value (balance=100).
4. Session A's second read still returns 100 — consistent snapshot.
5. After session A commits, the undo log entry for balance=100 can be purged.

Output.

Rule of thumb. InnoDB reads old versions via undo log. The consistent snapshot travels through time transparently.

Worked example — Snowflake Time Travel query

Detailed explanation. Snowflake stores versioned micro-partitions. You can query any past state within the retention window.

Question. Query a table's state as of yesterday.

Code.

-- Query current state
SELECT * FROM orders WHERE id = 100;
-- id=100, status='shipped'

-- Query state as of 24 hours ago
SELECT * FROM orders AT (OFFSET => -60*60*24) WHERE id = 100;
-- id=100, status='pending' (before the shipment update)

-- Query state at specific timestamp
SELECT * FROM orders AT (TIMESTAMP => '2026-07-11 09:00:00'::TIMESTAMP)
WHERE id = 100;

-- Query state before a specific transaction
SELECT * FROM orders BEFORE (STATEMENT => '01a123-4b56-...') WHERE id = 100;

-- CLONE the entire table as of a past time
CREATE TABLE orders_yesterday CLONE orders AT (OFFSET => -60*60*24);

Step-by-step explanation.

1. Snowflake retains historical micro-partitions for DATA_RETENTION_TIME_IN_DAYS (1-90 days).
2. AT (OFFSET => ...) queries the state that many seconds ago.
3. AT (TIMESTAMP => ...) queries state at a specific timestamp.
4. BEFORE (STATEMENT => ...) queries state just before a specific query executed.
5. CLONE with AT creates a zero-copy snapshot table.

Output. Historical rows accessed via metadata.

Rule of thumb. Snowflake Time Travel is production-grade "undo" for accidental data loss. Set retention high (~30 days) on critical tables.

postgres mvcc interview question on MVCC cost and cleanup

A senior interviewer asks: "You have a Postgres OLTP table receiving 10K UPDATEs per second. It's grown from 100 GB to 500 GB in a week. What's happening, and what's the fix?"

Solution Using autovacuum tuning + monitoring dead tuples

-- Diagnose
SELECT
  relname,
  n_live_tup, n_dead_tup,
  last_autovacuum, autovacuum_count,
  round(100.0 * n_dead_tup / NULLIF(n_live_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables
WHERE relname = 'orders';

-- Check what's blocking autovacuum
SELECT age(datfrozenxid), datname
FROM pg_database
ORDER BY 1 DESC;

-- Check long-running txs
SELECT pid, now() - xact_start AS xact_duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
   OR state = 'active' AND now() - xact_start > interval '5 minutes'
ORDER BY xact_duration DESC;

-- Fix 1: kill offending long-running txs (or tune app connection pool)
SELECT pg_terminate_backend(:pid);

-- Fix 2: aggressive autovacuum for this table
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,   -- more frequent
  autovacuum_vacuum_cost_delay = 2,        -- less throttling
  autovacuum_vacuum_cost_limit = 2000      -- more work per run
);

-- Fix 3: emergency VACUUM if wraparound risk
VACUUM (VERBOSE, ANALYZE) orders;

-- Fix 4: consider partitioning by date so old data can be dropped, not vacuumed

Step-by-step trace.

Output:

Why this works — concept by concept:

• Dead tuples pile up when UPDATE-heavy workloads outpace autovacuum — default autovacuum settings assume steady-state OLTP; 10K UPDATE/sec exceeds that.
• Long-running txs pin the visibility horizon — VACUUM cannot reclaim tuples still visible to any active snapshot. Even an idle-in-tx connection can stall vacuum for the whole DB.
• Per-table autovacuum tuning — autovacuum_vacuum_scale_factor = 0.05 means vacuum triggers at 5% dead vs 20% default; more responsive.
• Emergency partitioning — for time-series data, partition by day and drop old partitions. No vacuum cost; disk reclaimed instantly.
• Wraparound risk — if datfrozenxid age approaches 2 billion, Postgres refuses new writes. Freeze immediately.

SQL
Topic — optimization
SQL optimization drills

Practice →

Practice →

4. Locking vs snapshot isolation

two-phase locking vs snapshot isolation — the two big families of concurrency-control implementations

The mental model in one line: there are two ways to guarantee isolation — either serialize concurrent accesses via locks (2PL: every read grabs a shared lock, every write an exclusive lock, all released together at commit — used by SQL Server default and MySQL InnoDB at SERIALIZABLE) or let readers ignore writers via snapshot reads (SI: every reader sees its own snapshot without locks, writers create new versions — used by Postgres, MySQL InnoDB RR, Snowflake, Oracle) — and both can implement serializable correctness (SS2PL for locking, SSI for snapshot).

Slot 1 — two-phase locking (2PL).

• Growing phase. Acquire locks as needed. Never release during this phase.
• Shrinking phase. Release locks. Never acquire during this phase.
• The invariant. Guarantees serializable execution when combined with strict scheduling.
• SS2PL (Strict Strong Two-Phase Locking). Hold all locks until commit. What SQL Server default SERIALIZABLE uses.
• S2PL (Strict Two-Phase Locking). Hold exclusive locks until commit; can release shared locks earlier. Less common.
• Deadlock possible. T1 holds A, waits on B. T2 holds B, waits on A. Deadlock. Engine detects and aborts one.

Slot 2 — lock types.

• Shared (S) lock. Multiple readers can hold S. Blocks writers. Grabbed by reads.
• Exclusive (X) lock. Single writer. Blocks all others. Grabbed by writes.
• Intent (IS, IX). Table-level locks indicating intent to lock rows in the table. Prevents another tx from taking a schema-level exclusive.
• Update (U) lock. SQL Server's variant. Allows read but signals upcoming write; only one U at a time. Prevents deadlock in read-then-write patterns.
• Range lock. Covers a range of keys, preventing phantoms. Used by SS2PL SERIALIZABLE.
• Row lock vs page lock. Modern engines grab row-level locks. Under lock-count pressure, may escalate to page or table.

Slot 3 — snapshot isolation.

• Every tx gets a snapshot at BEGIN. Records the current "visibility horizon" — which committed txs are visible.
• Reads never block. Reader sees the snapshot; no lock needed.
• Writes create new versions. Old version stays; new version has the writing tx's id.
• Write-write conflict detection. If two txs write the same row, one commits and the other's write is rejected with serialization_failure.
• Read-write compatibility. Reader sees the pre-write snapshot; writer proceeds independently.
• No blocking (for reads). Massive throughput advantage vs 2PL.
• Not serializable per se. Allows write skew.

Slot 4 — deadlocks under 2PL.

• Cause. Circular wait — T1 holds X on A, waits on B; T2 holds Y on B, waits on A.
• Detection. Engines have deadlock detectors that check the wait-for graph periodically.
• Resolution. Abort one tx (usually the youngest or the least-work-done). Application catches the abort and retries.
• Postgres error code. 40P01 (deadlock_detected).
• MySQL InnoDB error code. 1213 (ER_LOCK_DEADLOCK).
• Prevention. Acquire locks in consistent order across all txs. Every senior review comment.

Slot 5 — deadlocks under snapshot isolation.

• Reads don't deadlock. No lock on reads.
• Write-write conflict. Not a deadlock per se — one write proceeds, the other gets serialization_failure.
• Explicit lock deadlocks. If you use SELECT FOR UPDATE, deadlocks can still happen.
• CockroachDB / SSI. Deadlock-free by design because it uses OCC (optimistic concurrency control) — validation on commit.

Slot 6 — read locks vs write locks.

• Under 2PL. Reads grab S (shared); writes grab X (exclusive). S+S compatible; S+X and X+X block.
• Under snapshot. Reads take no locks. Writes grab X during modification, released at commit.
• SELECT FOR UPDATE. Explicit request for X lock on the selected rows. Works under any isolation level.
• SELECT LOCK IN SHARE MODE (MySQL). Explicit S lock. Rare use case.
• Postgres FOR UPDATE. Same semantic. Also FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE for finer control.

Slot 7 — the trade-off matrix.

Slot 8 — when 2PL beats snapshot.

• Very low contention. Snapshot's version overhead isn't worth it.
• Very short txs. SS2PL's lock hold time is negligible.
• Correctness-critical. SS2PL is naturally serializable; no application-side reasoning needed.
• Legacy applications. Assumed 2PL semantics for decades.
• SQL Server workloads. Default SS2PL is battle-tested.

Slot 9 — when snapshot beats 2PL.

• High read/write concurrency. Snapshot lets readers not block writers.
• Long-running reads. Analytics against OLTP. Snapshot gives consistency without locking out writers.
• Modern OLTP. Snapshot is the default because it's cheaper for most workloads.
• When application accepts write skew or uses explicit locks. Snapshot + SELECT FOR UPDATE covers 95% of cases at RC.

Slot 10 — hybrid approaches.

• SQL Server Snapshot / RCSI. SQL Server supports snapshot isolation as an opt-in. ALTER DATABASE X SET READ_COMMITTED_SNAPSHOT ON — RC becomes RCSI (row-versioned RC). Best of both worlds.
• MySQL InnoDB. Default RR is already snapshot with gap locks (extra safety over pure SI).
• Oracle. Snapshot for reads by default; SS2PL for writes.

Common beginner mistakes

• Assuming SERIALIZABLE = 2PL. It can be SSI too.
• Deadlocking under SELECT FOR UPDATE by taking locks in inconsistent order.
• Using SS2PL SERIALIZABLE for read-heavy workloads and complaining about throughput.
• Ignoring the storage cost of MVCC (VACUUM, purge, Time Travel).
• Not catching serialization_failure in application retry logic.

Worked example — reproducing a classic deadlock under 2PL

Detailed explanation. Two sessions each update two rows in opposite orders.

Question. Show the schedule that deadlocks.

Code.

-- Session A
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
-- Session B interleaves here
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
COMMIT;

-- Session B
BEGIN;
UPDATE accounts SET balance = balance - 5 WHERE id = 2;
-- Session A interleaves here
UPDATE accounts SET balance = balance + 5 WHERE id = 1;
COMMIT;

Step-by-step explanation.

1. Session A acquires X lock on row 1.
2. Session B acquires X lock on row 2.
3. Session A tries to lock row 2 → blocks (B has X).
4. Session B tries to lock row 1 → blocks (A has X).
5. Deadlock — circular wait.
6. Engine detects, aborts one tx (usually the youngest). Application retries.
7. Fix — always lock rows in the same order (e.g., always by id ascending).

Output.

Rule of thumb. In multi-row transactions, always lock rows in a consistent order (e.g., ORDER BY id). Prevents deadlocks.

Worked example — snapshot isolation lets reads flow around writes

Detailed explanation. Under SI, a long read doesn't block a concurrent write.

Question. Show that a Postgres analytical query doesn't block a concurrent write.

Code.

-- Session A (long analytical read)
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*), SUM(revenue) FROM orders;  -- takes 5 seconds

-- Session B (concurrent write, no wait)
BEGIN;
UPDATE orders SET status = 'shipped' WHERE id = 42;
COMMIT;   -- succeeds instantly

-- Session A completes (still sees old status for order 42)
COMMIT;

Step-by-step explanation.

1. Session A starts RR tx. Snapshot taken at BEGIN.
2. Session A begins the long SUM query.
3. Session B updates orders row and commits — takes X lock on row 42, releases on commit. No wait on A.
4. Session A's SELECT continues, sees the snapshot (order 42 status = old value).
5. Both txs commit successfully. Session A's result reflects the snapshot state.

Output. A's SUM includes the pre-update value for order 42.

Rule of thumb. Snapshot isolation is the killer feature for mixed workloads. Analytics + OLTP can share the same DB without one blocking the other.

Worked example — SELECT FOR UPDATE on snapshot isolation

Detailed explanation. Even under SI, explicit locks work. SELECT FOR UPDATE upgrades a read to hold an X lock.

Question. Show how SELECT FOR UPDATE prevents the write skew doctor-on-call example.

Code.

-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM doctors WHERE on_call FOR UPDATE;   -- returns 2, locks all matching rows
-- Application check: 2 >= 1, safe
UPDATE doctors SET on_call = FALSE WHERE id = 1;
COMMIT;

-- Session B
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM doctors WHERE on_call FOR UPDATE;   -- BLOCKS
-- ... waits for A to commit ...
-- After A commits: returns 1
-- Application check: 1 >= 1, but if A also went off, check fails
UPDATE doctors SET on_call = FALSE WHERE id = 2;   -- (won't execute)
ROLLBACK;

Step-by-step explanation.

1. Session A takes X locks on all on_call=TRUE rows (via FOR UPDATE).
2. Session B's SELECT FOR UPDATE blocks because A holds the locks.
3. Session A commits (or rolls back). B unblocks.
4. B re-reads with fresh values (count=1 now). Application check fails. B rolls back.
5. Invariant preserved.

Output.

Rule of thumb. SELECT FOR UPDATE is the escape hatch for write skew under SI. When the invariant depends on a set-level property, lock the set.

two-phase locking interview question on choosing 2PL vs SI

A senior interviewer asks: "You're picking between SQL Server (default SS2PL) and Postgres (default RC on SI-based MVCC) for a new OLTP + analytics system. Compare on: concurrency, deadlock risk, analytics-on-OLTP, and operational cost."

Solution Using engine comparison + practical trade-offs

CRITERION           | SQL Server (SS2PL)      | Postgres (MVCC + SSI)
--------------------|-------------------------|----------------------
Read blocking       | Reads block on X locks  | Reads never block
Write blocking      | Writes block on X locks | Writes only block on same-row conflict
Deadlock risk       | High                    | Low (reads don't deadlock)
Analytics-on-OLTP   | Painful (reads block)   | Native (snapshot)
Storage overhead    | Low                     | Dead tuples until VACUUM
Serializable cost   | Range locks + hold      | SSI validation on commit
Retry logic needed  | On deadlock             | On serialization_failure
Enable snapshot?    | RCSI opt-in             | Always on
Default level       | READ COMMITTED          | READ COMMITTED
Max level           | SERIALIZABLE (SS2PL)    | SERIALIZABLE (SSI)

Step-by-step trace.

Choose Postgres when:

• Read-heavy or mixed workload.
• Analytics run against OLTP.
• Team comfortable with VACUUM operations.
• Need cheap SERIALIZABLE via SSI.

Choose SQL Server when:

• Legacy system with SS2PL semantics.
• Team more comfortable with SQL Server tooling.
• Enable RCSI opt-in to get snapshot benefits.

Output:

Why this works — concept by concept:

• 2PL blocks reads on writes — SS2PL takes S locks on reads; long writes hold X; reads wait. Painful under contention.
• MVCC snapshot lets reads flow — Postgres reads never block. Analytics stays fast on busy OLTP.
• SSI vs SS2PL — both give serializable, but SSI aborts on conflict (retry model) vs SS2PL blocks (waiting model). SSI wins under most modern workloads.
• VACUUM cost — the price of MVCC. Managed via autovacuum; monitor dead tuples.
• Cost — snapshot at high concurrency: linear in tx count with periodic vacuum. 2PL at high concurrency: quadratic worst case due to blocking chains.

SQL
Topic — indexing
SQL indexing drills

Practice →

Practice →

5. Serializable snapshot isolation + dialect matrix

serializable snapshot isolation (SSI) — combining MVCC's read throughput with commit-time validation for true serializability, and the 8-engine dialect matrix

The mental model in one line: SSI is a clever hybrid that keeps snapshot isolation's read-never-blocks-writer performance but adds commit-time validation to detect when concurrent txs would have violated serializable semantics; on conflict, one tx aborts with serialization_failure and the application retries — the trade-off is that SSI is optimistic (assumes no conflict, pays cost on conflict) whereas SS2PL is pessimistic (blocks preemptively).

Slot 1 — SSI internals.

• SIREAD locks. Postgres tracks "read dependency" via lightweight SIREAD locks — records what each tx has read.
• rw-dependency graph. As txs read and write, edges are added. A cycle = potential serializability violation.
• Cycle detection at commit. When a tx tries to commit, Postgres checks whether committing would create a cycle. If yes, abort with serialization_failure.
• Read-only optimization. Purely-reading txs never cause aborts. Only writes contribute to cycle risk.
• Cost. ~10-30% overhead vs snapshot RR under low contention; higher under high contention (more aborts).

Slot 2 — how SSI catches write skew.

• Doctor-on-call example. T1 reads doctors WHERE on_call. T2 reads same. T1 writes doctor 1 off. T2 writes doctor 2 off.
• SSI tracks. T1 read doctors; T1 wrote doctors. T2 read doctors; T2 wrote doctors.
• Cycle detected. T1's read depends on T2's write (T2 wrote doctor 2 which T1 saw as on_call). Similarly T2's read depends on T1's write.
• On commit of second. SSI raises serialization_failure. Application retries; retry sees the write of the first, refuses to go off. Invariant preserved.

Slot 3 — CockroachDB SERIALIZABLE.

• Default level. SERIALIZABLE via SSI.
• Different implementation. Uses a timestamp cache to detect conflicts.
• Multi-node MVCC. Snapshots across a distributed system.
• Retry on conflict. Application must catch 40001 and retry.
• Read-you-write. Guaranteed within a tx.

Slot 4 — YugabyteDB SERIALIZABLE.

• Distributed SSI. Similar to CockroachDB.
• Postgres-compatible. Same SQL surface.
• Global consistency. Serializable across regions.

Slot 5 — Oracle SERIALIZABLE.

• Actually snapshot isolation. Oracle calls SI "SERIALIZABLE" but technically allows write skew (per Berenson).
• ORA-08177. Serialization failure error code.
• Practical behavior. Detects certain conflicts and raises.

Slot 6 — MySQL InnoDB SERIALIZABLE.

• Uses gap locks + next-key locks. Every SELECT becomes SELECT LOCK IN SHARE MODE.
• Full 2PL SERIALIZABLE. Expensive.
• Rarely used. Most InnoDB workloads use RR (SI-flavoured with gap locks).

Slot 7 — SQL Server SERIALIZABLE.

• SS2PL. Range locks on WHERE-matched key ranges.
• Very expensive. Blocks heavily under concurrency.
• RCSI alternative. For snapshot-based consistency without full SS2PL cost.

Slot 8 — the 8-engine dialect matrix.

Slot 9 — retry patterns.

• Postgres 40001 (serialization_failure) or 40P01 (deadlock). Both should trigger retry.
• Retry limit. 3-5 attempts typical. Backoff between retries.
• Idempotent tx. Retry-safe only if tx is idempotent. Design accordingly.
• Framework support. Django transaction.atomic() doesn't retry by default; Rails, Sequel, sqlx have retry decorators.
• Application pattern.

from psycopg import errors
def with_retry(fn, max_retries=3):
    for attempt in range(max_retries):
        try:
            return fn()
        except (errors.SerializationFailure, errors.DeadlockDetected):
            if attempt == max_retries - 1: raise
            time.sleep(0.01 * 2 ** attempt)  # exponential backoff

Slot 10 — when SERIALIZABLE is worth it.

• Cross-row invariants. Doctor-on-call, sum-of-hours-<-40, unique-across-sibling-rows.
• Complex "read set, decide, write" logic. Where atomic UPDATE isn't feasible.
• Team can handle retry logic. Not everywhere.
• Postgres SSI is affordable. Just retry.
• When throughput matters more. Stay at RC + application-side care.

Common beginner mistakes

• Not catching serialization_failure in retry logic.
• Retrying forever without exponential backoff.
• Setting SERIALIZABLE globally without understanding the retry cost.
• Confusing Oracle "SERIALIZABLE" (snapshot) with true SSI.
• Not knowing SQL Server's SERIALIZABLE is SS2PL (heavy).

Worked example — the SSI abort and retry

Detailed explanation. Show the doctor-on-call scenario producing a serialization_failure on Postgres SSI.

Code.

-- Session A
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM doctors WHERE on_call;  -- returns 2
UPDATE doctors SET on_call = FALSE WHERE id = 1;
-- do not commit yet

-- Session B
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM doctors WHERE on_call;  -- returns 2 (snapshot)
UPDATE doctors SET on_call = FALSE WHERE id = 2;
COMMIT;  -- succeeds

-- Session A tries to commit
COMMIT;
-- ERROR: could not serialize access due to read/write dependencies among transactions
-- Detail: Reason code: Canceled on identification as a pivot, during commit attempt.

Step-by-step explanation.

1. Both txs read the doctors WHERE on_call set. SIREAD locks record the read.
2. Both txs write different doctors. Each write is visible only to itself until commit.
3. B commits first. Post-commit, rw-dep from A → B (A's read overlaps B's write). And A → B via rw-dep as well.
4. A tries to commit. SSI detects cycle: A read what B wrote AND B read what A is writing.
5. A aborted with 40001. Application catches and retries.
6. Retry sees B's committed state (only 1 doctor on). Refuses.

Output. A aborts; retry sees fresh state; invariant preserved.

Rule of thumb. SSI turns write skew into a retry event. Cheap, transparent, correct.

Worked example — Postgres SERIALIZABLE with retry logic

Detailed explanation. Wrap tx logic in a retry loop.

Code.

import psycopg
from psycopg import errors
import time

def transfer(from_id, to_id, amount):
    for attempt in range(5):
        try:
            with psycopg.connect(DSN) as conn:
                conn.autocommit = False
                conn.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
                cur = conn.cursor()

                cur.execute("SELECT balance FROM accounts WHERE id = %s", (from_id,))
                from_bal = cur.fetchone()[0]
                if from_bal < amount:
                    raise InsufficientFunds()
                cur.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s",
                            (amount, from_id))
                cur.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s",
                            (amount, to_id))
                conn.commit()
                return
        except errors.SerializationFailure:
            if attempt == 4:
                raise
            time.sleep(0.01 * 2 ** attempt)

Step-by-step explanation.

1. Wrap in retry loop with exponential backoff.
2. Set isolation level per-tx.
3. Do the tx logic.
4. On SerializationFailure, retry.
5. On max retries, raise.

Output. Success on the first successful tx; retry on conflict.

Rule of thumb. SERIALIZABLE requires retry logic. Build it into your data-access layer.

Worked example — CockroachDB serializable by default

Detailed explanation. CockroachDB is SERIALIZABLE by default. Application must catch retries.

Code.

-- CockroachDB
BEGIN;
-- Auto SERIALIZABLE
UPDATE users SET karma = karma + 1 WHERE id = 42;
COMMIT;

Step-by-step explanation.

1. CockroachDB's default isolation is SERIALIZABLE.
2. Every tx is subject to conflict detection.
3. On conflict, application receives 40001 and retries.
4. CockroachDB has automatic retries for some patterns (SAVEPOINT cockroach_restart).
5. Application should still expect retries.

Output. Correct execution with automatic serializable guarantees.

Rule of thumb. CockroachDB's default is what most other engines make you opt into. Trade-off is retry cost is the norm.

serializable interview question on choosing the level

A senior interviewer asks: "You're building a scheduling system where the invariant is 'total hours per employee per week <= 40'. Design the SQL with the right isolation level and error handling."

Solution Using SERIALIZABLE + retry + trigger validation

CREATE TABLE assignments (
  id BIGSERIAL PRIMARY KEY,
  employee_id INT NOT NULL,
  week INT NOT NULL,
  hours NUMERIC(4,1) NOT NULL,
  CHECK (hours BETWEEN 0 AND 40)
);

CREATE INDEX ON assignments(employee_id, week);

-- Application code (Python)
def assign(employee_id, week, hours):
    for attempt in range(5):
        try:
            with psycopg.connect(DSN) as conn:
                conn.autocommit = False
                conn.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
                cur = conn.cursor()

                cur.execute("""
                  SELECT COALESCE(SUM(hours), 0) FROM assignments
                  WHERE employee_id = %s AND week = %s
                """, (employee_id, week))
                current = cur.fetchone()[0]
                if current + hours > 40:
                    raise OverAllocated()
                cur.execute("""
                  INSERT INTO assignments (employee_id, week, hours)
                  VALUES (%s, %s, %s)
                """, (employee_id, week, hours))
                conn.commit()
                return
        except errors.SerializationFailure:
            if attempt == 4:
                raise
            time.sleep(0.02 * 2 ** attempt)

Step-by-step trace.

Output: SERIALIZABLE catches write skew; retry succeeds with correct state.

Why this works — concept by concept:

• SERIALIZABLE catches write skew — sum-of-hours invariant would fail under RR. SSI aborts one tx on conflict.
• Retry logic in application — SSI is optimistic; conflicts require app retry.
• CHECK constraint as backup — even if application logic slips, CHECK (hours BETWEEN 0 AND 40) catches obviously wrong values.
• Index on (employee_id, week) — reads and predicate matches use the index. SSI's read-set tracking works efficiently.
• Exponential backoff — prevents retry storms under high contention.

SQL
Topic — SQL
SQL practice library

Practice →

SQL
Topic — optimization
SQL optimization drills

Practice →

Cheat sheet — transactions + isolation recipe list

• Four levels. RU (RC on Postgres), RC, RR (SI on Postgres/InnoDB), SERIALIZABLE.
• Anomalies. Dirty read, non-repeatable read, phantom, lost update, write skew, read-only-tx.
• Postgres default. READ COMMITTED. Snapshot semantics on RR.
• MySQL InnoDB default. REPEATABLE READ (SI-flavoured with gap locks).
• SQL Server default. READ COMMITTED (no SI unless RCSI opt-in).
• CockroachDB default. SERIALIZABLE (SSI).
• Set per tx. BEGIN ISOLATION LEVEL SERIALIZABLE; or SET TRANSACTION ISOLATION LEVEL RR.
• Atomic UPDATE beats read-modify-write. UPDATE counter SET n = n + 1 WHERE id = X — no isolation upgrade needed.
• SELECT FOR UPDATE for explicit lock. When you must read, decide, and write in application code.
• SSI aborts with 40001. Application must catch and retry.
• Deadlock aborts with 40P01 (Postgres) or 1213 (MySQL). Also retryable.
• Consistent lock order. Prevents deadlocks. ORDER BY id ASC for multi-row locks.
• Idempotent tx design. Retry-safe only if tx is idempotent.
• Exponential backoff. On retry, sleep 10ms × 2^attempt.
• Postgres MVCC via xmin/xmax. Every tuple carries visibility metadata.
• InnoDB MVCC via undo log. Old versions in separate log; purge cleans up.
• Snowflake MVCC via versioned micro-partitions. Time Travel for query at past state.
• VACUUM (autovacuum) reclaims dead tuples on Postgres. Tune per table for heavy write.
• Long-running txs are toxic. MVCC can't reclaim; storage grows.
• idle_in_transaction_session_timeout. Postgres setting to auto-kill leaked idle txs.
• CDC on Postgres uses logical replication. Requires wal_level=logical + replication slots.
• SELECT FOR NO KEY UPDATE. Weaker lock; allows FK checks.
• SELECT FOR SHARE. S lock; blocks writers but not other readers.
• 2PL: read locks vs write locks. S compatible with S; X excludes all.
• Snapshot isolation trade-off. Great throughput, allows write skew.
• SSI trade-off. Serializable but retry on conflict.
• SERIALIZABLE READ ONLY on Postgres. Optimized read-only serializable tx — never blocks and never gets aborted.
• Retry limit 3–5 attempts. Beyond that, likely persistent conflict; fail the request.
• Rethink app-level locking. Advisory locks (Postgres pg_advisory_lock) for cross-tx coordination outside the row model.

Frequently asked questions

What's the difference between READ COMMITTED and REPEATABLE READ?

READ COMMITTED (RC) — each SELECT sees only committed data as of the moment the SELECT runs. Consequence: successive SELECTs in the same tx can see different values because other txs commit in between (non-repeatable read). Cheap, fast, sufficient for most stateless reads. REPEATABLE READ (RR) — the tx sees a consistent snapshot of the DB as of BEGIN. All reads within the tx see the same version of every row, even if other txs commit changes concurrently. Prevents non-repeatable reads. On Postgres and InnoDB, RR is implemented as snapshot isolation — it also prevents phantom reads (which ANSI RR doesn't require). Cost — modest overhead to maintain the snapshot and preserve old row versions. Rule of thumb — use RC for stateless reads and simple check-then-write patterns; use RR for reports that need multi-row consistency; use SERIALIZABLE for cross-row invariants that would fail under snapshot isolation's write-skew.

What is snapshot isolation and how does it differ from SERIALIZABLE?

Snapshot isolation (SI) — every tx starts with a snapshot of the DB. All reads see the snapshot. Writes create new versions of rows visible only to the writing tx until commit. On write-write conflict, one tx aborts. SI prevents dirty read, non-repeatable read, phantom, and lost update, but allows write skew — two txs each reading a set of rows and writing based on a set-level property can both commit and violate joint invariants. SERIALIZABLE — guarantees the result is equivalent to some serial execution of all concurrent txs. Prevents write skew too. Implementations: SS2PL (SQL Server default) uses locks — slow under contention. SSI (Postgres 9.1+ SERIALIZABLE, CockroachDB) uses SI plus commit-time validation — aborts a tx if committing would violate serializability. SSI is optimistic; SS2PL is pessimistic. Rule of thumb: Postgres users at RR are on SI (fast, might have write skew). SERIALIZABLE upgrade prevents write skew via SSI at the cost of ~10-30% overhead and retry logic on conflict.

How does MVCC handle concurrent writes?

MVCC (multi-version concurrency control) stores multiple versions of each row so that readers can see a consistent snapshot while writers create new versions without blocking readers. Postgres tags every tuple with xmin (creating tx-id) and xmax (deleting tx-id or 0). Readers see tuples whose xmin is committed-and-visible and whose xmax is either 0 or not-visible. UPDATE = insert new tuple + set old tuple's xmax. Old tuples reclaimed by VACUUM. MySQL InnoDB stores the current row in the table and old versions in an undo log linked via DB_ROLL_PTR. Readers follow the undo chain to find their snapshot's version. Purge thread reclaims old undo entries. Snowflake uses immutable versioned micro-partitions with Time Travel for historical access. Concurrent writes on the same row — MVCC detects via write-write conflict; on Postgres/InnoDB, the later writer waits for the earlier to commit or abort (RC/RR) or aborts with serialization_failure (SSI). On Snowflake, MERGE and UPDATE serialise on the target's micro-partition boundary. Rule of thumb — MVCC gives high read throughput with no blocking; the trade-off is storage overhead until dead versions are cleaned up.

What's the write skew anomaly and how do I prevent it?

Write skew — two concurrent txs each read the same set of rows (or an aggregate over them), each makes a decision based on the read, and each writes to different rows. Individually, each tx would preserve the invariant; together, they violate it. Example: two doctors want to go off call while the invariant is "at least one doctor on call." Both txs read count=2 (from their snapshots), both decide to go off, both commit different rows — final count=0. Under RC or RR (SI), this is allowed. Preventions: (1) SELECT FOR UPDATE on the row set — first tx locks, second tx blocks until first commits, then re-reads and sees only 1 doctor on call. (2) SERIALIZABLE via SSI — Postgres detects the rw-dependency cycle and aborts one tx with serialization_failure. Application retries; retry sees the fresh state. (3) Materialized constraint — a table on_call_count maintained via triggers; the constraint prevents count from dropping below 1. Rule of thumb — any invariant that spans multiple rows and depends on a set-level property (count, sum, existence) is vulnerable to write skew unless you use SERIALIZABLE or explicit locking.

When should I use SELECT FOR UPDATE?

Use SELECT FOR UPDATE when your application does read → decide → write in separate SQL statements and the decision depends on the current value (not derivable from an atomic UPDATE expression). Examples: inventory check with complex validation, credit-limit check with multi-table joins, session-state update with computed fields, workflow-state transitions with business logic in application code. SELECT ... FOR UPDATE acquires an exclusive row lock on the selected rows; concurrent writes to those rows block until the lock is released at commit. Do NOT use when a simple atomic UPDATE with a WHERE guard works — UPDATE t SET n = n + 1 WHERE id = X is race-free without any FOR UPDATE. Prefer over SERIALIZABLE when you want explicit locking semantics rather than retry-on-conflict. Lock in consistent order across all txs to prevent deadlocks (e.g., ORDER BY id ASC before FOR UPDATE). Postgres variants: FOR NO KEY UPDATE (allows FK reads on the row), FOR SHARE / FOR KEY SHARE (weaker locks for read-only or FK-checking flows). Rule of thumb — reach for FOR UPDATE when the application logic between read and write is too complex to encode in a single atomic statement.

What causes serialization_failure and how do I handle it?

serialization_failure (SQLSTATE 40001) is raised when the engine detects that committing your transaction would violate serializable semantics. On Postgres SERIALIZABLE (SSI), the SIREAD-lock system tracks rw-dependencies between concurrent txs; on commit, it checks for cycles in the dependency graph. If your tx would close a cycle, it's aborted. On CockroachDB and YugabyteDB, similar logic runs on every tx (they're SERIALIZABLE by default). On Oracle, ORA-08177 is raised for detected write-write conflicts and some read-write conflicts. Handle it via retry logic — catch the error, wait a small backoff (10ms * 2^attempt), retry the entire tx. Retry limit 3-5 attempts; beyond that, likely persistent contention or bug. Design txs to be idempotent — retry-safe. Not always trivial. Framework support — some ORMs (Django with transaction.atomic) don't retry; others (Sequel, sqlx) offer decorators. Roll your own retry decorator if needed. Don't just log and continue — the tx did NOT commit; the invariant was NOT satisfied. Rule of thumb — SERIALIZABLE = retry-on-conflict is the design. Build the retry loop into your data-access layer once; benefit forever.

Practice on PipeCode

• Drill the SQL practice library → — 450+ DE-focused questions covering transactions, isolation, MVCC, and concurrency patterns.
• Sharpen SQL optimization drills → — read concurrent-write execution plans and diagnose lock contention.
• Layer SQL indexing drills → — unique indexes are the storage layer that makes atomic UPSERT and MERGE actually atomic.
• Warm up with SQL join drills → — cross-row invariants and write skew often surface in join-heavy multi-table update patterns.
• For the broader SQL interview surface, take the SQL for Data Engineering course →.

Pipecode.ai is Leetcode for Data Engineering — every `sql isolation levels mvcc` recipe above ships with hands-on practice rooms where you set `BEGIN ISOLATION LEVEL SERIALIZABLE` on a live Postgres, reproduce the doctor-on-call write skew under REPEATABLE READ, watch SSI abort the second commit with `serialization_failure`, migrate the retry logic into a Python decorator, benchmark MVCC dead-tuple growth under high UPDATE throughput, and finally tune autovacuum for the exact table shape you're running — the exact concurrency fluency that senior DE and backend interviews probe. PipeCode pairs every transaction and isolation concept with 450+ DE-focused problems and a real-time scoring engine, so you never have to wonder whether your `read committed` / `repeatable read` / `serializable` answer holds up under a senior interviewer's depth probes.

Practice SQL now →
Optimization drills →