Photo by Towfiqu barbhuiya on Unsplash
Transaction Isolation Levels: Correctness vs Performance
Every database transaction makes an implicit tradeoff between correctness guarantees and system throughput. Transaction isolation levels formalize this tradeoff by defining exactly which anomalies a database prevents and which it allows in exchange for better performance.
The SQL standard defines four isolation levels: Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Each successive level provides stronger guarantees but imposes higher coordination costs. Understanding what each level actually prevents—and what it silently permits—matters when choosing between them.
What Isolation Levels Actually Prevent
Read Uncommitted allows transactions to see changes from other transactions that haven’t committed yet. This creates dirty reads where you might see data that gets rolled back moments later. Almost no production system uses this level because the correctness violations are too severe.
Read Committed prevents dirty reads by ensuring you only see committed data. However, it allows non-repeatable reads: if you query the same row twice in a transaction, another transaction might modify it between your reads, giving you different values. It also permits phantom reads where a query returns different result sets when executed multiple times because other transactions inserted or deleted matching rows.
Repeatable Read goes further by guaranteeing that if you read a row once, subsequent reads in the same transaction will see the same values. Most databases implement this using multi-version concurrency control (MVCC), where each transaction sees a consistent snapshot of the data as it existed when the transaction started. However, phantoms can still appear in many implementations.
Serializable provides the strongest guarantee: transactions behave as if they executed one at a time, with no concurrent interference. No anomalies occur. The challenge is implementing this without actually serializing all transactions and destroying throughput.
The Performance Cost of Stronger Isolation
Moving up the isolation hierarchy introduces coordination overhead. Read Committed typically requires only short-lived locks on individual rows during reads and writes. Repeatable Read requires maintaining multiple versions of data and tracking which version each transaction should see. Serializable requires detecting conflicts that could violate serial execution order.
The performance gap between isolation levels varies dramatically by workload. For read-heavy workloads with minimal contention, MVCC-based Repeatable Read performs nearly as well as Read Committed. Both readers and writers proceed without blocking each other. For write-heavy workloads with high contention, Serializable can reduce throughput by 10x or more as transactions abort and retry due to detected conflicts.
Database engines use different techniques to implement Serializable. Two-phase locking acquires locks in a way that prevents conflicts but can cause significant blocking. Serializable Snapshot Isolation (SSI) tracks read and write dependencies to detect cycles that would violate serializability, aborting one transaction in the cycle. SSI offers better performance than locking for many workloads but introduces retry costs when conflicts occur.
Choosing an Isolation Level
Most applications default to Read Committed because it offers reasonable consistency with good performance. You can see committed changes from other transactions immediately, and you avoid the most egregious anomalies. This works well when transactions are short and when you design application logic to handle races explicitly.
Repeatable Read makes sense when you need consistent reads within a transaction. Financial calculations that aggregate multiple rows, reporting queries that must present a coherent snapshot, and any logic that makes decisions based on multiple reads benefit from the stronger guarantee. The MVCC overhead is typically modest.
Serializable is necessary when application logic cannot correctly handle concurrent anomalies. Inventory management, seat reservations, and any constraint that spans multiple rows often requires Serializable to avoid subtle race conditions. The performance cost forces you to keep transactions short and minimize contention through careful schema design and query patterns.
What the Standard Doesn’t Tell You
Database implementations vary significantly in what anomalies they actually prevent at each level. PostgreSQL’s Repeatable Read prevents phantom reads even though the standard allows them. Oracle’s Read Committed uses multi-version snapshots that eliminate some non-repeatable reads. MySQL’s Repeatable Read doesn’t provide true serializability despite preventing many anomalies.
The practical approach is to understand both what the standard says and what your specific database implements. Test your application logic against the anomalies that matter for your use case. Use stronger isolation when correctness requires it and when you’ve confirmed the performance cost is acceptable. Default to weaker isolation only after understanding exactly which anomalies you’re accepting and why your application can tolerate them.
Transaction isolation isn’t about choosing the “best” level. It’s about making an explicit engineering decision about which anomalies your application handles correctly and which require database-level prevention. The performance difference is the price you pay for that correctness guarantee.