When Serializable Is Not Serializable: The Hidden World of Transaction Isolation Levels

In 2012, a team of database researchers published a paper that would reshape how engineers think about transaction isolation. The paper, titled “Serializable Snapshot Isolation in PostgreSQL,” described a subtle anomaly that had been hiding in plain sight for decades: two transactions could both execute correctly in isolation, yet produce an incorrect result when run concurrently. The anomaly wasn’t a dirty read or a phantom—it was something called write skew, and it exposed a fundamental truth about the ANSI SQL isolation levels: the names don’t always mean what developers think they mean.

The budget problem illustrated this perfectly. An IT department has a monthly budget of $100,000. Alice, the CEO, wants to give a 10% bonus to all employees in the department. She reads the current total salary ($90,000) and calculates that a 10% bonus would bring the total to $99,000—within budget. Meanwhile, Bob, the HR manager, is hiring Carol and sets her salary at $9,000, also calculating that the total will be $99,000. Both transactions commit successfully. The final total: $108,000. The budget is exceeded by $8,000, yet neither transaction did anything wrong in isolation.

This is the paradox of transaction isolation levels: the safeguards built into databases can give developers a false sense of security. Understanding why requires peeling back the layers of how databases actually implement isolation—and why the same isolation level name can mean different things in different databases.

The Four Levels That Never Were

ANSI SQL-92 defined four isolation levels based on three phenomena: dirty reads, non-repeatable reads, and phantom reads. The hierarchy seemed clean. Read Uncommitted allows all three phenomena. Read Committed prevents dirty reads. Repeatable Read prevents dirty reads and non-repeatable reads. Serializable prevents all three.

But in 1995, researchers from Microsoft published “A Critique of ANSI SQL Isolation Levels,” exposing fundamental flaws in these definitions. The paper showed that the ANSI definitions were ambiguous and failed to characterize several popular isolation levels. More importantly, they identified additional anomalies that the standard didn’t account for: dirty writes, lost updates, read skew, and write skew.

The mathematical reality is more complex. Isolation levels exist on a spectrum of trade-offs between consistency and performance. Each level prevents certain anomalies while potentially allowing others. The choice isn’t between “safe” and “unsafe”—it’s about which anomalies are acceptable for a given application.

graph TD
    A[Read Uncommitted] -->|Prevents| B[Nothing]
    C[Read Committed] -->|Prevents| D[Dirty Reads]
    E[Repeatable Read] -->|Prevents| F[Dirty Reads, Non-repeatable Reads]
    G[Serializable] -->|Prevents| H[All Three ANSI Phenomena]
    
    B --> I[Allows: Dirty Reads, Non-repeatable Reads, Phantom Reads]
    D --> J[Allows: Non-repeatable Reads, Phantom Reads]
    F --> K[Allows: Phantom Reads]
    H --> L[Note: Still allows Write Skew in some implementations]
    
    style A fill:#ff6b6b
    style C fill:#ffd93d
    style E fill:#6bcf7f
    style G fill:#4dabf7

How Databases Actually Implement Isolation

Two primary mechanisms dominate isolation implementation: Two-Phase Locking (2PL) and Multi-Version Concurrency Control (MVCC). Understanding the difference is crucial because the same isolation level name can behave differently depending on which mechanism is used.

Two-Phase Locking: The Pessimistic Approach

2PL uses locks to prevent conflicts before they occur. Transactions acquire shared (read) locks and exclusive (write) locks. A shared lock allows multiple readers but blocks writers. An exclusive lock blocks both readers and writers. The “two-phase” refers to a growing phase where locks are acquired and a shrinking phase where locks are released.

MySQL’s InnoDB engine uses 2PL for its Serializable isolation level. When Alice reads the sum of salaries, InnoDB acquires a shared predicate lock on the range of rows satisfying the query. When Bob attempts to insert Carol’s record, his transaction blocks because it cannot acquire an exclusive lock on a range that overlaps with Alice’s shared lock. Bob’s transaction eventually times out, and the budget constraint is preserved.

The downside is contention. Locks create bottlenecks, especially in read-heavy workloads. The relationship between contention and scalability follows Neil Gunther’s Universal Scalability Law: as contention increases, throughput eventually decreases.

MVCC: The Optimistic Approach

MVCC takes a different approach: readers don’t block writers, and writers don’t block readers. Instead of locking rows, MVCC maintains multiple versions of each row, each tagged with transaction IDs. A transaction sees only the versions that were committed before it started.

PostgreSQL, Oracle, and MySQL’s default isolation levels all use MVCC. When a transaction begins, it gets a transaction ID (XID). Every row stores an xmin (the XID that created it) and an xmax (the XID that deleted it, if applicable). A row is visible to a transaction if xmin is committed and less than the transaction’s XID, and xmax is either null or greater than the transaction’s XID.

sequenceDiagram
    participant T1 as Transaction A
    participant DB as Database
    participant T2 as Transaction B
    
    Note over T1,T2: MVCC Timeline
    T1->>DB: BEGIN (XID: 100)
    T2->>DB: BEGIN (XID: 101)
    T1->>DB: INSERT row (xmin=100)
    Note right of DB: Row not visible to T2
    T2->>DB: SELECT * FROM table
    DB-->>T2: Returns empty (snapshot at XID 101)
    T1->>DB: COMMIT
    Note right of DB: Row now committed
    T2->>DB: SELECT * FROM table
    DB-->>T2: Still empty (T2's snapshot unchanged)
    T2->>DB: COMMIT

This approach provides excellent read performance. Queries never block on locks. But it introduces subtlety: what happens when two transactions try to modify the same data concurrently? The answer depends on the isolation level and the specific MVCC implementation.

Write Skew: The Anomaly Hiding in Plain Sight

Write skew occurs when two transactions read the same data, make decisions based on that data, and then modify different parts of the data in ways that violate a constraint. Neither transaction sees the other’s changes, so both commit successfully—but the resulting state violates application invariants.

The budget scenario is a classic example. Both Alice and Bob read the current salaries, make decisions based on what they see, and commit. The constraint (total salaries ≤ $100,000) is violated, but neither transaction directly modified the same row, so neither MVCC implementation detected a conflict.

Not all databases handle this the same way. PostgreSQL 9.1 introduced Serializable Snapshot Isolation (SSI), which detects write skew by tracking read/write dependencies between transactions. When Alice’s transaction commits, PostgreSQL detects that it read data that Bob’s transaction later modified. This creates a dependency cycle, indicating that the transactions were not serializable. PostgreSQL aborts Alice’s transaction with the error: “could not serialize access due to read/write dependencies among transactions.”

sequenceDiagram
    participant Alice as Alice's Transaction
    participant DB as Database
    participant Bob as Bob's Transaction
    
    Note over Alice,Bob: Write Skew Scenario
    Alice->>DB: READ total_salary = $90,000
    Bob->>DB: READ total_salary = $90,000
    Alice->>DB: Calculate: 10% bonus = $9,000
    Bob->>DB: Insert Carol: salary = $9,000
    Alice->>DB: UPDATE salaries (+10%)
    Bob->>DB: COMMIT (total now $99,000)
    Alice->>DB: COMMIT (total now $108,000)
    Note right of DB: Budget exceeded!<br/>Neither transaction detected conflict

MySQL, using 2PL for Serializable, prevents the write skew by blocking Bob’s insert with a shared predicate lock. But this comes at the cost of reduced concurrency.

The Default Isolation Level Dilemma

One of the most surprising aspects of database isolation is that almost no production database defaults to Serializable—the highest isolation level. PostgreSQL defaults to Read Committed. MySQL defaults to Repeatable Read. SQL Server defaults to Read Committed. Oracle defaults to Read Committed. CockroachDB, a distributed SQL database, defaults to Serializable, making it the exception.

The reason isn’t laziness. Serializable isolation imposes significant performance overhead. In 2PL implementations, it requires predicate locks that reduce concurrency. In MVCC implementations, it requires tracking read/write dependencies and potentially aborting transactions.

Research from the 1970s established that strict serializability—serializability plus linearizability—comes with inherent trade-offs. The CAP theorem formalized this for distributed systems: you can’t have consistency, availability, and partition tolerance simultaneously. The same principle applies to isolation levels: higher isolation means lower throughput and higher latency.

MVCC Under the Hood: Visibility and Versioning

To understand why write skew occurs, it’s necessary to understand how MVCC handles visibility. The key insight is that each transaction operates on a snapshot of the database as it existed when the transaction began (for Repeatable Read) or when the statement began (for Read Committed).

The trade-off becomes apparent when considering updates. In MVCC, an update is actually a delete plus an insert. The old row is marked deleted (by setting xmax), and a new version is created. This creates “dead rows”—versions that are no longer visible to any active transaction. PostgreSQL’s VACUUM process cleans these up, but the overhead is real. A table with heavy update activity can accumulate millions of dead rows, consuming disk space and degrading query performance.

graph LR
    A[Original Row] --> B[Update Transaction]
    B --> C[Old Version<br/>xmax set]
    B --> D[New Version<br/>xmin set]
    C --> E[VACUUM Process]
    E --> F[Dead Row Removed]
    
    style C fill:#ff6b6b,stroke-dasharray: 5 5
    style D fill:#4dabf7
    style F fill:#90EE90

When Isolation Levels Collide with Reality

The theoretical definitions of isolation levels often collide with practical implementation details. PostgreSQL’s Repeatable Read, for example, is actually implemented as Snapshot Isolation—a stronger guarantee than the SQL standard requires. PostgreSQL’s Serializable is implemented as Serializable Snapshot Isolation (SSI), which provides true serializability but with different performance characteristics than 2PL-based implementations.

MySQL’s Repeatable Read prevents phantom reads using next-key locks, which combine record locks with gap locks. This is stricter than the SQL standard, which allows phantoms at Repeatable Read level. But it also means that certain insert patterns can create unexpected lock contention.

Oracle’s “Serializable” isolation level is actually Snapshot Isolation, which doesn’t prevent write skew. An application running on Oracle that assumes true serializability might encounter subtle bugs when migrated to a database with different semantics.

The lesson is clear: isolation level names are marketing terms. The actual behavior depends on the database engine and its implementation. Developers must test assumptions, not rely on labels.

Choosing the Right Level: A Pragmatic Framework

Selecting an isolation level requires understanding the application’s consistency requirements and the database’s implementation. Here’s a decision framework:

Read Committed works well for applications where seeing committed data is sufficient but exact consistency isn’t critical. Most web applications fall into this category. A user viewing their profile doesn’t need to see a perfectly consistent snapshot—the cost of seeing slightly stale data is usually acceptable.

Repeatable Read (or Snapshot Isolation) provides a consistent view for the duration of a transaction. This is essential for operations that span multiple queries where intermediate consistency matters. Generating a report that joins several tables benefits from a stable snapshot.

Serializable is necessary when correctness is paramount and anomalies like write skew are unacceptable. Financial systems, inventory management, and any application with complex invariants often require this level. But it comes with a cost: reduced throughput and potential transaction aborts that require retry logic.

The general rule: use the lowest isolation level that satisfies the application’s consistency requirements. Starting with Serializable and downgrading is usually wrong. Start with Read Committed, identify consistency issues, and escalate isolation only where necessary.

The Performance Cost of Correctness

Performance differences between isolation levels can be dramatic. A benchmark using the TPC-C workload shows that Serializable can achieve 60-80% of the throughput of Read Committed, depending on contention levels. Under high contention, the gap widens—transactions abort more frequently, consuming CPU and I/O without producing useful work.

The performance impact isn’t uniform. Read-heavy workloads are less affected because MVCC allows readers to proceed without blocking. Write-heavy workloads suffer more because every write creates new versions and potential conflicts.

Lock-based implementations have different characteristics. MySQL’s 2PL-based Serializable creates more blocking but fewer aborts. PostgreSQL’s SSI-based Serializable creates fewer blocks but more aborts. The choice between them depends on the application’s tolerance for latency (blocking) versus retry overhead (aborts).

graph TD
    A[Read Committed] --> B[High Throughput<br/>Low Latency<br/>Weak Consistency]
    C[Repeatable Read] --> D[Medium Throughput<br/>Medium Latency<br/>Medium Consistency]
    E[Serializable] --> F[Lower Throughput<br/>Higher Latency<br/>Strong Consistency]
    
    B --> G[Risk: Non-repeatable Reads,<br/>Phantom Reads, Write Skew]
    D --> H[Risk: Phantom Reads,<br/>Write Skew in some implementations]
    F --> I[Risk: Transaction Aborts,<br/>Deadlocks, Reduced Concurrency]
    
    style B fill:#90EE90
    style D fill:#FFD700
    style F fill:#FFA07A

What Developers Get Wrong

Common misconceptions about isolation levels lead to subtle bugs:

Myth: Serializable prevents all concurrency issues. False. Serializable guarantees that concurrent transactions produce a result equivalent to some serial execution. It doesn’t prevent logical errors in the transaction itself, nor does it guarantee real-time ordering (linearizability).

Myth: Higher isolation levels are always safer. False. Higher isolation levels reduce certain anomalies but introduce others: deadlocks become more likely, throughput decreases, and retry logic becomes necessary.

Myth: The default isolation level is good enough. Often false. Defaults are chosen for broad compatibility, not correctness. An application with specific consistency requirements might need a higher level.

Myth: ORMs handle isolation automatically. False. ORM frameworks like Hibernate and Entity Framework provide isolation configuration, but they don’t automatically choose the right level. Developers must still understand the trade-offs.

Monitoring and Detecting Isolation Issues

Detecting isolation anomalies in production requires observability. Key metrics include:

• Transaction abort rate: A high abort rate (especially serialization failures) indicates contention or overly strict isolation.
• Lock wait time: High lock wait times suggest 2PL-based isolation with contention.
• Deadlock frequency: Deadlocks increase with stricter isolation levels and higher concurrency.
• Long-running transactions: These can cause cascading effects under MVCC, preventing cleanup of dead rows.

PostgreSQL’s pg_stat_activity view shows blocked sessions and wait events. MySQL’s INFORMATION_SCHEMA.INNODB_LOCKS and INNODB_LOCK_WAITS tables reveal lock contention. SQL Server’s Extended Events can capture deadlock graphs for analysis.

The Future of Transaction Isolation

Research continues to advance isolation implementations. Recent papers have explored adaptive isolation levels that dynamically adjust based on workload characteristics. TxnSails, a middleware solution proposed in 2025, aims to achieve serializable scheduling with self-adaptive isolation level selection, reducing the burden on developers to choose the right level.

Distributed databases face additional challenges. CockroachDB provides serializable isolation by default, but achieving this across geographic regions introduces latency overhead. Google Spanner uses TrueTime—a synchronized clock mechanism—to provide external consistency, a stronger guarantee than serializability.

The industry is slowly converging on the realization that isolation levels are not just database configuration knobs but fundamental design decisions that affect application correctness. The 2018 Jepsen series of database analyses exposed isolation bugs in numerous production databases, showing that even mature systems get isolation wrong.

The Bottom Line

Transaction isolation levels represent one of the most consequential yet misunderstood aspects of database systems. The names—Read Committed, Repeatable Read, Serializable—are starting points, not complete specifications. Understanding the actual behavior requires diving into implementation details: MVCC versus 2PL, predicate locks versus version chains, detection versus prevention.

The budget scenario that opened this discussion isn’t hypothetical. Similar bugs have appeared in production systems across industries: double-spending in financial applications, overselling in e-commerce platforms, and inconsistent state in distributed systems. The root cause is often the same: developers assumed an isolation level provided guarantees it didn’t.

The solution isn’t to default to Serializable and accept the performance cost. It’s to understand the trade-offs, test assumptions, and choose isolation levels deliberately. The database isolation paper from 1995 wasn’t just academic—it was a warning. Twenty-five years later, that warning remains relevant.

References

1. Berenson, H., Bernstein, P., Gray, J., et al. (1995). “A Critique of ANSI SQL Isolation Levels.” Microsoft Research Technical Report TR-95-51.

2. Ports, D.R.K. & Grittner, K. (2012). “Serializable Snapshot Isolation in PostgreSQL.” Proceedings of the VLDB Endowment, 5(12), 1850-1861.

3. PostgreSQL Documentation. “Transaction Isolation.” Retrieved from https://www.postgresql.org/docs/current/transaction-iso.html

4. MySQL 8.4 Reference Manual. “InnoDB Transaction Model and Locking.” Retrieved from https://dev.mysql.com/doc/refman/8.4/en/innodb-transaction-model.html

5. Mihalcea, V. (2017). “A beginner’s guide to the Write Skew anomaly, and how it differs between 2PL and MVCC.” Retrieved from https://vladmihalcea.com/write-skew-2pl-mvcc/

6. Bailis, P. (2014). “Linearizability versus Serializability.” Retrieved from http://www.bailis.org/blog/linearizability-versus-serializability/

7. Cockroach Labs. “Everything you always wanted to know about SQL isolation levels.” Retrieved from https://www.cockroachlabs.com/blog/sql-isolation-levels-explained/

8. Fekete, A., Liarokapis, D., O’Neil, E., et al. (2005). “Making Snapshot Isolation Serializable.” ACM Transactions on Database Systems, 30(2), 492-528.

9. Gray, J. & Reuter, A. (1993). Transaction Processing: Concepts and Techniques. Morgan Kaufmann Publishers.

10. Kleppmann, M. (2017). Designing Data-Intensive Applications. O’Reilly Media.

11. TiDB Documentation. “Transaction Isolation Levels.” Retrieved from https://docs.pingcap.com/tidb/stable/transaction-isolation-levels

12. CockroachDB Documentation. “Transactions.” Retrieved from https://www.cockroachlabs.com/docs/stable/transactions.html

13. Google Cloud Spanner Documentation. “TrueTime and Transaction Semantics.” Retrieved from https://cloud.google.com/spanner/docs/true-time-external-consistency

14. Red Gate. “Understanding Database Transactions: From Dirty Reads to Snapshot Isolation.” Retrieved from https://www.red-gate.com/simple-talk/databases/postgresql/database-concurrency-in-postgresql/