Isolation Levels: From Read Committed to Serializable

1. Why isolation levels exist

An isolation level is the trade-off a database makes between correctness and throughput when multiple transactions access the same data concurrently. Full isolation, where every transaction behaves as though it were alone on the system, is theoretically the safest option but practically the most expensive: in the worst case it requires serializing transactions or applying broad locks, which invites waiting and deadlocks. With no isolation at all, throughput would be maximal, but data could be silently corrupted by concurrent access.

The SQL standard does not resolve this tension with a single answer but with four graduated isolation levels. Each stage disallows certain concurrency anomalies but costs more coordination effort in return. Choosing the right isolation level is therefore not a purely technical setting but a deliberate decision about which anomalies are tolerable for a given use case and which are not.

The following sections walk through each of the four isolation levels in detail, show the anomalies each one prevents, and compare the default settings of the most important relational databases.

2. The SQL standard: four isolation levels at a glance

The SQL-92 standard defines the four isolation levels READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE in terms of three anomalies each one prevents or allows: dirty read, non-repeatable read, and phantom read. Each higher level excludes at least one additional anomaly, without the lower-level anomalies necessarily disappearing entirely. This formal definition matters because it specifies what an isolation level must guarantee at minimum, not how it is implemented.

In practice, database vendors interpret the standard with varying strictness. PostgreSQL, for example, already prevents phantom reads at REPEATABLE READ through its snapshot isolation, even though the standard only requires that at SERIALIZABLE. Anyone relying on a specific isolation level in their design should therefore always check the documentation of the actual database, not just the generic name of the level.

3. Read Uncommitted: the weakest level

READ UNCOMMITTED is the weakest isolation level and allows dirty reads: a transaction may read data another transaction has already written but not yet committed. If the writing transaction is later rolled back, the reading transaction worked with values that never actually existed. This level offers virtually no protection against concurrency problems and is rarely used actively in practice today.

READ UNCOMMITTED makes sense almost exclusively for analytics where absolute precision does not matter and maximum throughput has priority, for example rough monitoring dashboards showing approximate counts. MySQL/InnoDB supports this level but does not use it as the default. PostgreSQL accepts READ UNCOMMITTED as syntax but internally treats it identically to READ COMMITTED, because PostgreSQL fundamentally never allows dirty reads.

4. Read Committed: the practical standard

READ COMMITTED is the most widely used isolation level and the default in PostgreSQL, Oracle, and SQL Server. It fully prevents dirty reads: a transaction only ever sees data that other transactions have already committed. Within the same transaction, however, the value read for a row can still change between two SELECT statements if another transaction commits in between, that is the non-repeatable read anomaly, which READ COMMITTED still explicitly allows.

Technically, modern databases mostly implement READ COMMITTED via MVCC: each individual statement within the transaction sees a fresh snapshot of the data committed at that moment, not the entire transaction seeing a single snapshot. This explains why two consecutive SELECTs in the same transaction can return different results, even though each one is individually consistent. For most web applications with short transactions, READ COMMITTED is a good trade-off between safety and performance.

-- Read Committed: non-repeatable read possible within a transaction
-- Session A:
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT price FROM products WHERE product_id = 42;  -- returns 19.99

-- Session B (concurrent, commits in the meantime):
-- UPDATE products SET price = 24.99 WHERE product_id = 42;
-- COMMIT;

-- Session A, same transaction, second query:
SELECT price FROM products WHERE product_id = 42;  -- now returns 24.99
COMMIT;
-- Both values are individually correct, but contradictory
-- within a single transaction, that is the non-repeatable read anomaly

5. Repeatable Read: a stable view within the transaction

REPEATABLE READ guarantees that a transaction always reads the same row with the same value throughout its entire lifetime, regardless of how many other transactions commit in between. This fully prevents non-repeatable reads. It is usually implemented via a single consistent snapshot created at transaction start and used for the entire transaction, instead of taking a new snapshot on every statement as with READ COMMITTED.

The SQL standard technically still allows phantom reads at REPEATABLE READ, meaning new rows that suddenly appear in a repeated range query due to a concurrent INSERT transaction. MySQL/InnoDB, which uses REPEATABLE READ as its default, prevents phantom reads in practice to a large extent through what are called next-key locks, a combination of row and gap locks. PostgreSQL also fully prevents phantom reads at REPEATABLE READ through its snapshot isolation, but detects a serialization failure on genuine write conflicts and aborts the transaction instead of letting it run silently incorrect.

-- Repeatable Read: stable snapshot across the entire transaction
-- Session A:
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE account_id = 1;  -- returns 1000.00

-- Session B (concurrent, commits in the meantime):
-- UPDATE accounts SET balance = 1500.00 WHERE account_id = 1;
-- COMMIT;

-- Session A, same transaction, second query of the same row:
SELECT balance FROM accounts WHERE account_id = 1;  -- still returns 1000.00
COMMIT;
-- The snapshot from transaction start stays stable for the entire
-- lifetime, regardless of what commits concurrently

6. Serializable: the strongest isolation

SERIALIZABLE is the strongest isolation level and guarantees that the result of concurrent transactions is always identical to some possible serial, that is sequential, ordering of those transactions. All three standard anomalies, dirty read, non-repeatable read, and phantom read, are thereby excluded. This guarantee has a cost: databases must either take genuine locks across entire value ranges or, as is common with modern MVCC systems, detect write conflicts at runtime and abort one of the involved transactions with a serialization failure error.

This serialization-failure mechanism means that application code under SERIALIZABLE must always implement retry logic in practice: a transaction aborted due to a conflict should automatically be retried, not surfaced as a final error to the user. Without this retry logic, SERIALIZABLE quickly leads to visible errors under high concurrency, even though the level technically works correctly.

-- Serializable: database detects write conflicts at runtime
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT SUM(amount) FROM transactions WHERE account_id = 7;
-- Application computes new balance based on this sum

INSERT INTO transactions (account_id, amount) VALUES (7, -50.00);
COMMIT;
-- On a genuine conflict with a concurrent transaction:
-- ERROR: could not serialize access due to read/write dependencies
-- Application code must catch this error and restart the transaction
-- entirely, not just retry the last statement

7. Default isolation level by database

Database vendors do not choose their default at random, but based on the trade-off between safety and performance most commonly expected by their community. Anyone relying on the default without checking it explicitly can get unexpectedly different isolation level guarantees when switching database engines.

8. Setting the isolation level in practice

The isolation level is set per transaction or per session using standardized syntax, though the exact scope varies slightly by database. The statement must come before the first data-modifying statement of the transaction, changing it later within a running transaction is not allowed in most databases or only affects future statements.

-- PostgreSQL and standard SQL syntax: per transaction
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- ... statements ...
COMMIT;

-- MySQL: isolation level for the session's next transaction
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- ... statements ...
COMMIT;

-- SQL Server: enable snapshot isolation as a READ COMMITTED alternative
ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON;

-- Check current isolation level (PostgreSQL)
SHOW TRANSACTION ISOLATION LEVEL;

9. Choosing the right isolation level for the use case

The choice of isolation level should always be derived from the consequences of a possible anomaly, not from a blanket preference for maximum safety. For most CRUD-heavy web applications with short, independent transactions, READ COMMITTED is fully sufficient, because individual statements usually need to be consistent on their own anyway, and it is rare for several related reads within one transaction to be critical.

REPEATABLE READ or SERIALIZABLE become relevant when a transaction performs multiple reads and depends on their consistency with each other, for example in financial reports, inventory reservations, or complex business rules based on several related values. SERIALIZABLE is the right choice when correctness has absolute priority and the application is prepared to catch serialization failures with a retry loop. For all other cases, a lower isolation level combined with targeted locking, as described in the articles on optimistic and pessimistic locking, is often the more pragmatic solution.

-- Retry wrapper for Serializable in pseudocode
-- (modeled after common database driver APIs)

-- function runWithRetry(work, maxAttempts = 3) {
--   for (let attempt = 1; attempt <= maxAttempts; attempt++) {
--     try {
--       db.execute("BEGIN");
--       db.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE");
--       const result = work();
--       db.execute("COMMIT");
--       return result;
--     } catch (error) {
--       db.execute("ROLLBACK");
--       if (error.code === 'SERIALIZATION_FAILURE' && attempt < maxAttempts) {
--         continue;  // full restart of the transaction
--       }
--       throw error;
--     }
--   }
-- }

10. Summary

The four isolation levels of the SQL standard, READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE, form a ladder between maximum concurrency and maximum correctness. Each level excludes one additional anomaly: READ COMMITTED prevents dirty reads, REPEATABLE READ additionally prevents non-repeatable reads, and SERIALIZABLE additionally prevents phantom reads while guaranteeing full serializability.

In practice, the defaults of major databases differ significantly, PostgreSQL and Oracle default to READ COMMITTED, MySQL/InnoDB to REPEATABLE READ. Anyone building or migrating an application across databases should therefore always set the isolation level explicitly instead of relying on implicit defaults. The choice should be guided by the actual consequences of an anomaly, not by a blanket preference for the strongest level available.

11. FAQ: Isolation Levels from Read Committed to Serializable