MySQL

MySQL Deadlock Found When Trying to Get Lock

A deadlock in MySQL happens when two transactions each hold a lock the other one needs, and neither can proceed — MySQL detects this automatically and kills one of the two transactions to break the standoff, which is why the error is expected behavior, not a database malfunction.

The Problem

A query or transaction fails with:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

This often happens intermittently, under concurrent load, and can be hard to reproduce reliably on demand, since it depends on the precise timing of two or more transactions running at the same moment.

Classic deadlock: opposite lock order Transaction A Transaction B Row 1 (locked) Row 2 (locked)

Why It Happens

Deadlocks occur when two transactions acquire locks in opposite order, each waiting for a resource the other already holds. The most common causes are:

  • Two transactions updating the same set of rows in different orders — for example, Transaction A updates row 1 then row 2, while Transaction B updates row 2 then row 1, and both happen to run at overlapping times
  • A transaction that reads a row with a locking read (SELECT ... FOR UPDATE) and then, later in the same transaction, tries to lock a different row that another transaction already holds, while that other transaction simultaneously needs the first row
  • Long-running transactions that hold locks for an extended period, increasing the window during which another transaction can collide with them
  • Missing or overly broad indexes causing MySQL to lock more rows than logically necessary for a given query, increasing the chance of two transactions' lock sets overlapping

The Fix

The most reliable fix at the application level is retry logic — since deadlocks are, by design, resolved by MySQL automatically killing one transaction, the correct response is to catch that specific error and retry the transaction from the start:

let retries = 3;
while (retries > 0) {
  try {
    await runTransaction();
    break;
  } catch (err) {
    if (err.errno === 1213 && retries > 1) {
      retries--;
      continue;
    }
    throw err;
  }
}

This pattern is standard practice in high-concurrency applications — deadlocks are expected to happen occasionally under load, and retrying the losing transaction is the normal way to handle them, not a workaround for a bug.

To reduce how often deadlocks happen in the first place, ensure your application always acquires locks on multiple rows in a consistent order across all code paths. If one part of your code updates a user's balance before their order, make sure every other part does the same, rather than sometimes reversing the order:

-- Consistent order: always lock the lower ID first
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

Keep transactions as short as possible, committing as soon as the necessary work is done, rather than holding locks open while performing unrelated work (like calling an external API) in the middle of a transaction.

Still Not Working?

If deadlocks are frequent and hard to pin down, enable MySQL's detailed deadlock logging to see exactly which queries and rows were involved in the most recent deadlock:

SHOW ENGINE INNODB STATUS;

Look for the "LATEST DETECTED DEADLOCK" section in the output, which lists both transactions involved, the exact queries they were running, and which locks each was waiting for — this is usually enough to identify the specific code path causing the conflicting lock order.

It's also worth reviewing your transaction isolation level, since some levels acquire more locks than strictly necessary for a given operation, increasing the surface area for deadlocks to occur. The default REPEATABLE READ in InnoDB uses gap locks in certain situations specifically to prevent phantom reads, which can sometimes lock more of the index range than an application developer might expect from reading the query alone:

SELECT @@transaction_isolation;

Switching to READ COMMITTED for specific transactions that don't require the stronger guarantees of REPEATABLE READ can reduce gap locking and, as a side effect, reduce deadlock frequency — though this tradeoff should be made deliberately, with an understanding of what consistency guarantee you're giving up, not applied blindly as a quick fix.

If deadlocks cluster around a specific batch job or scheduled task rather than normal user traffic, consider whether that job can be restructured to process rows in smaller batches with shorter transactions, or scheduled to run during lower-traffic periods, reducing the window during which it overlaps with regular application transactions competing for the same rows.

You might also like