Transactions & Isolation Levels - Complete Deep Dive

Prerequisites: Database Indexing, Terminology Used in: Digital Wallet, Ticket Booking, any design that touches money, inventory, or seats

What is a transaction?

A transaction groups multiple reads and writes into one unit that either fully happens or fully doesn’t.

Real-world analogy: Transferring ₹500 between accounts is two writes — debit A, credit B. Without a transaction, a crash between them destroys ₹500. The transaction makes the pair atomic: both, or neither.

BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE id = 'A';
  UPDATE accounts SET balance = balance + 500 WHERE id = 'B';
COMMIT;      -- both visible at the same instant
-- or ROLLBACK; -- neither ever happened

ACID, precisely

Most candidates can expand the acronym. Few can say what each letter actually protects against.

⚠️ Atomicity is not about concurrency. Atomicity is about crashes and aborts; isolation is about other transactions. People blur these constantly. And the C in ACID is not the C in CAP — see Terminology.

Durability has a caveat worth mentioning: “committed” only means “written to the WAL and fsync’d.” If synchronous_commit=off, or the disk lies about flushing, or replication is async and the primary dies — you can lose an acknowledged commit. Durability is a spectrum, and its knob is synchronous_commit.

The read anomalies (in severity order)

Isolation levels are defined by which of these they permit. Learn the anomalies first; the levels then define themselves.

1. Dirty read — reading uncommitted data

T1: UPDATE accounts SET balance = 0 WHERE id='A';   -- not committed
T2: SELECT balance FROM accounts WHERE id='A';      -- reads 0  ← dirty
T1: ROLLBACK;                                       -- that 0 never existed

T2 acted on data that never existed. Almost every real database prevents this by default.

2. Dirty write — overwriting uncommitted data

Two transactions both write the same row while the other is in flight, and the losing write vanishes. Prevented everywhere by row-level write locks.

3. Non-repeatable read (read skew) — same row, different values

T1: SELECT balance FROM accounts WHERE id='A';   -- 500
T2: UPDATE accounts SET balance=100 WHERE id='A'; COMMIT;
T1: SELECT balance FROM accounts WHERE id='A';   -- 100  ← changed mid-transaction

Breaks anything that reads a row twice, or reads two rows expecting a consistent snapshot (a backup that captures A pre-transfer and B post-transfer shows money that doesn’t exist).

4. Phantom read — the set of matching rows changes

T1: SELECT COUNT(*) FROM bookings WHERE room=5 AND date='2026-08-01';  -- 0
T2: INSERT INTO bookings VALUES (room 5, '2026-08-01'); COMMIT;
T1: SELECT COUNT(*) ...;                                              -- 1  ← a phantom appeared

The rows T1 read didn’t change — new rows matched its predicate. This is why row locks aren’t enough; you need predicate/range locks (or SSI).

5. Lost update — read-modify-write races

Both read counter = 10
T1: writes 11
T2: writes 11        ← should be 12; one increment vanished

Fix with an atomic write (SET n = n + 1), an explicit lock (SELECT FOR UPDATE), or a compare-and-set on a version column.

6. Write skew — the subtle one interviewers love

Two transactions read the same set, each makes a decision, each writes a different row — so no write conflict is detected, but the invariant breaks.

Rule: at least one doctor must always be on call.
Currently: Alice and Bob are both on call.

T1 (Alice): SELECT COUNT(*) FROM oncall WHERE shift='night';  -- 2, fine, I can leave
T2 (Bob):   SELECT COUNT(*) FROM oncall WHERE shift='night';  -- 2, fine, I can leave
T1: UPDATE oncall SET on_call=false WHERE dr='Alice'; COMMIT;
T2: UPDATE oncall SET on_call=false WHERE dr='Bob';   COMMIT;

→ zero doctors on call. Both transactions were individually valid.

This survives REPEATABLE READ / snapshot isolation. Different rows were written, so no conflict. Only SERIALIZABLE (or a manual lock on a shared row / a materialized conflict row) prevents it.

Write skew is the bug behind double-spending a wallet balance, overselling the last ticket, and duplicate-username races.

The four isolation levels

* Implementation-dependent — see the vendor table below. This is the part the SQL standard gets wrong and interviewers get wrong with it.

What the defaults actually are

⚠️ The trap: “Postgres defaults to Repeatable Read” is wrong (it’s Read Committed), and “Repeatable Read prevents phantoms” is incidentally true in Postgres and MySQL but not required by the standard. Say which database you mean.

Say this in an interview: “Postgres defaults to Read Committed, which means each statement sees a fresh snapshot — so a read-modify-write in application code can lose an update. For the balance check I’d either use SELECT ... FOR UPDATE, an atomic balance = balance - ? with a CHECK (balance >= 0) constraint, or bump to SERIALIZABLE and handle retries.”

MVCC — how modern databases avoid read locks

Multi-Version Concurrency Control: writers create new row versions instead of overwriting, so readers never block writers and writers never block readers.

Row 'A' physical versions in the heap:

 version | balance | created_by_txn | deleted_by_txn
---------|---------|----------------|----------------
    v1   |   500   |      100       |      105
    v2   |   100   |      105       |      NULL      ← current

Transaction 103 (snapshot taken before txn 105 committed) reads v1 → 500
Transaction 110 reads v2 → 100
Neither takes a read lock. Neither waits.

Consequences you should be able to name:

• Readers don’t block writers — the main reason MVCC won.
• Old versions accumulate → Postgres needs VACUUM; without it you get table bloat. This is a real operational concern in high-write tables.
• Long-running transactions are dangerous — they hold back the vacuum horizon, so a forgotten analytics query pins millions of dead tuples.
• Transaction ID wraparound — Postgres XIDs are 32-bit; if vacuum falls far enough behind the DB stops accepting writes to protect itself. It’s a famous outage cause.

MVCC vs locking:

Serializable: two ways to get there

Actual serial execution

Run transactions one at a time on a single thread (Redis, VoltDB, and Redis-Lua scripts effectively). Perfect isolation, zero concurrency — only viable if every transaction is tiny and in-memory.

Two-Phase Locking (2PL)

Acquire locks as you go, release only at commit. Correct but slow: lots of blocking, deadlocks (the DB detects and kills a victim), and predicate locks needed for phantoms.

Serializable Snapshot Isolation (SSI) — what Postgres does

Optimistic: run under snapshot isolation, track read/write dependencies, and abort a transaction at commit if the interleaving couldn’t have been serial.

Great when conflicts are rare  → near-snapshot-isolation performance
Bad when contention is high    → high abort rate, and your app MUST retry

⚠️ If you say “SERIALIZABLE” in an interview, immediately say “and the application retries on serialization failure (Postgres SQLSTATE 40001).” Forgetting the retry loop is the #1 real-world SSI bug.

// The retry loop that has to exist
for (int attempt = 0; attempt < 3; attempt++) {
    try {
        tx.begin();  // SERIALIZABLE
        transfer(from, to, amount);
        tx.commit();
        return;
    } catch (SerializationFailureException e) {   // 40001
        tx.rollback();
        sleep(jitteredBackoff(attempt));
    }
}
throw new TooMuchContentionException();

Preventing lost updates and write skew in practice

Four options, in order of how often you should reach for them:

1. Atomic write operation — best when it fits

-- The DB does read-modify-write in one lock-protected step
UPDATE accounts SET balance = balance - 500
WHERE id = 'A' AND balance >= 500;
-- 0 rows updated → insufficient funds, no race possible

Add CHECK (balance >= 0) and the invariant is enforced by the DB, not by hope.

2. Optimistic concurrency (compare-and-set on a version)

UPDATE docs SET content = ?, version = version + 1
WHERE id = ? AND version = ?;   -- 0 rows → someone else won, reload and retry

Great for user-facing edits (you can show “this changed, reload?”). No locks held across user think-time.

3. Explicit pessimistic lock

BEGIN;
SELECT * FROM seats WHERE id = 42 FOR UPDATE;   -- others block here
-- decide, then write
COMMIT;

Right for high contention (last seat, hot inventory row). Beware: FOR UPDATE held across a network call is how you build a deadlock factory. Also consider FOR UPDATE SKIP LOCKED — the standard way to build a job queue on Postgres.

4. Materialize the conflict Write skew happens because there’s no shared row to conflict on. So create one: a room_bookings_lock row per (room, date) that both transactions must update. Now the DB sees a write-write conflict and one aborts. Ugly, but it turns an invisible anomaly into a detectable one.

Distributed transactions

Single-node ACID doesn’t survive being spread across services or shards.

Two-Phase Commit (2PC)

Coordinator                     Participants (DB-A, DB-B)
    |-- PREPARE ------------------->  write to log, lock rows, reply "ready"
    |<-- ready, ready -------------
    |-- COMMIT -------------------->  commit, release locks
    |<-- done, done ---------------

Why it’s avoided: it’s a blocking protocol. If the coordinator dies after PREPARE, participants sit holding locks — indefinitely, until an operator intervenes. Add a network partition and you’ve frozen writes on both databases. It also multiplies your unavailability: the transaction needs every participant up.

When it’s still right: within one datacenter, across a small fixed set of resources, with a highly available coordinator — e.g. XA between a DB and a message broker (though the outbox pattern is the better answer there).

Saga — the microservices answer

Break the distributed transaction into local transactions, each with a compensating action.

Book flight    → OK
Book hotel     → OK
Book car       → FAILS
  ↓ compensate in reverse
Cancel hotel
Cancel flight

You trade atomicity for availability: there are visible intermediate states (someone can see a flight booked with no hotel), and compensations must be idempotent and retryable — you can’t “roll back,” only “do the opposite.” See Saga Pattern.

Interview application

Digital wallet / payments: “Balance updates use a single atomic UPDATE ... WHERE balance >= amount plus a CHECK (balance >= 0) constraint, so double-spend is impossible regardless of isolation level. The ledger is append-only — I never update a balance without writing the corresponding immutable ledger entry in the same transaction. Cross-user transfers stay in one transaction on one shard by sharding on a shared account grouping where possible; when they must cross shards, I use a saga with idempotent compensations keyed by transfer ID.”

Ticket/seat booking: “The last-seat problem is write skew, so counting available seats under snapshot isolation isn’t safe. I lock the specific seat row with SELECT ... FOR UPDATE — contention is naturally low because it’s per-seat — and hold a short reservation with a TTL rather than locking through the user’s payment flow.”

Inventory: “Decrement with a conditional atomic update so oversell is structurally impossible. For flash sales I’d pre-allocate inventory into Redis with an atomic DECR and reconcile to Postgres asynchronously, because the DB row becomes a contention hotspot at thousands of concurrent buyers.”

Common Interview Questions

Q: “What isolation level does your database use by default?” A: “Postgres and Oracle default to Read Committed; MySQL InnoDB defaults to Repeatable Read. Read Committed means each statement sees a fresh snapshot, so application-level read-modify-write can lose updates — I’d use an atomic update or explicit locking for anything money-related rather than relying on the default.”

Q: “What’s a phantom read, and why aren’t row locks enough?” A: “A phantom is a new row that matches your query’s predicate mid-transaction. Row locks can only lock rows that exist, so preventing phantoms needs range/gap locks (MySQL) or dependency tracking (Postgres SSI).”

Q: “What’s write skew?” A: “Two transactions read the same set, each decides it’s safe to act, then each writes a different row — no write-write conflict is detected, but the shared invariant breaks. It’s why ‘check then act’ on a count is unsafe under snapshot isolation, and it’s the mechanism behind double-spend and overselling. Fix: SERIALIZABLE with retries, lock the rows you read, or materialize a conflict row.”

Q: “Why not just use SERIALIZABLE everywhere?” A: “Under low contention, SSI costs little — so on a new system it’s a defensible default. But it adds abort-and-retry logic to every write path, and under high contention the abort rate can collapse throughput. I’d rather make the specific invariant unbreakable with an atomic conditional update, and keep the default at Read Committed.”

Q: “How do you handle a transaction that spans two services?” A: “I try hard not to have one — usually by moving the boundary so the invariant lives inside one service. If it must span, a saga with idempotent compensating actions and a durable orchestrator, not 2PC, because 2PC blocks holding locks when the coordinator fails.”

Q: “What is MVCC and what does it cost?” A: “Writers create new row versions so readers never block. The cost is version accumulation — Postgres needs VACUUM, long transactions cause bloat by holding the vacuum horizon, and letting vacuum fall behind risks XID wraparound.”

Q: “Is a deadlock a bug?” A: “Not necessarily — the DB detects it and aborts a victim, so the app must retry. But consistently deadlocking usually means transactions acquire locks in inconsistent order. Fix it by ordering lock acquisition (e.g. always lock the lower account ID first), keeping transactions short, and never holding a lock across a network call.”

Decision Framework