MySQL error 1213 deadlock found: retry it, you cannot prevent it

Checkout starts failing at about the time the evening traffic peak hits. Not all of it, maybe one request in two hundred, and the exception is always the same: Deadlock found when trying to get lock; try restarting transaction. Somebody opens a ticket titled "fix the deadlocks in the database" and assigns it to whoever touched the orders table last.

That ticket cannot be completed as written, and it is worth saying so out loud early. A deadlock is not a database malfunction. It is InnoDB noticing that two of your transactions are waiting on each other in a cycle, deciding one of them has to die so the other can proceed, and telling you which one it killed. In a system where two concurrent transactions can touch the same rows in different orders, deadlocks are a permanent possibility. The MySQL manual is blunt about it: even with correct application logic, you must still handle a transaction that has to be retried.

So the job is not "eliminate deadlocks". The job is two things: make them rare enough to ignore, and make the ones that still happen invisible to the customer. Most teams do only the first, badly, and skip the second entirely.

The exact error: "Deadlock found when trying to get lock; try restarting transaction"

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

You will see it in different clothes depending on your stack, but the numbers underneath never change:

Layer	What you see
MySQL error code	1213 (ER_LOCK_DEADLOCK)
SQLSTATE	40001, the standard "serialization failure" class
Python (PyMySQL / mysqlclient)	OperationalError: (1213, 'Deadlock found when trying to get lock; try restarting transaction')
SQLAlchemy	sqlalchemy.exc.OperationalError wrapping the same 1213
JDBC	SQLTransactionRollbackException, SQLState 40001
Node (mysql2)	err.errno === 1213, err.code === 'ER_LOCK_DEADLOCK'

Catch on the error number or on SQLSTATE 40001. Never match on the message string; it is localised in some builds and it will silently stop matching after an upgrade.

Why this happens, properly

InnoDB locks rows, not statements. When transaction A holds a lock that transaction B wants, B waits. InnoDB maintains a waits-for graph of exactly these relationships, and when that graph contains a cycle, no amount of waiting will resolve it. Rather than let both sessions hang until innodb_lock_wait_timeout expires, InnoDB breaks the cycle immediately by rolling back the transaction it judges cheapest to undo, usually the one that has modified the fewest rows. That victim gets 1213. The other transaction proceeds as if nothing happened.

The classic same-table case is easy to picture: A updates row 1 then row 2, B updates row 2 then row 1. The one that actually bites in production is the cross-table variant, because the two halves of the cycle live in different files, written by different people, months apart:

Two mechanisms make this worse than the textbook picture suggests. First, under the default REPEATABLE READ isolation level InnoDB takes next-key locks, which cover the gap before an index record as well as the record itself. Two transactions can deadlock while touching entirely different rows, because they are contending for the same gap. Second, an UPDATE with no usable index has to lock every row it examines, not just the rows it changes. A missing index does not merely make a query slow, it makes its lock footprint enormous.

Step 1: Capture the actual lock cycle, do not guess

InnoDB keeps only the most recent deadlock in memory:

SHOW ENGINE INNODB STATUS\G

Scroll to the LATEST DETECTED DEADLOCK section. That is fine for a reproduction on your laptop and useless in production, where the deadlock you care about was overwritten forty seconds ago. Turn on persistent logging instead:

SET GLOBAL innodb_print_all_deadlocks = ON;

and make it survive a restart in my.cnf:

[mysqld]
innodb_print_all_deadlocks = ON

Every deadlock now lands in the MySQL error log. The overhead is a few lines of text per event, so leave it on permanently unless you are deadlocking thousands of times a minute, in which case you have a much bigger conversation to have. The entries look like this, trimmed:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2026-07-14 02:14:07 0x7f2b1c0d8700
*** (1) TRANSACTION:
TRANSACTION 4213877, ACTIVE 0 sec starting index read
LOCK WAIT 4 lock struct(s), 3 row lock(s), undo log entries 1
UPDATE inventory SET on_hand = on_hand - 1 WHERE sku_id = 12
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 42 index PRIMARY of table `shop`.`orders`
trx id 4213877 lock_mode X locks rec but not gap
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 44 index PRIMARY of table `shop`.`inventory`
trx id 4213877 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 4213880, ACTIVE 0 sec starting index read
UPDATE orders SET status = 'paid' WHERE id = 77
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 44 index PRIMARY of table `shop`.`inventory`
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 42 index PRIMARY of table `shop`.`orders`
*** WE ROLL BACK TRANSACTION (2)

Read it in this order: which index and table each side holds, which one each side is waiting for, and whether the lock is on a record, a gap, or both. That tells you the lock order each path used, which is the entire diagnosis. The statement printed is only the last statement of the transaction, not all of it, so you usually have to find the surrounding code yourself.

Step 2: Impose one lock order, everywhere

Pick a canonical order and enforce it in every code path that writes: for example, always orders before order_items before inventory. Write it down. Put it in the pull request template if you have to. A convention nobody can find is not a convention.

Within a single table, sort the keys before you touch them. This is the highest-value two-line change in the whole article, because bulk updates driven by a set or a dictionary iterate in effectively random order:

# Deadlock-prone: the ID order depends on whatever the caller passed in
for sku_id in payload["sku_ids"]:
    cur.execute("UPDATE inventory SET on_hand = on_hand - 1 WHERE sku_id = %s", (sku_id,))

# Safe: every transaction in the system now walks the rows the same way
for sku_id in sorted(payload["sku_ids"]):
    cur.execute("UPDATE inventory SET on_hand = on_hand - 1 WHERE sku_id = %s", (sku_id,))

Step 3: Shrink the window

A deadlock needs two transactions to overlap in time. Every millisecond a transaction holds locks is a millisecond of exposure. So:

• Never make an HTTP call inside a transaction. Not to a payment provider, not to a notification service, not to anything. A Daraja call that normally takes 400ms and occasionally takes 30 seconds will hold row locks for 30 seconds.
• Do your reads and your computation before BEGIN, then open the transaction, write, and commit.
• Split giant batch updates into chunks with a commit between them. One statement updating 500,000 rows is a lock on 500,000 rows.
• Index the columns in your WHERE clauses. An UPDATE that scans locks what it scans.

Step 4: Add the retry, and retry the right thing

When you get 1213, MySQL has already rolled back your entire transaction. Not the statement. Everything since BEGIN, including work you did three statements ago. So the retry has to restart the whole unit of work, which means your transaction body has to be a callable you can invoke again, and it has to be safe to run twice:

import random, time
import pymysql

DEADLOCK = 1213

def run_in_transaction(conn, work, attempts=3):
    for attempt in range(1, attempts + 1):
        try:
            conn.begin()
            result = work(conn)
            conn.commit()
            return result
        except pymysql.err.OperationalError as exc:
            conn.rollback()
            if exc.args[0] != DEADLOCK or attempt == attempts:
                raise
            # full jitter: spread the retries so the same pair does not collide again
            time.sleep(random.uniform(0, 0.05 * (2 ** attempt)))
    raise RuntimeError("unreachable")

Three rules for this wrapper. Retry a small number of times, not indefinitely, because a persistent deadlock storm needs a human. Add jitter, because two transactions that deadlocked together will deadlock again if they both retry after exactly 100ms. And emit a metric on every retry, because "deadlock retries per minute" is the number that tells you whether step 2 worked.

On ERPNext or another Frappe app the same principle applies: the retry belongs around the whole document save, not around one query. Finding which statement holds locks for that long is a job for the MariaDB slow query log, and if the underlying cause is an unindexed UPDATE, see indexes that actually work.

Verification: proving it worked

Three things to check, in order.

1. Count deadlocks over time. With innodb_print_all_deadlocks on, the error log is your source of truth:

grep -c 'LATEST DETECTED DEADLOCK' /var/log/mysql/error.log

Compare the count for the 24 hours before your change against the 24 hours after. On MariaDB 10.5 and later, and on Percona Server, there is also a counter you can scrape:

SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';

If your build returns an empty set, that variable does not exist there and the log count is your metric.

2. Confirm the retry path actually executes. Reproduce a deadlock deliberately in staging: open two mysql sessions, BEGIN in both, update row A then row B in one and the reverse in the other. One session gets 1213 within a second. Then run the same collision through your application and confirm the request still returns 200 and the retry counter increments.

3. Check for the silent failure mode. Search your codebase for places that catch a broad database exception and log it without re-running the transaction. Those are the endpoints that were quietly losing work all along.

What people get wrong

"Raise innodb_lock_wait_timeout." Different error. That setting governs error 1205, Lock wait timeout exceeded; try restarting transaction, which is what happens when a transaction waits too long for a lock without a cycle being present. Raising it does nothing for 1213, because deadlock detection fires immediately and never waits for a timeout. Worse, 1205 has a trap of its own: by default a lock wait timeout rolls back only the current statement, leaving your transaction alive and half-applied. If you treat 1205 like 1213 without checking innodb_rollback_on_timeout, you can commit a partial transaction.

"Turn off deadlock detection." innodb_deadlock_detect=OFF exists, and it does not make deadlocks go away. It makes InnoDB stop looking for them, so instead of one transaction dying in microseconds, both sit there until innodb_lock_wait_timeout expires. It is a genuine tuning option for very high concurrency workloads paired with a very short lock wait timeout, and it is a terrible default for a normal application.

"Just retry the failed query." The transaction is already rolled back. Re-running the single statement that reported the error commits a fragment of the work with none of the earlier statements, which is worse than the failure. Retry from BEGIN.

"Use a table lock or a global mutex so it can never happen." Congratulations, you have converted a rare retry into permanent serialisation of your busiest endpoint. If you genuinely need to serialise a critical section, use a narrow named lock around the smallest possible unit, and know what it costs.

"Switch to READ COMMITTED and it goes away." This one is half right, which is why it spreads. READ COMMITTED removes most gap locking, so it genuinely reduces one whole family of deadlocks. It also changes what your queries see inside a transaction and requires row-based binary logging for safe replication. It is a real lever, not a free one. Change it deliberately, in a maintenance window, after you have read what it does to your reads.

When it is still deadlocking

1. Look for foreign keys. InnoDB takes shared locks on parent rows to check referential integrity. An insert into a child table can therefore wait on a row in a table your statement never mentions. These deadlocks are the hardest to see in the log because one side of the cycle has no matching SQL in your code.
2. Look for INSERT ... ON DUPLICATE KEY UPDATE and REPLACE. Both take locks on unique index gaps and are a well-known deadlock source under concurrency, especially with multiple unique indexes on the same table.
3. Look at what your ORM actually emits. Turn on the general log for one minute in staging and read the real statement order. Bulk save helpers and cascade deletes routinely produce an order nobody intended.
4. Look at background jobs. Most of the cross-table cycles I have chased had a nightly report, a stock sync or a cron-driven cleanup on one side of them. Those jobs are invisible during business-hours testing and land squarely in peak traffic when a schedule drifts.

Fix the lock order where you can, keep transactions short, and accept the residue. A system that retries 1213 cleanly and reports the rate on a dashboard is in far better shape than one that has never seen a deadlock because nobody is looking.