Alert rule: PostgreSQLDeadlockConflicts

Overview

This alert triggers when more than 10 deadlocks have been detected on a database in the last 5 minutes. Deadlocks occur when two or more transactions are each waiting for the other to release a lock. PostgreSQL resolves them automatically by aborting one of the transactions, but frequent deadlocks indicate a problem in the application’s transaction logic.

Steps for Debugging

There are two ways to solve this issue:

First Option: Identify the conflicting queries via pg_stat_activity. PostgreSQL logs deadlock details, but you can also find the involved relations by inspecting lock wait chains in your database.

Find queries currently waiting on locks

SELECT pid, wait_event_type, wait_event, state, query
FROM pg_stat_activity
WHERE wait_event IS NOT NULL
ORDER BY wait_event_type;

Second Option: Fix the application’s transaction ordering. Deadlocks are typically caused by transactions acquiring locks in different orders. Ensure that all transactions acquire locks on tables and rows in a consistent order. Consider using SELECT … FOR UPDATE explicitly to control lock acquisition order, or reduce transaction scope to hold locks for shorter periods.