Transaction Isolation Levels: What Your Database Actually Does When Two Users Book the Last Seat

The consistency post drew a line: replication models are about what a reader on some replica can see; isolation levels are about what concurrent transactions on one copy can do to each other. Then it punted the second axis. This is the other half.

Isolation is where “two users book the last seat” actually gets answered. It is also where a surprising number of senior candidates discover that their production database has been running at a level that permits lost updates, and that the word “serializable” means different things in different products. By the end of this post you will know the anomalies, which level permits which, what your database really does under each name, and the specific tool that prevents each anomaly without paying for the most expensive level.

Where This Shows Up

• “Two users try to book the last seat at the same instant. Walk me through it.”
• “What isolation level would you use for the payments table?”
• “What is the difference between repeatable read and serializable?”
• “What is write skew?” (the senior filter question)
• “Why did the counter go up by one when two requests incremented it?”
• “Can you enforce uniqueness in application code?”

What They Are Really Checking

1. Do you know the anomalies by name? Dirty read, non-repeatable read, phantom, lost update, write skew. The interviewer wants you to name the thing that goes wrong before you name the level that prevents it.
2. Do you know what your database’s default permits? Most run at read committed. Read committed permits lost updates. A candidate who does not know this has probably shipped a race.
3. Do you know that the names lie? “Repeatable read” in one database is snapshot isolation in another. “Serializable” in one product is snapshot isolation in another. Knowing this is the mark of someone who has read the documentation instead of the standard.
4. Can you prevent an anomaly surgically? Cranking everything to serializable is the junior answer. An atomic update, a conditional write, a row lock, or a unique constraint is usually the right one.
5. Do you know the cost? Serializable costs throughput and forces retry logic. Say which implementation and what it does under contention.

The Gotchas

Gotcha 1: Assuming ACID means serializable. The I in ACID is configurable, and the default is almost never serializable. Saying “the database is ACID so this is safe” is the sentence that starts the follow-up you do not want.

Gotcha 2: Assuming read committed prevents lost updates. It does not. Two transactions read the same row, both compute a new value, both write. The second write wins and the first is gone. Read committed only guarantees you do not see uncommitted data.

Gotcha 3: Assuming “repeatable read” means the same thing everywhere. In the standard it prevents non-repeatable reads and permits phantoms. In PostgreSQL it is snapshot isolation, which prevents phantoms too. In MySQL InnoDB it is snapshot reads plus gap locks on locking reads, which is a third thing. Name the product when you name the level.

Gotcha 4: Not knowing write skew. Snapshot isolation prevents every anomaly in the standard’s list and still permits write skew, which is why a database can call it “serializable” and be wrong. If you cannot explain write skew with an example, you cannot explain why serializable exists.

Gotcha 5: Read-modify-write in application code. Select a row, compute in the app, update the row. No lock, no condition, no version. This is the most common concurrency bug in production code, and it looks fine in every code review.

Gotcha 6: Turning on serializable and not writing retries. Optimistic serializable implementations abort transactions that would violate serializability. If the application does not catch the serialization failure and retry, users see errors under load and the team turns the level back down.

Gotcha 7: Checking uniqueness in code. “Select to see if the username exists, then insert.” Two requests, both see nothing, both insert. A unique constraint is the only correct answer. Say it before the interviewer asks.

Gotcha 8: Long transactions. Holding a transaction open across user think time or a network call to another service holds locks or a snapshot for seconds. In lock-based systems that blocks everyone; in multi-version systems it bloats storage and delays cleanup. Transactions should be short and never span an external call.

Gotcha 9: Assuming isolation spans services. A transaction is a property of one database. Two services with two databases have no isolation between them. That is what sagas and the outbox pattern are for, and it is a different post.

Gotcha 10: Conflating the two axes. “The user refreshed and their comment vanished” is replication. “Two increments produced one” is isolation. Say which axis the question is on. The consistency post covers the other one.

How to Answer

Step 1: Name the anomalies

Anomaly	What happens	Example
Dirty read	T2 reads a value T1 wrote but has not committed	Reporting a balance that is rolled back a moment later
Dirty write	T2 overwrites a value T1 wrote but has not committed	Two transactions each updating two rows; the rows end up from different transactions
Non-repeatable read	T1 reads a row, T2 commits a change, T1 reads the row again and sees a different value	A report that sums a column twice and gets two answers
Phantom	T1 runs a predicate query, T2 inserts a matching row, T1 runs the query again and sees a new row	“Count the bookings for this event” changing mid-transaction
Lost update	T1 and T2 both read, both compute, both write; one write is silently lost	Two increments, counter goes up by one
Read skew	T1 reads row A, T2 commits changes to A and B, T1 reads B; A and B are from different points in time	Transferring between accounts and a concurrent read sees the money in neither
Write skew	T1 and T2 read overlapping data, make a decision, and write to different rows; each write is fine alone, together they break an invariant	Two on-call doctors both check “at least one other doctor is on call,” both go off call

The bold rows are the ones interviewers actually ask about, because they survive the default level.

Step 2: The levels, and what each permits

Level	Dirty read	Non-repeatable read	Phantom	Lost update	Write skew	How it typically works
Read uncommitted	permitted	permitted	permitted	permitted	permitted	No read locks; rarely used; PostgreSQL silently upgrades it
Read committed	prevented	permitted	permitted	permitted	permitted	Each statement sees data committed before that statement started. The default almost everywhere
Repeatable read (standard)	prevented	prevented	permitted	varies	permitted	Row-level read stability; phantoms slip through predicate reads
Snapshot isolation	prevented	prevented	prevented	prevented (first committer wins on the same row)	permitted	The transaction reads from a consistent snapshot taken at its start. Not in the ANSI standard, but what most “repeatable read” implementations actually are
Serializable	prevented	prevented	prevented	prevented	prevented	Result equals some serial ordering. Implemented by locking, by optimistic detection, or by literally running serially

Two things to say out loud. First, the standard’s ladder was defined in terms of locking and does not describe modern multi-version databases well; the 1995 critique that introduced snapshot isolation and write skew is still the reference. Second, snapshot isolation is the level that matters in practice, and its one hole is write skew.

Step 3: What your database actually does

This is the table that separates candidates who have read documentation from candidates who have read the standard.

Database	Default	“Repeatable read” is	“Serializable” is	Notes
PostgreSQL	Read committed	Snapshot isolation. Also detects concurrent updates to the same row and aborts the second, so lost updates are prevented	True serializability via SSI (Serializable Snapshot Isolation), optimistic, aborts on dangerous patterns	You must retry on serialization failure
MySQL InnoDB	Repeatable read	Snapshot reads for plain SELECT, plus next-key (gap) locks on locking reads and writes, which blocks most phantoms	Every plain SELECT becomes a shared-lock read; effectively two-phase locking	Lost updates are possible with plain SELECT then UPDATE; use FOR UPDATE or atomic updates
Oracle	Read committed	Not offered by that name	Snapshot isolation, despite the name. Write skew is possible	The classic example of the name lying
SQL Server	Read committed (locking by default; a database option switches it to versioned reads)	Locking-based	Locking-based two-phase locking with range locks	Also offers a separate SNAPSHOT level, which is snapshot isolation
SQLite	Serializable	n/a	Single writer at a time	Trivially serializable because there is no write concurrency
CockroachDB	Serializable	n/a	True serializability, optimistic with retries	Read committed added later as an opt-in for migration compatibility
Spanner	Strict serializability	n/a	Serializable plus linearizable, via synchronized clocks	The rare system where the two axes are both at the top

The sentence to have ready: “Most relational databases default to read committed, which permits lost updates, so any read-modify-write in my design needs an explicit mechanism.”

Step 4: Write skew, because it is the question

Snapshot isolation prevents everything except this. Here is the canonical example.

Invariant: at least one doctor must be on call. Two doctors, both on call, both ask to go off call at the same moment.

sequenceDiagram
    participant A as Transaction A (Alice)
    participant DB as Database (snapshot isolation)
    participant B as Transaction B (Bob)
    A->>DB: SELECT count(*) WHERE on_call = true
    DB-->>A: 2
    B->>DB: SELECT count(*) WHERE on_call = true
    DB-->>B: 2
    Note over A: 2 ≥ 2, safe to leave
    Note over B: 2 ≥ 2, safe to leave
    A->>DB: UPDATE doctors SET on_call=false WHERE name='Alice'
    B->>DB: UPDATE doctors SET on_call=false WHERE name='Bob'
    A->>DB: COMMIT
    B->>DB: COMMIT
    Note over DB: Both commit. Nobody is on call.<br/>No row was written by both, so first-committer-wins never fired.

Each transaction read a consistent snapshot. Each wrote a different row. Snapshot isolation has no reason to object, and the invariant is broken. The same shape appears in: booking a meeting room (both check for overlap, both insert), claiming a username (both check, both insert), spending from a shared budget (both check the total, both add a charge), and the ticketing case when it is written as “check the seat is free, then insert a booking.”

Three fixes, in order of preference:

1. Make the conflict visible with a constraint. A unique index on (room, time_slot) or (event_id, seat_id) turns the second insert into an error. This is the cheapest and most robust fix, and it works at read committed.
2. Lock the rows you read. SELECT ... FOR UPDATE on the rows the decision depends on. Both transactions now contend on the same rows and the second waits, then re-reads and sees the change. Works for the doctors example where there is no constraint to express the invariant.
3. Use serializable. The database detects the dangerous pattern and aborts one transaction. Correct by construction, but you must retry, and throughput drops under contention.

Step 5: The toolbox

Match the anomaly to the cheapest mechanism that prevents it. Cranking the level is the last resort, not the first.

Problem	Mechanism	Works at	Cost
Increment or decrement	Atomic update in one statement: UPDATE t SET n = n - 1 WHERE id = ? AND n > 0	Read committed	None. This is the answer for inventory counts and balances
Read-modify-write on one row	Compare-and-set with a version column: UPDATE ... WHERE id = ? AND version = ?, retry if zero rows	Read committed	A retry loop in the application
Read-modify-write, pessimistic	SELECT ... FOR UPDATE then update in the same transaction	Read committed	Lock held for the transaction’s duration; deadlock potential with multiple rows
Uniqueness	Unique constraint or index	Any level	None. Never check in code
Invariant across rows (write skew)	Materialize the conflict: a constraint, a lock on a shared row, or FOR UPDATE on the read set	Read committed	Some contention on the shared row
Invariant you cannot express as a constraint or a lock	Serializable	Serializable	Retries, and reduced throughput under contention
Whole-table reports that must be consistent	Snapshot isolation, or a read-only serializable deferrable transaction	Repeatable read or above	Longer snapshots delay cleanup

For the seat booking: a bookings table with a unique constraint on (event_id, seat_id), and a conditional insert or update. Two buyers, one succeeds, one gets a conflict, and it works at read committed with no locks held across the payment call. That is the answer, and it is better than “use serializable.”

Step 6: The cost of serializable

If you do choose serializable, say which kind and what it costs.

Implementation	How	Under contention	Retry needed
Two-phase locking (2PL)	Readers block writers, writers block readers; range locks for phantoms	Throughput collapses; deadlocks are detected and one victim is aborted	Yes, on deadlock
Serializable snapshot isolation (SSI)	Snapshot reads plus tracking of read-write dependencies; abort when a cycle is possible	Readers do not block writers; abort rate rises with conflicting workloads	Yes, on serialization failure. Always
Actual serial execution	One transaction at a time per partition, in memory	Fine as long as every transaction is tiny and partition-local	No, but cross-partition transactions are slow or unsupported

The rule for retries: wrap the transaction in a loop, retry on the serialization error code with backoff, cap the attempts, and make sure the transaction body has no side effects outside the database, because it may run more than once. If it must call an external service, that call moves outside the transaction and gets an idempotency key, per the idempotency post.

Step 7: The sentence to say in an interview

For the seat booking:

The database default is read committed, which is fine here because I will not rely on isolation for the invariant. The bookings table has a unique constraint on event and seat, and the hold is a conditional update that only succeeds if the seat is still available. Two buyers race, one wins the row, the other gets a conflict and sees a clear message. No locks are held across the payment call, and the payment carries an idempotency key. I would only reach for serializable if there were an invariant I could not express as a constraint or a single-row condition.

For a money transfer:

The debit is a conditional update that fails if the balance would go negative, and both account rows are updated in one transaction so a concurrent reader never sees the money in neither place. I would run that at snapshot isolation so the read side sees a consistent pair, and I do not need serializable because the invariant is per-row.

Both answers name the level, the anomaly, the mechanism, and why the expensive option is unnecessary.

Follow-Up Questions to Expect

• “What is the difference between repeatable read and serializable?” Snapshot isolation, which is what repeatable read usually means, permits write skew. Serializable does not. Give the doctors example.
• “Why not just run everything serializable?” Retries in every code path, throughput loss under contention, and most invariants are cheaper to enforce with a constraint or a conditional write.
• “How would you enforce ‘at most three active sessions per user’?” A lock on the user row (FOR UPDATE) before counting and inserting, or serializable. A unique constraint cannot express “at most three.” Interviewers like this one because the constraint trick does not work.
• “Can you get lost updates at repeatable read?” In PostgreSQL, no, it detects them and aborts. In MySQL, yes with plain SELECT then UPDATE. The honest answer is “depends on the product,” which is the point.
• “How does this work across two services?” It does not. No isolation spans databases. That is sagas, compensation, and the outbox.
• “What about NoSQL?” Single-document operations are usually atomic. Multi-document transactions, where offered, are typically snapshot isolation. The same toolbox applies: conditional writes and unique indexes do most of the work.

Key Takeaways

• Isolation is the single-copy, multi-object axis. Replication consistency is the other one. Say which you are on.
• Name the anomaly first: dirty read, non-repeatable read, phantom, lost update, write skew.
• The default is read committed almost everywhere, and it permits lost updates.
• “Repeatable read” is usually snapshot isolation. “Serializable” is sometimes snapshot isolation. Name the product.
• Snapshot isolation prevents everything except write skew. Explain write skew with the doctors.
• Prevent anomalies surgically: atomic updates, conditional writes, FOR UPDATE, unique constraints. Serializable is the last resort.
• If you use serializable, write the retry loop and keep side effects out of the transaction.
• Transactions are short and never span an external call.

Further Reading

• Berenson et al., A Critique of ANSI SQL Isolation Levels (1995; introduced snapshot isolation and write skew)
• Martin Kleppmann, Designing Data-Intensive Applications, chapter 7
• PostgreSQL documentation, Transaction Isolation
• Ports and Grittner, Serializable Snapshot Isolation in PostgreSQL (VLDB 2012)
• Jepsen, Consistency Models (the isolation levels sit on the same map)