On-Call at 3 AM: A Database Deadlock and What It Taught Me About Idempotency

3:07 AM, my phone buzzing nonstop. The alert: payment-service p99 latency > 15s.

What we saw: error rate from 0.2% to 18%

Payment transactions were timing out in bulk. The Grafana dashboard was solid red: error rate jumped from 0.2% to 18% in three minutes.

It got worse. The mobile client retries on its own after 3 seconds if it hasn’t received a response. So traffic doubled right when the system was at its weakest.

The first thing I did wasn’t fix code. It was confirm the blast radius: how many users affected, every region or just one, and most important of all, are we losing real customer money right now?

Those three questions have to be answered in the first 5 minutes. Only then do you know whether to roll back immediately or whether there’s time to dig in.

Tracing it: two transactions waiting on each other

Skimming the APM logs, I noticed something odd: lots of requests attached to connections sitting on a PostgreSQL lock, waiting for a long time. Joining pg_stat_activity with pg_locks gave a clearer picture:

SELECT
  blocked_locks.pid AS blocked_pid,
  blocking_locks.pid AS blocking_pid,
  blocked_activity.query AS blocked_query,
  blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
  AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

The result: two transactions waiting on each other, textbook style. A deadlock.

Why it deadlocked: two transactions locking in opposite order

Two transactions were updating account balances at the same time, but they took their locks in opposite order:

• Transaction A: locks account_1, then waits for the lock on account_2
• Transaction B: locks account_2, then waits for the lock on account_1

PostgreSQL sees the wait cycle and kills one of the two transactions to break the deadlock. That’s the database doing its job, not a bug.

The mistake was in the application layer. The killed transaction returned an error response. The client got no ACK in time, so it retried the exact same payment request. That request carried no unique identity, so the server treated it as a brand-new transaction. Some users got double-charged.

Here’s the sequence:

sequenceDiagram
  participant C as Client (mobile app)
  participant S as payment-service
  participant PG as PostgreSQL
  participant A as Transaction A
  participant B as Transaction B

  C->>S: payment request
  S->>PG: open Transaction A, lock account_1
  PG->>A: hold lock on account_1
  S->>PG: open Transaction B, lock account_2
  PG->>B: hold lock on account_2
  A->>PG: request lock on account_2 (held by B)
  B->>PG: request lock on account_1 (held by A)
  Note over PG: circular wait, deadlock
  PG->>A: kill one transaction to break the cycle
  PG-->>S: error response
  S-->>C: no ACK within 3 seconds
  C->>S: retry the identical payment request
  Note over C,S: the request carries no unique identity
  S->>PG: processed as a brand-new transaction
  Note over PG: double-charge

This one is hard to catch because the deadlock only happens when two transfers go in opposite directions between exactly the same two accounts, at nearly the same moment. Low probability. But not zero, and with enough traffic it means a few times a day at peak hours.

The fix: an Idempotency-Key on every request that can be retried

Every request that might be retried, such as payments and order creation, now has to carry a unique Idempotency-Key generated by the client. Usually a UUID v4 generated the moment the user taps the pay button, and kept the same across every retry. The server stores the result keyed by it:

func ProcessPayment(ctx context.Context, db *sql.DB, key string, req PaymentRequest) (*PaymentResult, error) {
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback()

	var existing PaymentResult
	err = tx.QueryRowContext(ctx,
		`SELECT result FROM idempotency_records WHERE key = $1 FOR UPDATE`, key,
	).Scan(&existing.Result)

	if err == nil {
		// Key already exists and was fully processed → return the old response, don't process again
		return &existing, nil
	}
	if err != sql.ErrNoRows {
		return nil, err
	}

	result, err := executeTransfer(ctx, tx, req)
	if err != nil {
		return nil, err
	}

	if _, err := tx.ExecContext(ctx,
		`INSERT INTO idempotency_records (key, result, created_at) VALUES ($1, $2, now())`,
		key, result,
	); err != nil {
		return nil, err
	}

	return result, tx.Commit()
}

Three steps:

1. A request comes in. The server checks whether the key is already in the idempotency_records table, using FOR UPDATE to avoid a race when two requests with the same key arrive at almost the same time.
2. If the key exists and was fully processed, return the stored response and don’t run the business logic again.
3. If the key is new, process the transaction, then save the result against that key in the same DB transaction as the main business operation. This is the easiest place to get wrong: insert the idempotency record in a separate transaction and a failure midway can leave you with a transfer that succeeded and no record of it.

Fixing the root cause too: ordering the locks

The Idempotency-Key only contains the damage. The deadlock is still there and needs its own fix.

The simple approach: sort lock order by account_id ascending before opening the transaction, regardless of which direction the money actually moves:

func lockOrder(accountA, accountB string) (first, second string) {
	if accountA < accountB {
		return accountA, accountB
	}
	return accountB, accountA
}

When every transaction locks in the same fixed order, two opposing transfers can no longer wait on each other in a cycle. The deadlock is ruled out by design instead of being handled each time it blows up.

What happened after

After shipping both changes, the double-charge rate stayed at 0 for more than 6 months in production. p99 latency settled around 200ms, even during traffic spikes at peak hours.

Deadlock alerts from the database also went to 0. Before, they showed up a few times a week. Not every one became a double-charge, but the risk was always there.

One more thing that’s easy to forget: go through the most important side-effecting APIs in your system (payments, order creation, confirmation emails) and check which ones still have no Idempotency-Key. That list is usually longer than you’d expect.