Deadlock detection and prevention in PostgreSQL

Introduction

Deadlocks happen when two or more database tasks wait for each other to finish, causing a standstill. Detecting and preventing deadlocks helps keep the database running smoothly without freezing.

When multiple users try to update the same data at the same time.

When transactions lock resources in different orders causing a cycle.

When you want to ensure your application handles database conflicts gracefully.

When you notice your database queries sometimes hang or fail unexpectedly.

When designing systems that require high concurrency and data integrity.

Syntax

PostgreSQL

-- PostgreSQL automatically detects deadlocks and aborts one transaction.
-- To prevent deadlocks, use consistent locking order and short transactions.

-- Example: Lock rows in the same order in all transactions
BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) FOR UPDATE;
-- perform updates
COMMIT;

PostgreSQL detects deadlocks automatically and aborts one of the conflicting transactions.

Prevent deadlocks by accessing tables and rows in the same order in all transactions.

Examples

This transaction locks account 1 first, then account 2, preventing deadlocks if all transactions follow this order.

PostgreSQL

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
-- update balances
COMMIT;

This transaction locks account 2 first, then account 1, which can cause deadlocks if another transaction locks in reverse order.

PostgreSQL

BEGIN;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- update balances
COMMIT;

This query shows current sessions waiting for locks, helping to identify deadlock situations.

PostgreSQL

-- To detect deadlocks, check PostgreSQL logs or use:
SELECT * FROM pg_stat_activity WHERE wait_event_type = 'Lock';

Sample Program

This example shows two sessions locking rows in opposite order, causing a deadlock. PostgreSQL detects it and aborts one transaction to resolve the deadlock.

PostgreSQL

-- Simulate deadlock detection
-- Session 1
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- Session 2
BEGIN;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;

-- Session 1 tries to lock account 2
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
-- Session 2 tries to lock account 1
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- PostgreSQL detects deadlock and aborts one transaction automatically.

Important Notes

Keep transactions short to reduce deadlock chances.

Use consistent locking order in your application code.

Check PostgreSQL logs for deadlock details if errors occur.

Summary

Deadlocks happen when transactions wait on each other in a cycle.

PostgreSQL detects deadlocks automatically and aborts one transaction.

Prevent deadlocks by locking resources in the same order and keeping transactions short.