A deadlock is a dependency cycle, not simply a slow query
Two transactions can each hold a lock that the other needs. InnoDB's deadlock detection can choose a victim and roll it back so work can continue. Even a well-designed application needs an explicit way to handle a transaction selected as the victim.
A lock-wait timeout and a detected deadlock are related operational symptoms but not interchangeable outcomes. Record the actual error and transaction behavior. This guide reviews a deadlock and a safe retry boundary; it does not recommend disabling detection or increasing timeouts as a universal repair.
Setup: capture the operations involved
Record the MySQL version, table schema, relevant indexes, application operation and error time. Preserve the statement sequence inside each transaction rather than considering only the final statement that reported failure. Restrict diagnostic access because query text and key values can contain sensitive data.
An authorized read-oriented diagnostic command is:
SHOW ENGINE INNODB STATUS;
The latest-deadlock section describes the most recently retained deadlock, so collect evidence promptly and correlate it with the application incident. If recurring incidents require broader capture, review the supported all-deadlock logging option with attention to log volume and data exposure.
Illustrative incident: opposite account-update order forms a cycle
In this fictional example, two application paths update the same pair of business records in opposite orders. Under concurrency, each transaction acquires its first lock and then waits for the other. A victim is rolled back and one user sees an error.
The team compares the two transaction sequences, then standardizes the access order where appropriate. It also defines a bounded retry for the complete failed business transaction. Repeating only the final SQL statement would not necessarily reconstruct the transaction's intended invariants.
Troubleshooting: follow the lock graph and business boundary
| Observation | Evidence | Question |
|---|---|---|
| Opposite record or table order appears | Transaction sequences and held/requested locks | Can a consistent access order reduce cycles? |
| Broad scans lock more than expected | Query predicates, indexes and plan | Is the lock footprint unnecessarily large? |
| A victim is retried but effects duplicate | Application transaction and external calls | Is retry scoped to an idempotent business operation? |
| Failures persist despite longer waits | Exact deadlock evidence | Was a cycle mistaken for a simple timeout? |
Keep transactions short enough for the business operation, but do not split an atomic change without reviewing correctness. Reducing a lock duration is useful only if the resulting operation still preserves the application's data invariants.
Implement bounded recovery and reduce avoidable contention
At the application layer, identify the database error that permits a transaction retry and restart the complete transaction according to the driver or framework contract. Set a retry budget and make retry exhaustion visible. Rollback and session cleanup must be complete before another attempt begins.
External side effects such as email or payment requests need their own idempotency or durable coordination. A database rollback cannot undo an already accepted remote operation. Keep irreversible work outside an automatically repeated region or use an appropriate outbox and reconciliation design. Review index or statement-order changes with a representative concurrent workload before production rollout.
Verification, alerting and SOP
Test concurrent conflicting operations in a safe environment and verify both final data consistency and absence of duplicated side effects. Measure retry counts and exhausted operations rather than hiding all victim errors behind unlimited retries. Observe whether the revised lock access reduces the specific cycle.
An internal SLO can describe successful completion within a bounded retry budget; a customer SLA requires separately agreed terms. The SOP should include diagnostic capture, sensitive-data handling, transaction owners, retry classification, concurrency tests, rollback and escalation when deadlocks indicate a wider application design issue.
See Clouvenix mysql administration for a reviewed implementation and operational handover.
Technical reference
MySQL InnoDB deadlocks, MySQL InnoDB monitor output, MySQL InnoDB troubleshooting. Reviewed 19 October 2026; use documentation for the installed product and release.