The Four SQL Standard Isolation Levels
The SQL standard defines four isolation levels, each permitting a different set of anomalies. Moving up the levels eliminates more anomalies but reduces concurrency and throughput.
READ UNCOMMITTED
The weakest level. A transaction can see data written by other transactions that have not yet committed (dirty reads). In practice, almost no production system uses this level because acting on data that might be rolled back leads to cascading errors. Some databases (like PostgreSQL) do not even implement it — requesting READ UNCOMMITTED silently upgrades to READ COMMITTED.
READ COMMITTED
Each SQL statement within a transaction sees only data that was committed before that statement began. If another transaction commits between two of your statements, the second statement will see the new data. This prevents dirty reads but allows non-repeatable reads (reading the same row twice yields different values) and phantom reads (a range query returns different rows on re-execution). PostgreSQL uses READ COMMITTED as its default. Oracle also defaults to this level.
REPEATABLE READ
A snapshot of the database is taken at transaction start (or at the first read, depending on the engine). All reads within the transaction see this consistent snapshot, regardless of concurrent commits. This prevents dirty reads and non-repeatable reads. Whether it prevents phantoms depends on the implementation: the SQL standard says REPEATABLE READ may allow phantoms, but MySQL InnoDB's REPEATABLE READ uses gap locks that also prevent phantom inserts within locked ranges. MySQL InnoDB uses REPEATABLE READ as its default.
SERIALIZABLE
The strongest level. Transactions behave as if they executed one at a time in some serial order. No anomalies are possible — not dirty reads, not non-repeatable reads, not phantoms, and not write skew. Implementation varies: PostgreSQL uses Serializable Snapshot Isolation (SSI), which runs transactions on snapshots and detects dangerous read-write dependency cycles, aborting one transaction if a cycle is found. MySQL InnoDB uses strict two-phase locking (2PL) with gap locks, physically blocking concurrent access.
PostgreSQL's Snapshot Isolation Nuance
PostgreSQL's REPEATABLE READ actually provides snapshot isolation (SI), which is stronger than the SQL standard's definition. SI prevents phantoms (because the snapshot is frozen at transaction start) but still allows write skew. Two transactions can read overlapping data, make disjoint modifications, and both commit — even though the combined result violates a constraint. Only PostgreSQL's SERIALIZABLE level (SSI) detects and prevents write skew by tracking read-write dependencies.
MySQL's Gap Locks
MySQL InnoDB at REPEATABLE READ prevents phantoms using gap locks: when a transaction performs a range scan, InnoDB locks not only the existing rows but also the "gaps" between index records. This physically blocks other transactions from inserting new rows that would fall within the range. The trade-off is reduced concurrency — gap locks can cause lock waits and deadlocks that would not occur under pure snapshot isolation.
Isolation Levels in PostgreSQL vs MySQL
Setting the isolation level:
-- PostgreSQL
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT * FROM accounts WHERE balance > 1000;
-- SSI tracks this read dependency
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
COMMIT; -- may be aborted if SSI detects a cycle
-- MySQL InnoDB
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM accounts WHERE balance > 1000;
-- Gap lock placed on balance > 1000 range
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
COMMIT;
Write Skew: PostgreSQL REPEATABLE READ vs SERIALIZABLE:
-- Table: doctors(name, on_call BOOLEAN)
-- Constraint: at least 1 doctor must be on-call
-- Both Dr. A and Dr. B are currently on-call
-- T1 (REPEATABLE READ):
BEGIN;
SELECT COUNT(*) FROM doctors WHERE on_call = true; -- sees 2
UPDATE doctors SET on_call = false WHERE name = 'A';
COMMIT; -- succeeds (snapshot still shows B on-call)
-- T2 (REPEATABLE READ, concurrent):
BEGIN;
SELECT COUNT(*) FROM doctors WHERE on_call = true; -- sees 2 (snapshot!)
UPDATE doctors SET on_call = false WHERE name = 'B';
COMMIT; -- succeeds! Both committed → 0 on-call (write skew!)
-- With SERIALIZABLE (SSI):
-- PostgreSQL detects the read→write dependency cycle
-- and aborts one of the transactions with:
-- ERROR: could not serialize access due to read/write dependencies
Anomaly Matrix:
| Level | Dirty Read | Non-Repeatable | Phantom | Write Skew |
|---|---|---|---|---|
| READ UNCOMMITTED | Yes | Yes | Yes | Yes |
| READ COMMITTED | No | Yes | Yes | Yes |
| REPEATABLE READ | No | No | Maybe* | Yes |
| SERIALIZABLE | No | No | No | No |
*MySQL RR prevents phantoms via gap locks; PostgreSQL RR (SI) prevents phantoms via snapshot but allows write skew.