Database & ORM · Developer Error Resolution Lab

Fix PostgreSQL 'ERROR: deadlock detected' in Concurrent Transactions

Prevent transaction circular wait conditions by enforcing consistent table lock ordering and shortening transaction scopes.

Fix Guide:

Triggered Error Message:

ERROR: deadlock detected (SQLSTATE 40P01) Detail: Process 12345 waits for ShareLock on transaction 6789; Process 6789 waits for ShareLock on transaction 12345.

// Rule: Sort primary keys before locking multiple rows:
async function transferFunds(accountA: number, accountB: number, amount: number) {
  // Sort IDs so all concurrent transactions lock resources in identical order:
  const [firstId, secondId] = [accountA, accountB].sort((a, b) => a - b);

  return await prisma.$transaction(async (tx) => {
    // 1. Lock lower ID first
    await tx.$executeRaw`SELECT id FROM accounts WHERE id = ${firstId} FOR UPDATE`;
    // 2. Lock higher ID second
    await tx.$executeRaw`SELECT id FROM accounts WHERE id = ${secondId} FOR UPDATE`;

    // 3. Perform updates safely without deadlocks
    await tx.accounts.update({ where: { id: accountA }, data: { balance: { decrement: amount } } });
    await tx.accounts.update({ where: { id: accountB }, data: { balance: { increment: amount } } });
  });
}

Root Cause & Mechanism Analysis

Why does this error occur?

Two or more concurrent database transactions update the same rows in reverse order (Transaction A locks Row 1 then requests Row 2; Transaction B locks Row 2 then requests Row 1).