Anomalies and Isolation Levels

Trading safety for concurrency

Weaker isolation lets more transactions run concurrently but allows anomalies. A dirty read reads another transaction's uncommitted change, which may be rolled back. A non-repeatable read reads the same row twice and gets different values because another transaction committed an update in between. A phantom occurs when re-running a query returns new rows inserted (or rows deleted) by another committed transaction. A lost update happens when two transactions read a value, both compute a new one and the second write overwrites the first. Write skew happens when two transactions read overlapping data and update different rows, together breaking a rule (two doctors both going off call). The SQL standard defines READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE, mainly by which of the first three anomalies they forbid. Real systems differ: PostgreSQL's default is READ COMMITTED, its REPEATABLE READ uses snapshot isolation (no phantoms, but write skew is possible) and its SERIALIZABLE uses Serializable Snapshot Isolation; MySQL InnoDB defaults to REPEATABLE READ.

Isolation levels and the anomalies they allow (SQL standard)

Check your database's documentation for its exact behaviour.

level              dirty read   non-repeatable read   phantom
-----------------  -----------  --------------------  ---------
READ UNCOMMITTED   possible     possible              possible
READ COMMITTED     no           possible              possible
REPEATABLE READ    no           no                    possible (standard)
SERIALIZABLE       no           no                    no

lost update fix:  UPDATE stock SET qty = qty - 1 WHERE sku = 'X' AND qty > 0;   -- atomic
             or:  SELECT qty FROM stock WHERE sku = 'X' FOR UPDATE;  ... then UPDATE

Read-modify-write needs care

Reading a value into application code and writing back a computed value is the classic lost-update bug. Use an atomic UPDATE, SELECT ... FOR UPDATE, optimistic version columns or SERIALIZABLE with retries.

Quick check: Transaction T1 reads a row, T2 updates and commits it, and T1 reads it again getting a different value. Which anomaly is this?

• Dirty read
• Phantom read
• Non-repeatable read
• Deadlock