SQL Deadlock Detection and Prevention Strategies

What Is a Deadlock?

A deadlock occurs when two or more transactions each hold a lock that the other needs, creating a circular wait. Neither transaction can proceed, so the database must intervene by aborting one of them.

Classic Example

-- Transaction A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Locks row 1
-- waits for row 2...

-- Transaction B (concurrent)
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;   -- Locks row 2
UPDATE accounts SET balance = balance + 50 WHERE id = 1;   -- Waits for row 1 (DEADLOCK!)

Transaction A holds the lock on row 1 and waits for row 2. Transaction B holds the lock on row 2 and waits for row 1. Neither can proceed.

How Databases Detect Deadlocks

PostgreSQL

PostgreSQL runs a deadlock detector that periodically checks for circular waits (controlled by deadlock_timeout, default 1 second). When it detects a cycle, it aborts the transaction that is easiest to roll back (typically the one that has done the least work) and returns:

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

MySQL (InnoDB)

InnoDB maintains a wait-for graph and checks it immediately when a lock wait occurs. It chooses the transaction with the fewest undo log records as the victim:

ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

Viewing Deadlock Information

PostgreSQL

-- Check recent deadlocks in the log
-- postgresql.conf: log_lock_waits = on, deadlock_timeout = 1s

-- View currently blocked queries
SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks gl ON gl.locktype = bl.locktype
  AND gl.database IS NOT DISTINCT FROM bl.database
  AND gl.relation IS NOT DISTINCT FROM bl.relation
  AND gl.page IS NOT DISTINCT FROM bl.page
  AND gl.tuple IS NOT DISTINCT FROM bl.tuple
  AND gl.pid <> bl.pid
  AND gl.granted
JOIN pg_stat_activity blocking ON blocking.pid = gl.pid;

MySQL

-- Show the most recent deadlock
SHOW ENGINE INNODB STATUS;

-- Check current locks (MySQL 8.0+)
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

Prevention Strategy 1: Consistent Lock Ordering

The most effective prevention technique. If every transaction acquires locks in the same order, circular waits cannot form.

-- BAD: Transaction A locks row 1 then 2, Transaction B locks row 2 then 1
-- Transaction A
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Transaction B
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;

-- GOOD: Both transactions lock rows in id order
-- Transaction A
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Transaction B
UPDATE accounts SET balance = balance + 50 WHERE id = 1;  -- Same order: id 1 first
UPDATE accounts SET balance = balance - 50 WHERE id = 2;

In application code, sort the IDs before issuing updates:

-- Application sorts ids = [2, 1] -> [1, 2]
-- Then updates in order
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;

Prevention Strategy 2: Reduce Lock Duration

The shorter a transaction holds locks, the smaller the window for deadlocks.

-- BAD: Long transaction with locks held throughout
BEGIN;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;  -- Locks row
-- ... application does slow computation ...
UPDATE orders SET status = 'processed' WHERE id = 1;
COMMIT;

-- BETTER: Do computation outside the transaction
-- 1. Read data without locking
SELECT * FROM orders WHERE id = 1;
-- 2. Application computes the new values
-- 3. Short transaction with minimal lock time
BEGIN;
UPDATE orders SET status = 'processed' WHERE id = 1;
COMMIT;

Prevention Strategy 3: Use SELECT FOR UPDATE Wisely

Acquire all necessary locks upfront at the start of the transaction:

BEGIN;
-- Lock both rows immediately in a single statement (sorted order)
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

-- Now safely update both
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

The ORDER BY id in the SELECT FOR UPDATE ensures consistent lock ordering even within a single statement.

SKIP LOCKED for Queue Patterns

For job-queue patterns where workers compete for rows, SKIP LOCKED avoids blocking entirely:

BEGIN;
SELECT id, payload
FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- Process the job
UPDATE job_queue SET status = 'processing' WHERE id = <selected_id>;
COMMIT;

Each worker skips rows already locked by other workers, eliminating contention.

Prevention Strategy 4: Use Appropriate Isolation Levels

Higher isolation levels can increase deadlock frequency because they hold locks longer and detect more conflicts:

-- Serializable may cause more serialization failures
-- Use Read Committed unless you need stronger guarantees
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

If you use Serializable, implement retry logic:

-- Pseudocode
MAX_RETRIES = 3
FOR attempt IN 1..MAX_RETRIES:
  BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE
  TRY:
    -- business logic
    COMMIT
    BREAK
  CATCH serialization_failure OR deadlock_detected:
    ROLLBACK
    IF attempt == MAX_RETRIES: RAISE
    SLEEP(random_backoff)

Prevention Strategy 5: Batch Operations

Instead of updating rows one at a time in a loop, use a single statement:

-- BAD: Multiple statements, multiple lock acquisitions
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 101;
UPDATE inventory SET quantity = quantity - 2 WHERE product_id = 205;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 150;

-- BETTER: Single statement, predictable lock order
UPDATE inventory
SET quantity = quantity - v.qty
FROM (VALUES (101, 1), (150, 1), (205, 2)) AS v(product_id, qty)
WHERE inventory.product_id = v.product_id;

The database can optimize lock acquisition for a single statement, and you avoid interleaving with other transactions.

Prevention Strategy 6: Advisory Locks

For application-level coordination, PostgreSQL advisory locks provide a lightweight mechanism:

-- Acquire an advisory lock (blocks if already held)
SELECT pg_advisory_lock(hashtext('process_customer_42'));

-- Do work that should not run concurrently
UPDATE customers SET ... WHERE id = 42;
UPDATE orders SET ... WHERE customer_id = 42;

-- Release the lock
SELECT pg_advisory_unlock(hashtext('process_customer_42'));

Advisory locks exist outside the transaction system, so they must be explicitly released. Use pg_advisory_xact_lock for transaction-scoped advisory locks that auto-release on COMMIT/ROLLBACK.

Handling Deadlocks in Application Code

Even with prevention strategies, deadlocks can still occur. Your application must handle them:

-- PostgreSQL error code for deadlock: 40P01
-- MySQL error code: 1213

-- Application pattern (pseudocode):
-- 1. Catch the deadlock error
-- 2. Wait a random backoff period
-- 3. Retry the entire transaction (not just the failed statement)
-- 4. Give up after N retries

Key rules for retry logic:

• Retry the entire transaction, not just the last statement.
• Use exponential backoff with jitter to avoid thundering herd.
• Set a maximum retry count to prevent infinite loops.
• Log deadlocks for monitoring and analysis.

Monitoring Deadlocks

Track deadlock frequency to identify problematic patterns:

-- PostgreSQL: check deadlock count (since last stats reset)
SELECT deadlocks FROM pg_stat_database WHERE datname = current_database();

-- Reset statistics
SELECT pg_stat_reset();

Set up alerts when the deadlock rate exceeds a threshold. A spike in deadlocks usually indicates a new code path that acquires locks in an inconsistent order.

Summary

Deadlocks are an inevitable consequence of concurrent access to shared data. The most effective prevention is consistent lock ordering — always acquire locks on the same resources in the same order. Complement this with short transactions, upfront locking via SELECT FOR UPDATE, batch operations, and SKIP LOCKED for queue patterns. Always implement retry logic in your application, because no prevention strategy eliminates deadlocks entirely. Monitor deadlock frequency and investigate spikes promptly.