Postgres Deadlocks: Two Transactions, Two Rows, Zero Progress

RedBite runs a wallet feature: customers can hold store credit and move it between a personal balance and a gift-card balance, both rows in the same wallets table. Two support agents, working two different tickets at the same moment, both run a transfer:

• Agent 1 refunds a customer: moves credit from wallet W-100 to wallet W-200.
• Agent 2, on an unrelated ticket for a different customer pair, moves credit from wallet W-200 to wallet W-100 — same two rows, opposite direction.

Both transactions commit fine on their own, thousands of times a day. Today, both run in the same few milliseconds, and Postgres kills one of them with:

ERROR:  deadlock detected
DETAIL:  Process 8821 waits for ShareLock on transaction 4471; blocked by
process 8834.
Process 8834 waits for ShareLock on transaction 4466; blocked by process
8821.

Nobody wrote a bug here in the usual sense — both transfer functions are individually correct. The deadlock comes purely from the order each transaction locks the two rows in.

Reproducing it

Each transfer does the obvious thing: lock the source row, lock the destination row, move the amount:

async function transfer(fromWallet, toWallet, amount) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query(
      'SELECT balance FROM wallets WHERE id = $1 FOR UPDATE',
      [fromWallet]
    );
    await client.query(
      'SELECT balance FROM wallets WHERE id = $1 FOR UPDATE',
      [toWallet]
    );
    await client.query(
      'UPDATE wallets SET balance = balance - $1 WHERE id = $2',
      [amount, fromWallet]
    );
    await client.query(
      'UPDATE wallets SET balance = balance + $1 WHERE id = $2',
      [amount, toWallet]
    );
    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Agent 1 calls transfer('W-100', 'W-200', 500). Agent 2 calls transfer('W-200', 'W-100', 300). Walk through what each transaction locks first:

1. Agent 1’s transaction: SELECT ... FOR UPDATE on W-100 — locked. Next line wants to lock W-200.
2. Agent 2’s transaction, running at the same moment: SELECT ... FOR UPDATE on W-200 — locked. Next line wants to lock W-100.
3. Agent 1’s transaction tries to lock W-200 — already held by Agent 2’s transaction. It waits.
4. Agent 2’s transaction tries to lock W-100 — already held by Agent 1’s transaction. It also waits.

Each transaction is holding the exact row the other one needs next, and waiting for the exact row the other one is holding. Neither can proceed, and neither will ever release its lock, because releasing happens at COMMIT — which can’t happen until the blocked SELECT ... FOR UPDATE returns. This is a real deadlock, not a slow query: left alone, both transactions would wait forever.

Postgres doesn’t actually let this hang forever — its deadlock detector runs periodically (deadlock_timeout, default 1 second), finds the cycle, and aborts one of the two transactions with the deadlock detected error so the other can proceed. That’s Postgres saving you from a permanent hang; it is not Postgres fixing your bug. The aborted transaction still needs to be retried, and the next unlucky pair of concurrent transfers in opposite directions will deadlock again.

Fix: always lock rows in the same order

The deadlock exists only because Agent 1’s transaction locked W-100 then W-200, while Agent 2’s locked W-200 then W-100 — a cycle needs two transactions disagreeing on order. If every transaction that touches multiple wallet rows locks them in the same order regardless of which one is logically the “source,” the cycle can’t form.

The simplest consistent order: sort the two IDs before locking, lock low-to-high every time, and only then apply the actual debit/credit:

async function transfer(fromWallet, toWallet, amount) {
  const [first, second] =
    fromWallet < toWallet ? [fromWallet, toWallet] : [toWallet, fromWallet];

  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query(
      'SELECT balance FROM wallets WHERE id = $1 FOR UPDATE',
      [first]
    );
    await client.query(
      'SELECT balance FROM wallets WHERE id = $1 FOR UPDATE',
      [second]
    );
    await client.query(
      'UPDATE wallets SET balance = balance - $1 WHERE id = $2',
      [amount, fromWallet]
    );
    await client.query(
      'UPDATE wallets SET balance = balance + $1 WHERE id = $2',
      [amount, toWallet]
    );
    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Re-run the same scenario. Agent 1’s transfer (W-100 to W-200) sorts to lock order [W-100, W-200]. Agent 2’s transfer (W-200 to W-100) sorts to the exact same lock order — [W-100, W-200], because the sort doesn’t care which one is logically “from.” Whichever transaction gets there first locks W-100, then W-200, and finishes; the second transaction simply queues behind it waiting for W-100 — a normal, bounded wait, not a cycle. No deadlock is possible, because both transactions now agree on which row to grab first.

This generalizes beyond two rows. Any time a transaction needs locks on more than one row and the set of rows overlaps with another transaction’s, sort every row’s key the same way before locking, every time, everywhere in the codebase that touches those rows. One code path that forgets to sort reintroduces the cycle for everyone.

Don’t skip retry logic — the fix reduces deadlocks, it doesn’t eliminate the need to handle one

Lock ordering removes deadlocks between calls to this function. It doesn’t protect against a deadlock between this transaction and some entirely different code path that also touches wallet rows without going through the same ordering rule — and Postgres’s detector can still abort a transaction for other lock conflicts. Any code that opens a transaction touching more than one row should still catch Postgres error code 40P01 (deadlock detected) and retry once:

async function transferWithRetry(fromWallet, toWallet, amount, attempts = 2) {
  try {
    await transfer(fromWallet, toWallet, amount);
  } catch (err) {
    if (err.code === '40P01' && attempts > 0) {
      return transferWithRetry(fromWallet, toWallet, amount, attempts - 1);
    }
    throw err;
  }
}

Lock ordering is the structural fix — it’s why deadlocks on this path should become rare. The retry is the safety net for the case it doesn’t rule out. Ship both; neither one alone is the full story.

Hit a deadlock lock ordering didn’t fix? Drop the DETAIL line in #edge-cases on Discord.