Skip to content

Deadlock Detected

Error message

                deadlock detected
              

Quick summary

Two transactions are waiting for each other's locks, creating a circular dependency. The database detects this and kills one transaction.

Why this happens

  • Two transactions lock the same rows in opposite orders

  • Long-running transactions holding locks while waiting for other locks

  • Application code that acquires locks in inconsistent orders

Minimal example

✗ Broken code
-- Transaction A: locks row 1, then row 2
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
-- Transaction B (concurrent): locks row 2, then row 1 → DEADLOCK
✓ Fixed code
-- Both transactions lock in the same order (1 then 2)
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
COMMIT;

If both transactions lock rows in the same order, the circular wait is broken and no deadlock occurs.

How to diagnose

  • Check the database logs for deadlock details — which transactions and queries were involved

  • Identify the resources (rows/tables) each transaction was locking

  • Determine if the transactions lock resources in different orders

How to fix

  • Retry the killed transaction — deadlocks are often transient

  • Ensure all transactions lock resources in the same order

  • Keep transactions short to reduce the window for deadlocks

  • Use a lower isolation level if appropriate (e.g., READ COMMITTED instead of SERIALIZABLE)

How to prevent

  • Always acquire locks in a consistent order across all transactions

  • Keep transactions as short as possible

  • Use appropriate isolation levels — do not over-lock

  • Implement retry logic for transactions that fail due to deadlocks

Related Resources

Related Glossary

Related Lessons

← Back to language