1---
2title: Prevent Deadlocks with Consistent Lock Ordering
3impact: MEDIUM-HIGH
4impactDescription: Eliminate deadlock errors, improve reliability
5tags: deadlocks, locking, transactions, ordering
6---
7 
8## Prevent Deadlocks with Consistent Lock Ordering
9 
10Deadlocks occur when transactions lock resources in different orders. Always
11acquire locks in a consistent order.
12 
13**Incorrect (inconsistent lock ordering):**
14 
15```sql
16-- Transaction A                    -- Transaction B
17begin;                              begin;
18update accounts                     update accounts
19set balance = balance - 100         set balance = balance - 50
20where id = 1;                       where id = 2;  -- B locks row 2
21 
22update accounts                     update accounts
23set balance = balance + 100         set balance = balance + 50
24where id = 2;  -- A waits for B     where id = 1;  -- B waits for A
25 
26-- DEADLOCK! Both waiting for each other
27```
28 
29**Correct (lock rows in consistent order first):**
30 
31```sql
32-- Explicitly acquire locks in ID order before updating
33begin;
34select * from accounts where id in (1, 2) order by id for update;
35 
36-- Now perform updates in any order - locks already held
37update accounts set balance = balance - 100 where id = 1;
38update accounts set balance = balance + 100 where id = 2;
39commit;
40```
41 
42Alternative: use a single statement to update atomically:
43 
44```sql
45-- Single statement acquires all locks atomically
46begin;
47update accounts
48set balance = balance + case id
49  when 1 then -100
50  when 2 then 100
51end
52where id in (1, 2);
53commit;
54```
55 
56Detect deadlocks in logs:
57 
58```sql
59-- Check for recent deadlocks
60select * from pg_stat_database where deadlocks > 0;
61 
62-- Enable deadlock logging
63set log_lock_waits = on;
64set deadlock_timeout = '1s';
65```
66 
67Reference:
68[Deadlocks](https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-DEADLOCKS)
69