Alert Runbooks
Alert RunbooksAlert runbooksalerts-postgresql-exporterDeadlockDetected

DeadlockDetected

Runbook: DeadlockDetected Alert

Alert Details

Description

This alert triggers when the deadlock counter of a database increases and stays active for 30 minutes. A deadlock occurs when two or more transactions are each waiting for a lock held by the other, causing all of them to block indefinitely. PostgreSQL detects this automatically and aborts one of the transactions to break the cycle.

Possible Causes

Troubleshooting Steps

1. Check how many deadlocks have occurred per database

1
2
3
4
5
6
7
8
9

SELECT
    datname AS database,
    deadlocks,
    conflicts,
    blk_read_time,
    blk_write_time
FROM pg_stat_database
WHERE datname NOT LIKE 'template%'
ORDER BY deadlocks DESC;

High deadlocks on a specific database narrows the scope. Note which database is affected before proceeding.

2. Check PostgreSQL logs for deadlock details

PostgreSQL logs the full deadlock graph when it aborts a transaction. Look for lines containing deadlock detected in the database logs:

ERROR: deadlock detected
DETAIL: Process 1234 waits for ShareLock on transaction 5678; blocked by process 9012.
        Process 9012 waits for ShareLock on transaction 5678; blocked by process 1234.
HINT: See server log for query details.

The log entries include the exact queries and the lock order that caused the deadlock. This is the most reliable way to identify the root cause.

3. Identify currently waiting/blocked transactions

If deadlocks are still actively occurring, check for transactions that are blocked on locks right now:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocked_activity.application_name,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_query,
    blocking_activity.query AS blocking_query,
    EXTRACT(EPOCH FROM now() - blocked_activity.query_start) AS blocked_for_seconds
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

4. Identify which tables are involved

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

SELECT
    relation::regclass AS table_name,
    mode,
    granted,
    pid,
    pg_stat_activity.query,
    pg_stat_activity.state,
    EXTRACT(EPOCH FROM now() - pg_stat_activity.query_start) AS lock_age_seconds
FROM pg_locks
JOIN pg_stat_activity USING (pid)
WHERE relation IS NOT NULL
    AND NOT granted
ORDER BY lock_age_seconds DESC;

Knowing which tables are involved helps identify the application paths causing the deadlock.

5. Terminate blocking processes if deadlocks are ongoing

If a transaction has been blocking others for an unacceptable duration, terminate it:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT
    pg_terminate_backend(pid),
    usename,
    datname,
    application_name,
    state,
    query,
    EXTRACT(EPOCH FROM now() - query_start) AS query_age_seconds
FROM pg_stat_activity
WHERE pid IN ('<replace_with_blocking_pids>');

PostgreSQL will already have aborted one side of a completed deadlock automatically — only use this if blocking is still ongoing.

6. Long-term fix

Deadlocks are an application-level problem. After identifying the queries involved, the fix is usually one of: