A funds-transfer function. Locks the source account, then the target, then debits and credits. Looks safe in isolation. Under concurrent reverse transfers (A→B and B→A at the same time), deadlock.

The vulnerable diff

tswallet/transfer.ts

// wallet/transfer.ts
export async function transfer(from: Account, to: Account, amount: number) {
  await db.$transaction(async (tx) => {
    // BUG: locks acquired in argument order, not global order
    await tx.accounts.update({ where: { id: from.id }, data: { balance: { decrement: amount } }});
    await tx.accounts.update({ where: { id: to.id   }, data: { balance: { increment: amount } }});
  });
}

What is wrong

Postgres (and most relational databases) automatically detect deadlocks and abort one of the conflicting transactions. The abort manifests as a 'deadlock detected' error in the application. The fix is the standard one for any system with multiple locks: acquire them in a globally consistent order, regardless of the application semantics. Sorting by primary key is the canonical choice — every transaction that touches the same set of rows acquires the same locks in the same order, no cycle possible.

The attack

Reproducer:

textexploit

# Two concurrent transfers in opposite directions:
T1: transfer(A, B, 100)   T2: transfer(B, A, 100)
T1 locks A.                T2 locks B.
T1 tries to lock B → wait. T2 tries to lock A → wait.
                          Deadlock. Postgres aborts one.

Application sees: "deadlock detected" error.
Customer sees: payment failed.

Banks and payments systems have stricter ordering policies for exactly this reason; ordinary CRUD codebases tend to learn it the hard way.

Mesrai's review comment

textreview-comment.txt

mesraipilot · Bot · reviewed 1 min ago

[mesrai] [code-review] [Logic] [Deadlock] [high]

`transfer(from, to)` locks rows in argument order. Concurrent
`transfer(A, B)` and `transfer(B, A)` deadlock — Postgres aborts
one, application returns an error.

Fix: acquire locks in a globally consistent order. Sort by account
id before locking:

  const [first, second] = [from, to].sort((a, b) =>
    a.id.localeCompare(b.id)
  );
  // Apply the SELECT FOR UPDATE / first writes in sorted order:
  await tx.accounts.update({
    where: { id: first.id }, data: ...
  });
  await tx.accounts.update({
    where: { id: second.id }, data: ...
  });
  // Then apply the actual debit/credit (which know from/to):
  ...

The deadlock-avoidance rule generalizes: any function that needs
multiple row locks should sort the lock targets before acquiring.

The fix

tswallet/transfer.ts (fixed)

// wallet/transfer.ts — fixed
export async function transfer(from: Account, to: Account, amount: number) {
  // Lock in id-sorted order to avoid cycles
  const ids = [from.id, to.id].sort();

  await db.$transaction(async (tx) => {
    // Touch the sorted-first row first — acquires lock in stable order
    await tx.accounts.findUnique({ where: { id: ids[0] }, /* FOR UPDATE */ });
    await tx.accounts.findUnique({ where: { id: ids[1] }, /* FOR UPDATE */ });

    // Now apply the actual transfer (semantics know from/to)
    await tx.accounts.update({ where: { id: from.id }, data: { balance: { decrement: amount } }});
    await tx.accounts.update({ where: { id: to.id   }, data: { balance: { increment: amount } }});
  });
}

Sort the lock targets by a globally consistent key (id is the conventional choice). Touch the sorted-first row first to acquire its lock, then the second. Now `transfer(A, B)` and `transfer(B, A)` both lock A first — no cycle. The application semantics (who gets debited vs credited) are independent of the lock-acquisition order.

Why human review missed it

Deadlock from inconsistent lock order is one of those bugs that looks fine until concurrency makes it fail. The function reads correctly: 'lock the source, lock the target, transfer'. The error surfaces only when two concurrent transfers move funds in opposite directions. Mesrai catches the pattern by flagging any multi-row transaction where the lock order depends on input order rather than a stable key.

Related rules + further reading

Mesrai rule pack: logic/multi-row-lock-order — flags transactions with multi-row locks not acquired in a stable order.

Classic distributed systems: dining philosophers, two-phase locking.

Postgres docs: Concurrency Control — Deadlocks.

Takeaway

Sort lock targets by a stable key before acquiring. One sort, deadlock cycle broken. Mesrai catches the unsorted shape on every multi-row transaction.

Deadlock in Two-Lock Acquisition: The Order Mesrai Always Recommends