A MySQL Deadlock Is a Lock-Order Cycle, Not a Slow Query

MySQL error 1213 with SQLSTATE 40001 means InnoDB detected a transaction deadlock, chose a victim, and rolled that entire transaction back. Recovery therefore has two layers: retry the complete business transaction safely, then remove any repeatable lock-order pattern that makes the collision frequent. Raising innodb_lock_wait_timeout does not repair a detected cycle.

A deadlock can happen on a healthy, well-sized server because two valid transactions acquire overlapping locks in opposite order. One isolated event is not automatically a database outage. Repeated events on the same statements, rows, indexes, or request path are an application-and-schema signal that needs evidence rather than a timeout guess.

Read 1213 as a completed victim decision

InnoDB normally detects a wait-for cycle immediately and rolls back one participant so the other can continue. The client receives a receipt similar to this:

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

Capture the UTC time, application operation, request or job identifier, MySQL account, database, error number, SQLSTATE, retry attempt, and whether the business action had an external side effect. Do not log customer secrets or full sensitive payloads. The transaction that received 1213 no longer owns a partial commit inside InnoDB, but application work performed outside that transaction may still need idempotency protection.

Traffic symptoms can mislead. A web request that times out without 1213 belongs first in the WordPress gateway-timeout path, while background work that stops advancing may need scheduled-action backlog diagnosis. Neither symptom proves a deadlock until the database or client receipt says so.

Reconstruct the lock cycle before changing a timeout

Start with the last InnoDB deadlock record as soon as practical. A later deadlock replaces it, so preserve the output before an unrelated collision becomes the new LATEST DETECTED DEADLOCK:

SHOW ENGINE INNODB STATUS\G

Read both transaction sections, not only the statement selected as victim. Record the active query, tables and indexes, lock mode, records held, records requested, transaction age, row counts, and the final WE ROLL BACK TRANSACTION line. Oracle’s current MySQL 8.4 deadlock documentation explains that opposite access order can involve rows, index records, and gaps rather than only obvious table locks.

Draw the two orders in application language

Suppose checkout path A updates orders and then inventory, while worker path B reserves inventory before updating the same order. Each path is locally reasonable; together they can form a cycle:

Translate internal record data into the owning repository method, endpoint, queue consumer, or stored routine. The useful artifact is not a pasted monitor dump; it is a pair of call paths with the exact objects acquired in different order. That mapping gives developers a change they can review.

Use current waits as supporting evidence, not history

performance_schema.data_locks and data_lock_waits show locks that exist now. By the time error 1213 reaches the client, InnoDB has already broken that specific cycle, so the rows may be gone. The data lock table reference is most useful when contention remains active or when a long blocker accompanies the deadlock pattern.

Current waits can still expose a hot index, oversized transaction, or recurring blocker. Avoid building automation around the internal format of ENGINE_LOCK_ID; Oracle documents that format as subject to change. When the real incident is replica delay rather than client transaction failure, replication receiver-versus-applier diagnosis keeps those owners separate.

Retry belongs around the transaction boundary

For error 1213, retry every statement that defines the business transaction, beginning from a fresh transaction. Retrying only the last UPDATE can commit an incomplete state because InnoDB rolled back the whole victim transaction. Oracle’s InnoDB error-handling contract states this boundary explicitly.

for attempt in 1..MAX_ATTEMPTS:
    begin a fresh transaction
    try:
        read or lock the required current state
        apply every related database change
        commit
        return success
    catch MySQL 1213 / SQLSTATE 40001:
        roll back or discard the failed connection state
        if attempt is MAX_ATTEMPTS: surface a controlled failure
        wait for bounded jittered backoff

Keep attempts bounded. Add jitter so a synchronized worker fleet does not collide again on the same rows. Emit one metric for initial deadlocks, one for retry success, one for exhausted retries, and latency per attempt. Retries must not hide a storm from operators.

Idempotency belongs around effects that MySQL cannot roll back: payment capture, email, webhook delivery, file creation, or a message published outside the database transaction. Use a stable operation key and an outbox or equivalent commit-linked design where duplicate external execution would be harmful. Teams that need database incident ownership but do not maintain it in-house can compare managed hosting operations against the application’s actual retry and deployment responsibilities.

A lock wait timeout is a different rollback contract

Error 1205 is not interchangeable with deadlock error 1213. Under the default InnoDB behavior, a lock wait timeout rolls back the waiting statement, while a deadlock rolls back the entire transaction. Starting MySQL with --innodb-rollback-on-timeout changes the timeout behavior to a whole-transaction rollback.

That difference means one generic retry any database error wrapper is unsafe. Classify the exact error and server policy, then decide whether the unit of retry is a statement or complete transaction. Raising innodb_lock_wait_timeout can make ordinary waits last longer; it does not prevent InnoDB from detecting a cycle when deadlock detection is enabled.

Remove repeatable cycles from the workload

Application retry is mandatory defense, but frequent identical deadlocks deserve a structural fix. Oracle’s deadlock-handling guidance recommends short transactions, consistent operation order, useful indexes, and fewer unnecessary locking reads.

Acquire shared objects in one canonical order

Choose one order for every code path that touches the same entity set. If order rows precede inventory rows, checkout, cancellation, reservation, and reconciliation should follow that order. For a batch of IDs, sort them deterministically before locking rather than using request arrival order.

Keep remote API calls, user think time, report generation, and unrelated computation outside the critical transaction. Shorter lock duration lowers collision probability, but splitting one atomic business invariant across separate commits can create correctness failures. Preserve the invariant first; shorten only work that does not belong inside it.

Make the predicate lock fewer records

An UPDATE ... WHERE without a selective index can scan and lock more index records than the application author expects. Compare the deadlock report’s index with EXPLAIN for the relevant statement, verify row estimates, and test a candidate index on representative data before production deployment.

Changing isolation level is not a universal fix. READ COMMITTED can reduce some gap-lock behavior for locking reads, yet write-write cycles can still occur. Treat isolation as an application correctness decision with regression tests, not an emergency toggle. Serialize with a table or semaphore row only when the lost concurrency is acceptable and simpler ordering or indexing cannot express the requirement.

Measure whether the fix changed production

One successful request does not prove the cycle is gone. Compare the same traffic class before and after the change: deadlocks per committed transaction, retry success, exhausted retries, p95/p99 latency, affected statement fingerprint, and external-effect duplicates. A deployment passes when the target operation completes under representative concurrency without shifting failures into timeouts or queue growth.

Enable all-deadlock logging only for a bounded investigation

When SHOW ENGINE INNODB STATUS loses events too quickly, enable innodb_print_all_deadlocks temporarily so each event reaches the MySQL error log:

SET GLOBAL innodb_print_all_deadlocks = ON;
-- reproduce or observe the bounded incident window
SET GLOBAL innodb_print_all_deadlocks = OFF;

Confirm the account has the required administrative privilege, know the actual error-log destination, and disable the setting after the evidence window. Frequent monitor text can increase log volume and may expose statement or record context that belongs under restricted retention. The switch captures evidence; it does not fix the lock graph.

Preserve a compact change record: failing operation, two lock orders, chosen victim receipt, retry boundary, schema or code diff, test concurrency, before/after rate, rollback condition, and owner. Voxfor’s database operations library is a useful next stop when the same incident also exposes capacity, replication, or maintenance work owned by a different runbook.

Close with two proofs, not one

Deadlock recovery is complete only when the application retries a whole victim transaction without duplicating external effects and the recurring lock cycle is reduced or deliberately accepted with measurable thresholds. Keep both proofs in the deployment record. A quieter MySQL log without retry telemetry, or a successful retry without a reviewed lock order, leaves half of the failure contract untested.