Two or more transactions block each other, waiting for locks the other holds.
A deadlock occurs when two or more transactions each hold a lock on a resource and are each waiting for a lock held by the other, creating a circular dependency. Neither transaction can proceed. The database automatically detects this cycle and kills one of the transactions (the 'deadlock victim'), rolling it back.
Deadlocks happen when transactions acquire locks in different orders on multiple rows/tables. For example, Transaction A locks Row 1 then tries to lock Row 2, while Transaction B has locked Row 2 and is waiting for Row 1. Common triggers: long-running transactions, update statements that lock many rows, indexing gaps causing range lock conflicts, or application code that holds transactions open too long.
1-- Two concurrent transactions creating a deadlock:2 3-- Transaction A (runs at same time as B)4BEGIN;5UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- Locks row 16-- (paused, waiting for TX B to release row 2)7UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- Wants row 28COMMIT;9 10-- Transaction B (runs at same time as A)11BEGIN;12UPDATE accounts SET balance = balance - 50 WHERE id = 2; -- Locks row 213-- (paused, waiting for TX A to release row 1)14UPDATE accounts SET balance = balance + 50 WHERE id = 1; -- Wants row 115COMMIT;16-- One of these will be chosen as the deadlock victim and rolled back1-- Fix 1: Always acquire locks in the SAME ORDER across all transactions2 3-- Both transactions now access rows in ascending ID order4 5-- Transaction A — fixed6BEGIN;7UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- Lock row 1 first8UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- Then row 29COMMIT;10 11-- Transaction B — fixed (same order: 1 then 2)12BEGIN;13UPDATE accounts SET balance = balance + 50 WHERE id = 1; -- Lock row 1 first14UPDATE accounts SET balance = balance - 50 WHERE id = 2; -- Then row 215COMMIT;16 17-- Fix 2: Use SELECT ... FOR UPDATE to lock rows at read time18-- so the lock order is known upfront19BEGIN;20SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE; -- Lock both in order21UPDATE accounts SET balance = balance - 100 WHERE id = 1;22UPDATE accounts SET balance = balance + 100 WHERE id = 2;23COMMIT;24 25-- Fix 3: Keep transactions short — do computation outside the transaction26-- Move heavy logic before BEGIN, only lock/write inside the transactionSimulate standard system builds to trigger compiler trace records and track memory crashes locally.
Transaction A locks `id=1` and waits for `id=2`. Transaction B holds `id=2` and waits for `id=1`. Neither can proceed. The database's deadlock detector (which runs periodically) identifies this cycle and kills one transaction with: `ERROR: deadlock detected` and `DETAIL: Process X waits for ShareLock on transaction Y`.