TopolearnConcurrency bugs like lost updates and write skew pass every test and then corrupt production data.
The SQL standard defines isolation levels by the anomalies they forbid: dirty reads (seeing uncommitted data), non-repeatable reads (a row changes between two reads), phantoms (new rows appear in a repeated query) and serialization anomalies (a result impossible in any one-at-a-time order). PostgreSQL implements three distinct levels.
Read Committed (the default in PostgreSQL, Oracle and SQL Server) takes a new snapshot for each statement. Dirty reads never happen, but two statements in one transaction can see different data. Its classic bug is the lost update done in application code: two sessions read stock = 5, both compute 4, both write 4. Fix it by doing the arithmetic in SQL (SET stock = stock - 1), which re-reads the latest committed row, or by locking the row with SELECT ... FOR UPDATE.
Repeatable Read takes one snapshot at the first statement, so the whole transaction sees a frozen view. In PostgreSQL this is snapshot isolation and also prevents phantoms. If you try to update a row someone else changed and committed after your snapshot, you get ERROR: could not serialize access due to concurrent update (SQLSTATE 40001). What it still allows is write skew: two doctors each check "is someone else on call?", both see yes, both go off call. Each transaction was valid alone; together they broke the rule.
Serializable uses serializable snapshot isolation (SSI): it tracks read/write dependencies and aborts one transaction, again with 40001, when the outcome could not have arisen serially. It prevents write skew with no explicit locks.
At Repeatable Read and Serializable, your application must retry transactions that fail with 40001. That is part of the contract, not an exceptional failure.
Dialects: READ UNCOMMITTED behaves as Read Committed in PostgreSQL. MySQL InnoDB defaults to Repeatable Read, with different, lock-based semantics.
Example
-- Lost update in READ COMMITTED (application-side arithmetic)
-- A: SELECT stock FROM products WHERE id = 3; -- 5
-- B: SELECT stock FROM products WHERE id = 3; -- 5
-- A: UPDATE products SET stock = 4 WHERE id = 3; COMMIT;
-- B: UPDATE products SET stock = 4 WHERE id = 3; COMMIT; -- one sale lost
-- Safe: arithmetic in SQL re-reads the committed row
UPDATE products SET stock = stock - 1 WHERE id = 3 AND stock > 0;
-- Write skew prevented by SERIALIZABLE
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM shifts WHERE on_call AND day = '2026-03-15'; -- 2
UPDATE shifts SET on_call = false
WHERE doctor = 'alice' AND day = '2026-03-15';
COMMIT;
-- If bob ran the same transaction concurrently, one COMMIT fails with:
-- ERROR: could not serialize access due to read/write dependencies
-- among transactions (SQLSTATE 40001) -> retry itRemember
- Read Committed: new snapshot per statement; watch for lost updates
- Repeatable Read: one snapshot per transaction; still allows write skew
- Serializable (SSI) prevents all anomalies by aborting conflicting transactions
- At Repeatable Read and Serializable, retry on SQLSTATE 40001
Watch out for
- Raising the isolation level without adding retry logic, turning anomalies into unhandled errors.