Under the Hood: Concurrency Anomalies & Isolation Levels
When multiple client transactions execute concurrently, the database must prevent data corruption without serializing all execution. The SQL standard defines four isolation levels based on which concurrency anomalies they prevent.
The Concurrency Anomaly Hierarchy
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read | Write Skew |
|---|---|---|---|---|
| Read Uncommitted | ⚠️ Occurs | ⚠️ Occurs | ⚠️ Occurs | ⚠️ Occurs |
| Read Committed (Postgres Default) | ✅ Prevented | ⚠️ Occurs | ⚠️ Occurs | ⚠️ Occurs |
| Repeatable Read | ✅ Prevented | ✅ Prevented | ✅ Prevented (in Postgres MVCC) | ⚠️ Occurs |
| Serializable | ✅ Prevented | ✅ Prevented | ✅ Prevented | ✅ Prevented |
Two caveats on this table. The lost update anomaly (two read-modify-write cycles overwriting each other) occurs at Read Committed; PostgreSQL's Repeatable Read prevents it by aborting the second writer with a serialization error, while MySQL InnoDB's Repeatable Read does not. And the SQL standard permits phantoms at Repeatable Read: PostgreSQL's snapshot prevents them, while InnoDB prevents them for plain reads via its snapshot and for locking reads via next-key locks.
Under the Hood: The Four Real-World Anomalies
- Dirty Read: Transaction A modifies row R. Transaction B reads the modified row before Transaction A commits. Transaction A rolls back; Transaction B has acted on phantom data.
- Non-Repeatable Read: Transaction A reads row R. Transaction B updates row R and commits. Transaction A reads row R again and observes different column values.
- Phantom Read: Transaction A queries
WHERE status = 'pending'(retrieving 3 rows). Transaction B inserts a new row withstatus = 'pending'and commits. Transaction A executes the query again and observes 4 rows. - Write Skew: Two doctors on on-call shift concurrently request leave. Rule: At least one doctor must remain on call. Both check: count is 2. Both submit leave transactions. Both commit. Zero doctors remain on call! Snapshot-based Repeatable Read permits this. Preventing it requires Serializable isolation (PostgreSQL's Serializable Snapshot Isolation (SSI) detects and aborts one transaction; lock-based Serializable in MySQL and SQL Server blocks it) or explicitly locking the rows the check depends on (
SELECT ... FOR UPDATEon the doctors' rows).
Worked example: the lost update and three fixes
Two requests each add 10 to an account balance of 100.
| Time | Transaction A | Transaction B |
|---|---|---|
| t1 | SELECT balance returns 100 | |
| t2 | SELECT balance returns 100 | |
| t3 | UPDATE SET balance = 110, COMMIT | |
| t4 | UPDATE SET balance = 110, COMMIT |
The final balance is 110 instead of 120, and this happens at Read Committed with no error. Three fixes:
- Atomic update:
UPDATE accounts SET balance = balance + 10 WHERE id = 1;The row lock serializes the two writers. This is the simplest and best option when possible. - Pessimistic lock:
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;B blocks at t2 until A commits, then reads 110. - Optimistic check:
UPDATE ... SET balance = 110, version = 8 WHERE id = 1 AND version = 7;B updates 0 rows, then re-reads and retries.
Lock types you will meet
| Lock | Taken by | Blocks |
|---|---|---|
| Row exclusive (FOR UPDATE) | UPDATE, DELETE, SELECT FOR UPDATE | Other writers and FOR UPDATE on the same row, but not plain MVCC reads |
| Row shared (FOR SHARE) | SELECT FOR SHARE, FK checks | Writers on that row |
| Gap / next-key (InnoDB) | Range reads at Repeatable Read | Inserts into the locked range, which prevents phantoms |
| Predicate SIRead (PostgreSQL SSI) | Reads at Serializable | Blocks nothing; tracked to detect dangerous read-write cycles |
| Table ACCESS EXCLUSIVE | ALTER TABLE, DROP, some DDL | Everything, including reads |
Deadlocks
A locks row 1 then wants row 2, while B locks row 2 then wants row 1. The database detects the cycle (PostgreSQL checks after deadlock_timeout, 1 s by default) and aborts one transaction. The prevention is to always lock rows in a consistent order, for example by ascending primary key.
How real systems implement Serializable
- PostgreSQL: Serializable Snapshot Isolation, which runs optimistically and aborts on dangerous structures.
- MySQL InnoDB: Serializable turns plain SELECTs into locking reads (two-phase locking).
- CockroachDB: Serializable by default, using timestamps and transaction restarts.
- Spanner: strict serializability via two-phase locking plus TrueTime.