What Are Transaction Isolation Levels?

Transaction isolation controls how the effects of one transaction are visible to other concurrent transactions. Think of it as deciding whether multiple people editing the same spreadsheet see each other's changes immediately, only after they hit save, or not at all until everyone is done. The American National Standards Institute (ANSI) SQL standard defines four core levels that trade consistency for concurrency.

Read Uncommitted allows dirty reads (seeing uncommitted changes). Read Committed prevents dirty reads but allows the same row to return different values on repeated reads. Repeatable Read ensures repeated reads of the same row return consistent values but allows phantom reads (new rows appearing in query results). Serializable prevents all anomalies by making concurrent transactions behave as if they ran one after another.

In production, most systems use Multi-Version Concurrency Control (MVCC) variants rather than pure ANSI models. Microsoft SQL Server defaults to Read Committed with locking but offers Read Committed Snapshot Isolation (RCSI) via versioning. Oracle uses Read Committed with statement level snapshots by default. PostgreSQL defaults to Read Committed but supports Serializable Snapshot Isolation (SSI). These implementations change the performance characteristics dramatically: Google Spanner provides strict serializability with typical commit waits under 7 milliseconds in single region deployments, while cross continent transactions pay 80 to 150 milliseconds plus for network round trips.

Choosing an isolation level is a system design decision. Stronger isolation prevents data corruption but reduces throughput under contention. At 10,000 to 100,000 transactions per second, enabling snapshot isolation in SQL Server typically eliminates read/write blocking but increases version store growth and storage input/output pressure. The key is matching isolation strength to your correctness requirements: use Serializable for money transfers and inventory allocation, Read Committed or snapshots for read heavy analytics.

💡 Key Takeaways

•Read Uncommitted allows dirty reads (seeing uncommitted data from other transactions), rarely used in production due to corruption risk

•Read Committed is the most common default (SQL Server, Oracle, PostgreSQL), prevents dirty reads but allows non-repeatable reads and phantoms

•Repeatable Read ensures the same query returns consistent row values but new rows can still appear (phantoms), MySQL/InnoDB default with gap locking

•Serializable prevents all anomalies by making transactions appear to run sequentially, but reduces throughput significantly under contention (often 2 to 5 times lower)

•Most production systems use MVCC variants (snapshot isolation) rather than pure locking, trading write skew vulnerability for better read concurrency

•Enabling snapshot isolation at SQL Server with 100,000 transactions per second typically eliminates blocking but can grow version store by gigabytes requiring careful monitoring

📌 Examples

Google Spanner uses TrueTime for strict serializability with typical 7 millisecond commit waits in single region, 80 to 150 milliseconds cross continent

Oracle Database defaults to Read Committed with MVCC; long running queries (10 to 30 minutes) often require dedicated replicas to avoid snapshot too old errors

Microsoft SQL Server default is locking based Read Committed; enterprises enable RCSI to decouple readers from writers and eliminate read/write deadlocks

← Back to Transaction Isolation Levels Overview