MySQL 'Deadlock Found When Trying to Get Lock': Fix

Your app logs MySQL error 1213 and some requests fail at random. Find the two queries that collide, shorten the lock window, and add a safe retry.

MySQL error 1213 "Deadlock found when trying to get lock; try restarting transaction" means two transactions each hold a row lock the other one needs, so MySQL rolled one back. Read the last deadlock with SHOW ENGINE INNODB STATUS, find the two statements involved, make them touch rows in the same order with a proper index, keep the transaction short, and retry the transaction when error 1213 still happens.

The error is not a sign that MySQL is broken. It is MySQL doing its job: it picks a victim, rolls it back, and lets the other transaction finish. The failed request is the part you see. It tends to appear under load, in a few endpoints, and almost never on your laptop. This guide shows how to find the cause on a live database and how to stop it from hurting users.

Step 1: Confirm It Is a Deadlock, Not a Lock Wait

Two errors look alike. They need different fixes.

  • ERROR 1213 (40001): Deadlock found when trying to get lock is a deadlock. MySQL detected a cycle and rolled one side back immediately.
  • ERROR 1205 (HY000): Lock wait timeout exceeded is a slow wait. One transaction held a lock for too long, and no cycle was involved.

In Laravel the 1213 error arrives as an exception with SQLSTATE 40001. In Django it is OperationalError: (1213, ...). In Node with mysql2 or Sequelize it has the code ER_LOCK_DEADLOCK. If your logs say 1205 instead, the fix is to find the long-running transaction, not the lock order.

Step 2: Read the Last Deadlock

InnoDB remembers the most recent deadlock. Log in as an admin and run:

SHOW ENGINE INNODB STATUS\G

Find the section called LATEST DETECTED DEADLOCK. It lists both transactions, the statement each one was running, the lock each one holds, the lock each one waits for, and which one was chosen as the victim. The MySQL reference manual has a worked example of how to read this output.

Look for three things:

  1. The table and index named in each lock. A lock on PRIMARY is a row lock. A lock on a secondary index means your query scanned that index.
  2. The two SQL statements. These are the queries to change.
  3. How many rows each transaction locked. A large number means a missing or weak index.

Step 3: Log Every Deadlock, Not Just the Last One

SHOW ENGINE INNODB STATUS keeps only one deadlock. The next one overwrites it. To keep a history, turn on logging to the MySQL error log:

SET GLOBAL innodb_print_all_deadlocks = ON;

This setting resets on restart. Add innodb_print_all_deadlocks = 1 under [mysqld] in your config file to keep it. Guides such as this walkthrough on analyzing deadlocks recommend the same step, because a one-off deadlock tells you little. After a day you will see the same two or three statement pairs repeat. Those pairs are your real list of fixes.

Step 4: Fix the Common Causes

Most deadlocks in small apps come from a short list of patterns.

Missing index on the filter column. An UPDATE ... WHERE status = 'pending' with no index scans many rows and locks all of them. Two such queries overlap and deadlock. Add an index so each query locks only the rows it needs. If you also have slow queries on the same table, our Postgres guide on slow queries applies the same idea of checking the query plan first.

Rows touched in different orders. Transaction A updates order 1 then order 2. Transaction B updates order 2 then order 1. Each holds one lock and waits for the other. Fix it by always sorting ids before you update them:

START TRANSACTION;
SELECT id FROM orders WHERE id IN (2, 1) ORDER BY id FOR UPDATE;
UPDATE orders SET status = 'paid' WHERE id IN (2, 1);
COMMIT;

Long transactions. A transaction that calls an external API, sends an email, or waits on user input holds its locks the whole time. Do the slow work before START TRANSACTION or after COMMIT. A common Stripe example is to call Stripe first, then open the transaction to save the result.

Many workers on a database queue. Reports on Laracasts describe deadlocks when several workers delete and reserve rows in the jobs table. Move the queue to Redis, or follow the advice in Laravel queue jobs not processing in production.

Hot rows. A single counter row, such as UPDATE stats SET hits = hits + 1, updated by every request, makes every request queue behind the last. Spread the writes over several rows or batch them.

Step 5: Keep Transactions Short and Narrow

Even with the right order, a wide transaction raises the odds of a collision. A few rules help:

  • Open the transaction as late as possible and commit as early as possible.
  • Select only the rows you need, and use FOR UPDATE only when you really need the lock.
  • Prefer several small transactions to one huge batch. The Drupal discussions in the search results for this error came to the same fix: shorten the time a lock is held.

Step 6: Retry the Transaction

The MySQL error message itself says "try restarting transaction". Some deadlocks cannot be designed away, so your code must retry. This is safe because MySQL rolled the whole transaction back.

In Laravel, pass the number of attempts as the second argument. Laravel retries when it sees a deadlock:

use Illuminate\Support\Facades\DB;

DB::transaction(function () use ($orderId) {
    $order = Order::lockForUpdate()->findOrFail($orderId);
    $order->update(['status' => 'paid']);
}, 5);

In Node.js with mysql2, check the error code and retry a few times with a short random pause:

async function withRetry(fn, attempts = 4) {
  for (let i = 1; i <= attempts; i++) {
    try {
      return await fn();
    } catch (err) {
      const retryable = err.code === 'ER_LOCK_DEADLOCK' && i < attempts;
      if (!retryable) throw err;
      await new Promise((r) => setTimeout(r, 50 * i + Math.random() * 50));
    }
  }
}

Make the whole transaction the unit you retry, including the connection and BEGIN. Do not retry only the failed statement. Also release the connection in a finally block. A Sequelize issue on MariaDB shows a deadlock that left a connection unreleased and later caused connection timeouts. If that happens, see MySQL too many connections.

Step 7: Watch for It After Fixing

Count how often 1213 appears per day and which endpoint it hits. A rare deadlock that retries cleanly is fine. A rising count after a deploy means a new query pattern, and users are getting failed requests or slow pages while you retry.

How Relia Helps

Deadlocks show up to users as a failed checkout or a save that errors once and works on the second try. Relia watches the users, APIs and database of a live product and joins related signals, such as a deploy, a slow query and failing requests, into one incident explained in plain words, with who was affected. For Node.js apps the setup is npx relia-sdk@latest init. Relia has a Python package and a Composer package for PHP and Laravel. There is a Free plan and a live demo at app.tryrelia.com/demo.

FAQ

What does "Deadlock found when trying to get lock; try restarting transaction" mean?

Two transactions are waiting on each other's row locks. MySQL rolls one back so the other can finish. Rerun the rolled-back transaction.

Can I stop MySQL deadlocks completely?

No. You can make them rare with indexes, a fixed lock order and short transactions. You still need retry code for the rest, as the MySQL manual notes.

What is the difference between a deadlock and a lock wait timeout?

A deadlock is a cycle that MySQL detects at once (error 1213). A lock wait timeout (error 1205) means one transaction waited too long for a lock held by a slow transaction.

How do I see every deadlock, not just the last one?

Set innodb_print_all_deadlocks to ON. Each deadlock is then written to the MySQL error log.

Why do I get a deadlock on a query with no explicit lock?

Plain UPDATE and DELETE statements take row locks on their own. A node-mysql2 issue reports exactly this under heavy traffic. Check indexes and lock order on those statements.

All guides