Database Isolation Levels Explained With Real Transaction Examples

Two customers try to buy the final concert ticket. Both requests read available = 1; both decide the sale is valid; both write a successful order. The database did exactly what each statement requested, yet the business has oversold a seat. Isolation levels decide which concurrent histories the database permits, but the level name alone is never the whole design.

This guide explains database isolation levels through transaction timelines and practical choices. Read what a database transaction is and ACID properties first, then use the Backend Developer Interview Guide for the wider path. The Database tag has the prerequisite SQL material.

Table of Contents

Isolation Protects a Business Invariant

A transaction groups related database changes so they commit together or roll back together. Isolation answers a different question: while transactions overlap, which changes can one transaction observe and which unsafe interleavings must the database reject or block?

Start with the invariant, not a chart of names. “A ticket is sold at most once,” “an account balance never goes negative,” and “at least one doctor remains on call” are rules the application must preserve. Then draw two transactions that could break the rule. This turns an interview answer into an engineering decision: identify the anomaly, choose a protection mechanism, and define what happens when contention occurs.

flowchart TD
    A[Business invariant] --> B[Two concurrent transactions]
    B --> C{Unsafe history possible?}
    C -->|No| D[Commit both]
    C -->|Yes| E{Cheapest safe control}
    E --> F[Atomic conditional update]
    E --> G[Row or predicate lock]
    E --> H[Optimistic version check]
    E --> I[Serializable transaction]
    F --> J[Verify invariant]
    G --> J
    H --> J
    I --> K{Serialization failure?}
    K -->|Yes| L[Retry whole transaction]
    K -->|No| J

    classDef flow fill:#e8f0fe,stroke:#1a73e8,stroke-width:2px,color:#000000;
    class A,B,C,D,E,F,G,H,I,J,K,L flow;

The Anomalies Worth Understanding

A dirty read observes a value another transaction has written but not committed. If that writer rolls back, the reader acted on a value that never became real. Read Committed or stronger prevents this in mainstream engines.

A non-repeatable read happens when one transaction reads a row twice and another committed transaction changes it between the reads. A phantom is the set-based version: a transaction repeats WHERE status = 'OPEN' and sees a newly inserted matching row. The exact guarantees vary by engine and implementation, so do not assume the ANSI names predict every database’s behavior.

Lost updates and write skew are usually more important in backend work. A lost update occurs when two read-modify-write operations overwrite each other. Write skew occurs when transactions read a shared condition but update different rows, so no direct write conflict appears. The doctor-on-call example is write skew: each doctor sees another available doctor, each removes a different row, and neither invariant survives.

The Four Standard Isolation Levels

The table is a study aid, not a deployment guide. PostgreSQL implements Read Uncommitted as Read Committed and uses multiversion concurrency control. MySQL InnoDB defaults to Repeatable Read. SQL Server can use locking or row-versioning variants. Always check the documentation and the configuration of the exact database you operate.

Real Transaction Example: The Last Available Seat

The safest simple design often avoids a read-then-write race entirely. Use one conditional statement and inspect the affected-row count:

UPDATE events
SET available_seats = available_seats - 1
WHERE event_id = $1
  AND available_seats > 0;

If this updates one row, create the reservation in the same transaction. If it updates zero rows, the event is sold out. The write itself establishes the condition, so two buyers cannot both decrement 1 to 0. This is usually clearer and cheaper than increasing isolation for every request.

When the rule spans several rows, the design is harder. Imagine a scheduling system where at least one doctor must remain on call. Two transactions can both read a count of two, then each mark a different doctor unavailable. A row lock on only the doctor being changed does not protect the shared predicate. Use Serializable isolation, a lock on a common guard row, or model the invariant as an atomic counter. The next article compares optimistic and pessimistic locking for exactly this decision.

MVCC, Locks, and Engine Differences

Modern databases often use MVCC: writers create versions, and readers see a consistent committed version without taking a shared lock on every row. That improves read concurrency, but it does not make conflicts disappear. A snapshot can still permit write skew unless the engine’s Serializable mode detects the dangerous dependency and aborts one transaction.

Locks remain useful when a transaction must reserve a known row before making a decision. In PostgreSQL, SELECT ... FOR UPDATE can lock selected rows until commit; in MySQL and SQL Server, the equivalent syntax and locking behavior differ. Keep these transactions short. A lock held while an application calls a payment provider or waits for user input turns a correctness mechanism into a throughput outage.

The database also cannot make an HTTP request and a row update one atomic operation. For payment, email, or Kafka publication, combine a local transaction with idempotency, an outbox, explicit state transitions, and reconciliation. Idempotency in REST APIs is the right companion when a client retry can repeat a side effect.

Choosing the Right Protection

Choose the smallest mechanism that protects the invariant:

1. Prefer an atomic conditional update for one-row counters and state transitions.
2. Use a uniqueness or check constraint when the rule belongs in the schema.
3. Use optimistic version checks when conflicts are uncommon and the caller can retry or show a conflict.
4. Use explicit row locks when a small, known set of rows must be reserved briefly.
5. Use Serializable isolation when the rule depends on a predicate or multiple rows and lower-level controls are insufficient.

This order is about clarity as much as throughput. “Set everything to Serializable” hides the invariant, creates avoidable aborts, and still fails if the application does not retry safely. Conversely, choosing Read Committed because it is a default is not a justification for an invariant that requires stronger protection.

Retries Are Part of Serializable Design

Serializable databases may abort a transaction to preserve the guarantee. That is correct behavior, not an outage. The application must retry the entire transaction from the beginning with a small bounded backoff, and it must make retries safe through an idempotency key or a deterministic operation identity.

Do not retry indefinitely. Record the failed attempt, cap the retry count, add jitter so conflicting requests do not collide again, and return a useful retryable error or enqueue work when the budget is exhausted. Monitor serialization failures, deadlocks, lock waits, and transaction duration; these signals reveal contention before customers experience it as random latency.

Interview Questions

1. What isolation level would you choose for a payment workflow?

I would first separate the local database invariant from the external payment call. A local order state transition may use an atomic conditional update or Serializable transaction if a multi-row invariant requires it; the payment-provider call needs idempotency and reconciliation because it is not part of the database transaction. The level follows the invariant, and the retry path follows the database’s conflict behavior.

2. Does Repeatable Read prevent every concurrency bug?

No. It prevents a transaction from seeing a row change on repeated reads in the standard model, but phantom and write-skew behavior depend on the engine’s concurrency implementation. A good answer names the exact database and then tests the interleaving that threatens the business rule. If the invariant depends on a predicate across rows, Serializable or a deliberate guard mechanism may still be necessary.

3. Why is Serializable not always the default choice?

It provides the strongest general guarantee, but it can increase blocking or cause more transaction aborts under contention. Those aborts require application-level retries, observability, and idempotent behavior. For a one-row inventory decrement, a conditional update is often easier to reason about and more efficient than broad Serializable transactions.

4. What is write skew?

Write skew occurs when two transactions read the same shared condition but update different rows, so neither sees a direct write-write conflict. The two-doctors-on-call example is classic: each transaction sees another doctor available, then each marks itself unavailable. Protect the predicate with Serializable isolation, a common guard row, or a schema design that turns the invariant into an atomic write.

5. How do you test isolation behavior?

Use two database sessions and deliberately interleave the reads, writes, commits, and rollbacks that model the production race. Test on the same engine and configuration you deploy, capture the expected result, and run the scenario repeatedly under load. A unit test that runs one transaction at a time cannot prove a concurrency invariant.

Conclusion

Isolation is not a vocabulary test. State the invariant, demonstrate the unsafe history, choose the narrowest control that prevents it, and design the retry behavior before production traffic supplies the race for you. Use the default level only when it actually protects the rule you care about.

Continue with Optimistic vs Pessimistic Locking to choose the next control mechanism deliberately.

References

1. Transaction Isolation - PostgreSQL Documentation https://www.postgresql.org/docs/18/transaction-iso.html
2. InnoDB Transaction Isolation Levels - MySQL Reference Manual https://dev.mysql.com/doc/refman/8.4/en/innodb-transaction-isolation-levels.html
3. Transaction Locking and Row Versioning Guide - Microsoft Learn https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide

YouTube Videos

1. “Everything about database transaction isolation” - Kodiruem https://www.youtube.com/watch?v=SMv5_-uVwRo