How to Detect Deadlock in PostgreSQL: Simple Steps

In PostgreSQL, you can detect deadlocks by checking the pg_locks system view and reviewing the server logs where deadlock errors are recorded. Additionally, enabling log_lock_waits and setting deadlock_timeout helps capture deadlock events automatically in the logs.

Syntax

To detect deadlocks, you mainly use the pg_locks system view combined with pg_stat_activity to see which queries hold or wait for locks. You can also configure logging parameters to capture deadlock events.

• pg_locks: Shows current locks held or awaited by transactions.
• pg_stat_activity: Shows active queries and their states.
• log_lock_waits: When enabled, logs lock waits longer than deadlock_timeout.
• deadlock_timeout: Time to wait before checking for deadlocks.

sql

SELECT
  l.locktype,
  l.database,
  l.relation,
  l.page,
  l.tuple,
  l.virtualtransaction,
  l.pid,
  a.query AS waiting_query,
  a.state AS waiting_state
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted;

Example

This example shows how to find transactions waiting for locks that could indicate a deadlock situation. It queries pg_locks joined with pg_stat_activity to display waiting queries.

sql

SELECT
  l.pid,
  a.usename,
  a.query,
  l.mode,
  l.granted
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted
ORDER BY l.pid;

Output

 pid | usename |               query                |       mode       | granted 
-----+---------+----------------------------------+------------------+---------
 123 | alice   | UPDATE accounts SET balance=1000  | ExclusiveLock    | f
 124 | bob     | DELETE FROM accounts WHERE id=5   | RowExclusiveLock | f
(2 rows)

Common Pitfalls

Common mistakes when detecting deadlocks include:

• Not enabling log_lock_waits and deadlock_timeout, so deadlock info is missing from logs.
• Ignoring the pg_locks view state and only checking pg_stat_activity.
• Assuming all lock waits are deadlocks; some waits are normal and resolve without error.

Always check logs for deadlock detected errors to confirm deadlocks.

sql

/* Wrong: Not enabling logging */
-- No deadlock info in logs

/* Right: Enable logging for lock waits */
ALTER SYSTEM SET log_lock_waits = ON;
ALTER SYSTEM SET deadlock_timeout = '1s';
SELECT pg_reload_conf();

Quick Reference

Setting / View	Purpose
pg_locks	Shows current locks and waiting transactions
pg_stat_activity	Shows active queries and their states
log_lock_waits	Logs lock waits longer than deadlock_timeout
deadlock_timeout	Time before checking for deadlocks
PostgreSQL Logs	Contains deadlock error messages

Key Takeaways

Use the pg_locks view joined with pg_stat_activity to find waiting transactions.

Enable log_lock_waits and set deadlock_timeout to capture deadlocks in logs.

Check PostgreSQL logs for 'deadlock detected' errors to confirm deadlocks.

Not all lock waits are deadlocks; analyze carefully before concluding.

Reload configuration after changing logging settings to apply them.