They Both Needed Each Other’s Lock
A deadlock is not a random failure.
Two transactions each hold a lock the other one needs. Neither can move forward. PostgreSQL detects the cycle, picks one transaction as the victim, rolls it back, and lets the other proceed. The application receives an error. Most retry silently.
Nobody investigated. The same access pattern runs again an hour later. The deadlock happens again.
This script surfaces deadlock counts from pg_stat_database, active lock contention from pg_locks, and running transactions involved in waits so you can find the pattern and fix it.
Loading…
Reading the Results
Deadlock Counts (pg_stat_database)
| Column | What It Tells You |
|---|---|
datname | Database name |
deadlocks | Cumulative deadlock count since last pg_stat_reset() |
conflicts | Query conflicts with recovery on standby replicas |
temp_files | Temp files created — a sign of memory pressure during sorts or hash joins |
Active Lock Contention (pg_locks + pg_stat_activity)
| Column | What It Tells You |
|---|---|
pid | Process ID of the waiting session |
usename | Database user |
query | The SQL being blocked |
locktype | Type of lock requested — relation, tuple, transactionid, etc. |
mode | Lock mode requested — e.g. RowExclusiveLock, ShareRowExclusiveLock |
granted | false means this session is waiting |
wait_event | What specifically the session is waiting on |
What to Watch For
| Signal | What It Means |
|---|---|
deadlocks count climbing | A recurring access pattern is causing repeated deadlocks — needs investigation |
| Same table appearing repeatedly in lock waits | Index design or transaction ordering problem on that table |
locktype = relation on a busy table | Table-level lock — DDL, TRUNCATE, or LOCK TABLE is running against it |
mode = AccessExclusiveLock | The most aggressive lock — blocks all readers and writers |
Many rows with granted = false | A single blocker is causing a queue — find and resolve the root transaction |
| Deadlocks spiking after a deployment | Application code change introduced conflicting transaction ordering |
Reducing Deadlocks
Deadlocks are almost always fixable at the application level:
- Access tables in the same order across all transactions — if transaction A always locks
ordersbeforeline_items, transaction B should too - Keep transactions short — commit as soon as the work is done, do not hold locks across API calls or user input
- Use
SELECT ... FOR UPDATEexplicitly when you know you will modify a row — avoids the lock upgrade pattern that causes deadlocks - Retry on deadlock (error code
40P01) — but treat it as a monitoring signal, not a permanent fix
To enable deadlock logging in PostgreSQL:
Loading…
This logs any lock wait exceeding 1 second and all deadlock events to the PostgreSQL log.
Gareth Winterman