Transactions and Isolation Levels

Isolation levels define which concurrency anomalies a transaction may observe. Weaker levels allow more anomalies and more concurrency.

The anomalies

• Dirty read — reading data written by an uncommitted transaction.
• Non-repeatable read — reading a row twice in one transaction and getting different values because another transaction committed in between.
• Phantom read — re-running a range query and finding rows that were not there before.
• Lost update — two transactions read a value, both modify it, and one update overwrites the other.
• Write skew — two transactions read an overlapping set, each makes a decision that is valid alone, and together they violate an invariant. This is the anomaly that only serializable isolation prevents.

The levels

The same name means different things

This is the part that causes production bugs when a team moves between engines.

PostgreSQL implements three levels; READ UNCOMMITTED behaves as READ COMMITTED because MVCC never exposes uncommitted rows. Its REPEATABLE READ uses a snapshot taken at the first statement and prevents phantoms, but permits write skew. Its SERIALIZABLE adds Serializable Snapshot Isolation, which detects dangerous dependency cycles and aborts a transaction with SQLSTATE 40001.

MySQL/InnoDB defaults to REPEATABLE READ. Its consistent nonlocking reads use a snapshot, so plain SELECT does not see phantoms, but locking reads (SELECT ... FOR UPDATE, UPDATE, DELETE) read the latest committed data and take next-key locks — so a transaction can act on rows its own snapshot would have hidden. Its SERIALIZABLE works by taking shared locks on reads rather than detecting conflicts, so it blocks where PostgreSQL aborts.

Preventing lost updates

READ COMMITTED — the default in PostgreSQL — permits lost updates. Three ways to prevent them, in increasing order of cost:

-- 1. Atomic in-place update: no read-modify-write in the application at all.
UPDATE accounts SET balance = balance - 100 WHERE id = 42 AND balance >= 100;
 
-- 2. Pessimistic lock: serialise the readers of this row.
BEGIN;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;
-- application decides
UPDATE accounts SET balance = 900 WHERE id = 42;
COMMIT;
 
-- 3. Optimistic concurrency: fail if the row changed since it was read.
UPDATE accounts SET balance = 900, version = version + 1
WHERE id = 42 AND version = 17;
-- zero rows affected means someone else won; retry.

Option 1 is the best default when the update can be expressed as an expression over the current value. Option 3 scales better than option 2 under contention because it holds no locks while the application thinks.

Serializable in production

Serializable isolation is usable, and cheaper than most teams expect for short transactions. It requires two things:

1. A retry loop. Transactions can fail with a serialization error at commit time. That is not an exception to log and forget — it is the protocol working, and the transaction must be replayed.
2. Short transactions. The longer a transaction stays open, the more dependencies it accumulates and the more likely it is to abort.