MySQL / Editorial solution guide

Deadlocks and lock waits: diagnose before retrying

Use read-only lock evidence, distinguish rollback scope, and bound complete-business-operation retries.

About 3 min readContent reviewed

Read-only knowledge base ยท No public comments

Recognize the symptoms

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction. InnoDB detected a cycle and normally rolled back the selected victim transaction. A victim is not necessarily the transaction that began last or caused the most business harm.

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction. A wait exceeded its limit; this alone does not prove a cycle. Under the default timeout rollback setting, the waiting statement is rolled back rather than automatically undoing all earlier business writes.

Understand the likely causes

  • Opposite acquisition order creates cycles: transaction A holds product 1 and requests 2 while B holds 2 and requests 1. Range scans, foreign-key checks, and secondary-index maintenance can introduce less obvious shared resources.
  • Long transactions, remote calls inside transactions, unindexed write predicates, and idle connections holding locks can cause long waits even without a deadlock. Metadata locking is separate from row locking and can delay DDL.

Capture a minimal correlated incident

Target MySQL 8.4 InnoDB through an existing authorized diagnostic connection only. Record error code, time, request id, transaction boundary, isolation, and the business resources involved. Redact SQL values containing personal data. Do not issue KILL, change timeouts globally, or disable deadlock detection merely to obtain evidence; those are operational interventions requiring their own reviewed context.

Inspect current wait relationships

Read the requesting and blocking transaction identifiers, then correlate them with transaction age and connection ownership. The tables are live snapshots: a deadlock may already be resolved before inspection. An empty wait table therefore does not invalidate the application's recorded ERROR 1213. Access requires appropriate diagnostic privileges.

sql Read current waits and transaction ages 
SELECT REQUESTING_ENGINE_TRANSACTION_ID,
       BLOCKING_ENGINE_TRANSACTION_ID
FROM performance_schema.data_lock_waits;
SELECT trx_id,trx_state,trx_started,trx_wait_started,trx_mysql_thread_id
FROM information_schema.innodb_trx ORDER BY trx_started;

No fixed output is asserted because timing determines current rows. Investigate the blocking transaction's owner and lifecycle before proposing termination; its visible statement may have finished while the transaction still owns locks.

Inspect historical evidence and rollback scope

SHOW ENGINE INNODB STATUS can show the latest detected deadlock and chosen victim. Check whether timeout rollback behavior differs from the default before reasoning about a failed attempt. Diagnostic reports may include statement text and infrastructure details; retain only the necessary restricted incident evidence.

sql Inspect rollback configuration without changing it 
SHOW ENGINE INNODB STATUS;
SELECT @@GLOBAL.innodb_rollback_on_timeout AS rollback_on_timeout,
       @@GLOBAL.innodb_deadlock_detect AS deadlock_detection;

A deadlock victim rollback differs from the default statement-only timeout rollback. Whatever the observed configuration, the application should explicitly abandon and roll back the complete failed business attempt before starting a new retry.

Correct ordering and retry the right unit

Acquire product ids in one consistent order, index targeted predicates, and remove user or network waits from the transaction. After an error, clean up the failed attempt and start a new transaction that rereads current state. Retry the complete idempotent unit with a small attempt cap, a request deadline, and backoff with jitter. Replaying only the last UPDATE can leave earlier writes inconsistent. A unique request key is needed to reconcile uncertain commits and external payment effects.

Verify the fix

Rehearse the corrected application workflow in a disposable two-session lab. Confirm consistent lock order, complete rollback of failed attempts, a bounded attempt count, and no duplicate order for one request key. Verify stock and order-line totals after contention, and monitor recurring error rates rather than expecting every possible deadlock to disappear.

Keep it from recurring

  • Set transaction lifetime budgets, instrument attempt ids and wait duration, inspect slow write predicates, and document transaction ownership. Raising lock wait timeouts or lowering isolation can change symptoms while leaving the business invariant unsafe; evaluate those choices independently.

Official references