SQL Isolation Levels
Isolation levels define the degree to which a transaction is isolated from modifications made by other concurrent transactions. They represent a trade-off between data correctness and performance — higher isolation prevents more anomalies but reduces concurrency. Defined by the SQL standard (SQL-92) and implemented via locking or MVCC mechanisms.
The Four Standard Levels
Read Uncommitted
The lowest isolation level. Transactions can read data modified by other uncommitted transactions.
- Allows: Dirty reads, Non-repeatable reads, Phantom reads
- Use case: Approximate counts, monitoring dashboards where stale data is acceptable
- Implementation: No read locks; write locks only
Read Committed
A transaction only sees data committed before each statement begins. The default in PostgreSQL, Oracle, and SQL Server.
- Prevents: Dirty reads
- Allows: Non-repeatable reads, Phantom reads
- Implementation: MVCC — each statement gets a fresh snapshot; or short-duration shared locks released after each read
Repeatable Read
A transaction sees a consistent snapshot from the start of the transaction. Re-reading the same row always returns the same value. Default in MySQL (InnoDB).
- Prevents: Dirty reads, Non-repeatable reads
- Allows: Phantom reads (in standard SQL; MySQL’s implementation also prevents phantoms via gap locks)
- Implementation: MVCC snapshot at transaction start; or shared locks held until commit (Strict 2PL)
Serializable
The highest isolation level. Transactions execute as if they were serial (one at a time). Prevents all anomalies.
- Prevents: Dirty reads, Non-repeatable reads, Phantom reads, Write skew
- Implementation:
- Lock-based: Rigorous 2PL + predicate/range locks
- MVCC-based: Serializable Snapshot Isolation (SSI) in PostgreSQL — detects and aborts transactions with potential serialization conflicts
Concurrency Anomalies
| Anomaly | Description | Prevented at |
|---|---|---|
| Dirty Read | Reading uncommitted data from another transaction | Read Committed |
| Non-Repeatable Read | Re-reading a row yields different values (another txn modified & committed) | Repeatable Read |
| Phantom Read | Re-executing a range query yields new rows (another txn inserted & committed) | Serializable |
| Write Skew | Two transactions read overlapping data, make disjoint updates, and both commit — violating a constraint neither saw | Serializable (SSI) |
| Lost Update | Two transactions read the same value, both update it, and one overwrites the other | Repeatable Read |
Isolation Level Matrix
| Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible |
| Read Committed | No | Possible | Possible |
| Repeatable Read | No | No | Possible* |
| Serializable | No | No | No |
*MySQL/InnoDB’s Repeatable Read also prevents phantoms via next-key locking.
Setting Isolation Level
-- Per transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- ... queries ...
COMMIT;
-- Session-wide (PostgreSQL)
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;Practical Guidance
- Default to Read Committed for most OLTP workloads — good balance of correctness and concurrency
- Use Serializable for financial transactions, inventory management, or anywhere correctness is critical
- Understand your DBMS — the same isolation level name can behave differently across implementations (e.g., MySQL’s Repeatable Read vs PostgreSQL’s)
- Higher isolation = more deadlock risk and lower throughput