TopolearnMost concurrency bugs in business logic are anomalies the chosen isolation level permits.
Perfect isolation (every transaction behaves as if it ran alone) costs throughput, so SQL defines weaker isolation levels by the anomalies they allow:
- Dirty read: seeing another transaction's uncommitted changes.
- Non-repeatable read: reading the same row twice and getting different values because someone committed in between.
- Phantom read: re-running a query and getting new rows that match.
- Lost update: two transactions read a value, both write back a modified copy, and one write silently wins.
- Write skew: two transactions read overlapping data, each makes a decision based on it, and together they break a rule (two doctors both go off call because each saw the other on call).
The standard levels, weakest to strongest: Read Uncommitted, Read Committed, Repeatable Read, Serializable. Real databases differ: PostgreSQL defaults to Read Committed and never allows dirty reads; its Repeatable Read is snapshot isolation (no phantoms, but write skew possible); its Serializable detects dangerous patterns and aborts one transaction. MySQL InnoDB defaults to Repeatable Read; SQLite is serializable.
Practical patterns: use atomic statements (SET stock = stock - 1 WHERE stock > 0), lock the rows you'll update (SELECT ... FOR UPDATE), or run at Serializable and retry transactions that fail with a serialization error.
Example
CREATE TABLE item (id int PRIMARY KEY, stock int NOT NULL);
INSERT INTO item VALUES (1, 10);
-- Lost update at Read Committed, two sessions A and B:
-- A: BEGIN; SELECT stock FROM item WHERE id = 1; -- 10
-- B: BEGIN; SELECT stock FROM item WHERE id = 1; -- 10
-- A: UPDATE item SET stock = 9 WHERE id = 1; COMMIT;
-- B: UPDATE item SET stock = 9 WHERE id = 1; COMMIT; -- one sale lost
-- Fix 1: let the database do the read-modify-write atomically
UPDATE item SET stock = stock - 1 WHERE id = 1 AND stock > 0;
-- Fix 2: lock the row you are about to change
BEGIN;
SELECT stock FROM item WHERE id = 1 FOR UPDATE; -- B waits here
UPDATE item SET stock = stock - 1 WHERE id = 1;
COMMIT;
-- Fix 3: BEGIN ISOLATION LEVEL SERIALIZABLE; and retry on SQLSTATE 40001Remember
- Isolation levels are defined by which anomalies they allow
- Know your default: PostgreSQL Read Committed, MySQL Repeatable Read
- Prefer atomic updates or row locks; at Serializable, retry on failure
Watch out for
- Read-then-write in application code at Read Committed, causing lost updates under load.