Isolation

• Concurrent transactions are isolated from one another - each behaves as if it were the only transaction running.

Read anomalies

• Dirty read: reading data another transaction hasn't committed yet (it might roll back)
• Non-repeatable read: reading the same row twice in one transaction returns different values
• Phantom read: running the same query twice returns new rows
• Lost update: two read-modify-writes race and one is silently overwritten. Not on the SQL standard's list, so no level below Serializable is guaranteed to stop it — this is why SELECT ... FOR UPDATE exists

Isolation Levels (least → most strict)

• The standard defines levels by which anomalies they permit (❌ can happen, ✅ cannot happened)

Level	Dirty	Non-repeatable	Phantom	Default in
Read Uncommitted	❌	❌	❌	(almost never used)
Read Committed	✅	❌	❌	PostgreSQL, Oracle, SQL Server
Repeatable Read	✅	✅	❌*	MySQL/InnoDB
Serializable	✅	✅	✅	SQLite

* PostgreSQL's repeatable read is snapshot isolation and blocks phantoms too, which the standard doesn't require.

The snapshot is what separates the two middle levels: Read Committed takes a fresh one per statement, Repeatable Read takes one per transaction.

How it's implemented

• Locking-based (e.g. SQL Server) — read/write locks held longer at higher levels; Serializable adds range locks
• MVCC (e.g. PostgreSQL, MySQL) — each transaction reads a snapshot (essentially optimistic locking at the engine level); Serializable additionally detects dependency cycles and aborts one transaction at commit

Higher isolation = more locking/conflict detection = less throughput.

Deadlocks

• Two transactions each hold a lock the other wants. The engine detects the cycle and kills one with an error
• Mitigation: acquire locks in a consistent order, keep transactions short, and retry the victim
• Application code must be prepared to retry — at Serializable this is not optional, since the engine aborts transactions as a normal part of operation

Practical notes

• Most applications use Read Committed + explicit SELECT ... FOR UPDATE on the rows that need protection, rather than raising the entire isolation level
• Keep transactions short — a long one holds locks, pins old row versions (blocking MVCC cleanup), and blocks WAL recycling
• Never leave a transaction open across a network call or user think-time
• Defaults differ per engine (see table) — a classic source of portability bugs