Prevent and Resolve Deadlocks in SQL Transactions

Deadlocks occur when two transactions hold locks that the other needs, creating a circular wait. This approach handles detecting deadlock patterns, applying consistent lock ordering, choosing isolation levels wisely, and implementing client-side retry logic for production database resilience.

When to Use This

• Concurrent transactions fail intermittently with deadlock errors
• Batch operations and user-facing transactions compete for the same rows
• Row-level locking is required but performance must remain acceptable

Problem

Two concurrent fund transfers between accounts A and B deadlock because Transaction 1 locks A then waits for B, while Transaction 2 locks B then waits for A.

Solution

1. Consistent Lock Ordering

// transactions/TransferService.ts
class TransferService {
  async transfer(fromId: string, toId: string, amount: number): Promise<void> {
    // Always lock in a consistent order (e.g., by account ID)
    const [first, second] = [fromId, toId].sort();

    await db.transaction(async (trx) => {
      // Lock first account
      const fromAccount = await trx('accounts')
        .where('id', first)
        .forUpdate()
        .first();

      // Lock second account
      const toAccount = await trx('accounts')
        .where('id', second)
        .forUpdate()
        .first();

      // Transfer logic
      await trx('accounts')
        .where('id', fromId)
        .decrement('balance', amount);

      await trx('accounts')
        .where('id', toId)
        .increment('balance', amount);
    });
  }
}

2. Optimistic Locking (No Database Locks)

// transactions/OptimisticUpdate.ts
class InventoryService {
  async updateStock(productId: string, delta: number): Promise<boolean> {
    const result = await db('inventory')
      .where('product_id', productId)
      .where('version', db('inventory')
        .select('version')
        .where('product_id', productId)
      )
      .update({
        quantity: db.raw('quantity + ?', [delta]),
        version: db.raw('version + 1'),
      });

    return result > 0; // true if update succeeded
  }
}

3. Isolation Level Selection

-- Read Committed: default, prevents dirty reads
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Repeatable Read: prevents non-repeatable reads (higher lock contention)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Serializable: full isolation, highest deadlock risk
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

4. Deadlock-Resilient Retry Logic

// transactions/RetryWithBackoff.ts
async function executeWithRetry<T>(
  operation: () => Promise<T>,
  maxRetries = 3
): Promise<T> {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      return await operation();
    } catch (error) {
      if (isDeadlockError(error) && attempt < maxRetries) {
        const delay = Math.pow(2, attempt) * 100 + Math.random() * 100;
        await sleep(delay);
        continue;
      }
      throw error;
    }
  }

  throw new Error('Max retries exceeded');
}

// Usage
await executeWithRetry(() => transferService.transfer('A', 'B', 100));

5. Detecting Deadlocks in PostgreSQL

-- View current locks
SELECT
  blocked_locks.pid AS blocked_pid,
  blocked_activity.usename AS blocked_user,
  blocking_locks.pid AS blocking_pid,
  blocking_activity.usename AS blocking_user,
  blocked_activity.query AS blocked_statement,
  blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.relation = blocked_locks.relation
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- Log deadlock details
SHOW log_lock_waits;  -- should be 'on'

How It Works

• Consistent ordering prevents circular waits by always acquiring locks in the same sequence
• Optimistic locking uses versioning instead of database locks, reducing contention
• Isolation levels trade consistency against concurrency; lower levels have fewer deadlocks
• Retry logic with exponential backoff handles transient deadlocks that resolve quickly

Production Considerations

• Keep transactions short to minimize lock duration
• Use SELECT FOR UPDATE SKIP LOCKED for queue-like workloads. See Locks and Mutexes for coordination.
• Monitor pg_stat_database.deadlocks to track deadlock frequency

Common Mistakes

• Locking rows in different orders in different parts of the application
• Using SELECT FOR UPDATE on unnecessary rows, increasing lock scope
• Not retrying after deadlock errors, causing user-facing failures

Performance Tips

1. Use pgbench for deadlock reproduction. Simulate concurrent access patterns:

pgbench -i -s 10 mydb
pgbench -c 20 -j 4 -T 60 -f deadlock_test.sql mydb

2. Monitor pg_locks count. A high number of locks indicates contention:

SELECT count(*) AS total_locks, count(*) FILTER (WHERE NOT granted) AS waiting_locks
FROM pg_locks;

3. Use idle_in_transaction_session_timeout to prevent stuck transactions. Transactions that are idle but not committed hold locks indefinitely:

ALTER SYSTEM SET idle_in_transaction_session_timeout = '300s';

4. Batch FOR UPDATE with SKIP LOCKED for queue throughput. Process multiple jobs per transaction to reduce round trips:

BEGIN;
SELECT id FROM jobs WHERE status = 'pending' FOR UPDATE SKIP LOCKED LIMIT 50;
UPDATE jobs SET status = 'processing' WHERE id IN (...);
COMMIT;

5. Use lock_timeout combined with retry for graceful degradation. Set a short lock timeout and retry with backoff:

SET lock_timeout = '2s';
-- If lock acquisition fails (55P03), retry with backoff