Database Deadlock: When Two Transactions Wait on Each Other Forever

7 min readdatabasetransaction

Have you ever seen an error like this appear in your application logs during peak traffic?

ERROR: deadlock detected
Detail: Process 1234 waits for RowShareLock on transaction 5678;
        blocked by process 5678.
        Process 5678 waits for RowShareLock on transaction 1234;
        blocked by process 1234.
Hint: See server log for query details.

What makes it annoying: this error often appears without any code change. The code has been running smoothly for a long time, then suddenly some requests fail during busy hours. This article explains what is really happening, how the database handles it, and how to prevent it.

Background you need first

To follow this article, you only need two basic concepts:

• A transaction is a series of SQL statements that finish together (COMMIT) or are canceled together (ROLLBACK).
• A row lock: when a transaction changes or locks one row, that row is frozen for other transactions until the owning transaction finishes. This lock works per row, not once for all rows the transaction will touch.

This per-row nature is the root of the problem. One transaction can hold locks on several rows step by step, one after another, as its statements execute.

What is a deadlock?

A deadlock happens when two transactions (or more) wait for locks held by each other, so none of them can move forward.

The easiest analogy: a four-way gridlock intersection where four cars from four directions enter at the same time and each tries to go first. Car A waits for car B to pass, car B waits for car C, and so on. Nobody moves, and everyone waits forever.

Unlike a normal queue, a deadlock cannot be solved by waiting. One side has to give in.

Rebuilding the case: two transfers in opposite directions

Let us build a deadlock from zero. There are two accounts in the accounts table: ACC-001 and ACC-002.

• Transaction 1: sends 100,000 IDR from ACC-001 to ACC-002. Its statement order: reduce the balance of ACC-001 first, then add to the balance of ACC-002.
• Transaction 2: sends 50,000 IDR from ACC-002 to ACC-001. Its statement order: reduce the balance of ACC-002 first, then add to the balance of ACC-001.

Both transactions are valid for the business and valid in SQL. The problem is only the order of the rows they lock:

-- Transaction 1
BEGIN;
UPDATE accounts SET balance = balance - 100000 WHERE account_id = 'ACC-001'; -- lock ACC-001
UPDATE accounts SET balance = balance + 100000 WHERE account_id = 'ACC-002'; -- needs lock ACC-002

-- Transaction 2 (runs at almost the same time)
BEGIN;
UPDATE accounts SET balance = balance - 50000 WHERE account_id = 'ACC-002';  -- lock ACC-002
UPDATE accounts SET balance = balance + 50000 WHERE account_id = 'ACC-001';  -- needs lock ACC-001

Here is the timeline of events, step by step:

Loading diagram...

Reading the timeline above:

1. Transaction 1 locks row ACC-001 when its first UPDATE executes.
2. Transaction 2, running at almost the same time, locks row ACC-002.
3. Transaction 1 continues to its second statement and wants to lock ACC-002. That row is still held by Transaction 2, so Transaction 1 queues.
4. Transaction 2 continues to its second statement and wants to lock ACC-001. That row is held by Transaction 1, which happens to be queueing while waiting for Transaction 2.
5. Both transactions are now waiting on each other. Neither can move forward until the other releases its lock, and the locks will not be released before each one finishes. This is the waiting circle called a deadlock.

How does the database handle it?

Waiting forever is clearly not an option. Modern relational databases have deadlock detection that runs automatically:

1. The database monitors the waiting relations between transactions (the wait-for graph): T1 waits for T2, T2 waits for T1.
2. When the waiting relations form a circle, the deadlock is detected.
3. The database picks one transaction as the victim, automatically performs a ROLLBACK on that transaction, and releases all of its locks.
4. The rolled-back transaction receives the deadlock detected error. The other transaction is now free to continue and finish successfully.

Because the rollback is clean (all victim changes are canceled since BEGIN), the transaction that hit the deadlock can be retried right away by the application. The second attempt usually succeeds, because the other transaction has already finished and its locks are released.

Mental Model

A deadlock is a condition the database handles by itself, not a permanent hang. The practical impact on the application: some requests fail with an error that can be retried. A new problem only appears when the application does not handle that error and treats it as a permanent failure.

Solutions: avoiding deadlocks

Deadlocks can be prevented because their cause is always one pattern: two transactions locking the same rows in a different order. The solutions point in that direction too.

1. Lock all rows at the start with a consistent order

Instead of locking rows one by one as the statements execute, lock all rows you will touch in a single query at the start, with a fixed order:

BEGIN;

-- Lock both accounts at once, always in account_id order
SELECT balance
FROM accounts
WHERE account_id IN ('ACC-001', 'ACC-002')
ORDER BY account_id
FOR UPDATE;

UPDATE accounts SET balance = balance - 100000 WHERE account_id = 'ACC-001';
UPDATE accounts SET balance = balance + 100000 WHERE account_id = 'ACC-002';

COMMIT;

With this pattern, both transactions lock ACC-001 first. The one that arrives second queues at ACC-001 from the start, and never gets to hold ACC-002 in the reversed pattern. The waiting circle cannot form, because the lock order taken by every transaction is always the same.

2. Keep transactions short

The longer a transaction holds its locks, the bigger the time window for other transactions to enter and form a conflicting waiting pattern. Do not keep a transaction open while waiting for a slow process in the application (an HTTP call to another service, file processing), and do not put unnecessary operations inside the transaction block.

3. Catch the deadlock error and retry

For systems with high concurrency, deadlocks can still happen even with a clean design. Handle the error explicitly and repeat the transaction:

async function executeWithRetry(fn, maxAttempts = 3) {
  let lastError;

  for (let attempt = 1; attempt <= maxAttempts; attempt++) {
    try {
      return await fn();
    } catch (error) {
      // Only retry deadlock errors, let other errors bubble up
      if (error.code === "40P01") {
        lastError = error;
        // Simple backoff before the next attempt
        await new Promise((resolve) => setTimeout(resolve, 50 * attempt));
        continue;
      }
      throw error;
    }
  }

  throw lastError;
}

The error code 40P01 is the SQLSTATE code for a deadlock in PostgreSQL. The rollback from a deadlock is clean, so the retry starts from the beginning with consistent data.

Another case: batch workers with different orders

Deadlocks do not only happen in money transfer cases. The same pattern often shows up in batch jobs, and this is the case you will meet most often at work.

Imagine two workers processing invoice updates from a queue:

• Job A updates invoice ID 1, then 2, then 3.
• Job B updates invoice ID 3, then 2, then 1.

No money moves, and there is no bug in either worker's code, but they can still wait on each other: Job A holds the lock on invoice 1 while wanting to update 3, and Job B holds the lock on invoice 3 while wanting to update 1.

The cause is identical to the transfer case: the same dataset, a different lock order. The solution is the same too:

• Sort the rows to update before processing, for example with ORDER BY id.
• Process in small chunks and commit per chunk, so one worker does not hold many locks for a long time.

Summary

• A deadlock happens when two transactions wait for locks held by each other. Nobody can move forward by waiting.
• Its typical cause: transactions locking rows step by step in a different order, like two transfers in opposite directions between ACC-001 and ACC-002.
• Modern databases detect deadlocks automatically, pick one victim, and roll it back with the deadlock detected error. The rolled-back transaction can be retried.
• The main prevention: lock all rows at the start in one SELECT ... FOR UPDATE with an ORDER BY, keep transactions short, and catch the deadlock error to retry it.
• The same pattern appears in batch workers that update the same dataset in a different order. The solution stays the same: a consistent lock order and small chunks.

References

• PostgreSQL Documentation: Deadlocks (PostgreSQL Official Docs)
• MySQL Documentation: InnoDB Deadlock Detection and Rollback (MySQL Official Docs)