2 / 9 ACID, Isolation Levels & MVCC

Both Waiting Forever

Two transfer requests run concurrently at READ COMMITTED.

time Session 1 Session 2
t1 BEGIN; BEGIN;
t2 UPDATE accounts SET balance = balance + 100 WHERE acctnum = 11111;
t3 UPDATE accounts SET balance = balance + 100 WHERE acctnum = 22222;
t4 UPDATE accounts SET balance = balance - 100 WHERE acctnum = 22222; blocks
t5 UPDATE accounts SET balance = balance - 100 WHERE acctnum = 11111; blocks
t6 ? ?

What happens at t6?

A

Each blocked UPDATE silently matches zero rows because the target row is locked elsewhere, so both transactions commit with half the transfer applied.

B

After deadlock_timeout elapses (one second by default) the detector runs, aborts one of the two with ERROR: deadlock detected and rolls back its work, and the survivor proceeds and commits.

C

Both statements block until statement_timeout fires. PostgreSQL detects deadlocks on table-level locks only, not on row locks.

D

Both transactions are aborted, since the engine has no principled way to pick a winner from a two-cycle.