Deadlocks are not proof that MySQL is unreliable. They are a normal result of concurrent transactions acquiring incompatible locks in different orders. InnoDB detects the cycle and rolls back a victim so the remaining work can continue. The operational failure is usually an application that cannot retry safely, or a transaction design that creates needless contention.
Capture the complete pattern
A deadlock report is most useful when paired with the application operation, SQL text, affected indexes and transaction age. Ask whether two code paths update the same logical entities in a different order, whether a broad predicate locks more rows than expected, and whether a missing index turns a targeted update into a wide scan. A lock queue without a deadlock deserves the same discipline: find the blocker, not just the victim.
Reduce the chance of collision
- Access shared rows in a consistent business-key order.
- Keep transactions short and avoid waiting for remote calls while holding locks.
- Use selective indexes so updates and deletes locate fewer candidate rows.
- Batch large changes with an observable commit boundary.
- Make retry logic idempotent and bounded for expected deadlock victims.
Do not confuse isolation with correctness
Changing isolation level can alter locking and read semantics, but it is not a universal deadlock cure. Validate the business requirement for consistency first. A change that reduces waits while allowing an invalid read or write sequence is not a performance improvement.
Measure the outcome
After a change, compare deadlock frequency, transaction duration, retry volume and user-visible latency. A lower count is encouraging only when the work still completes correctly and the blocking did not migrate to another statement or service.
Read the deadlock report as a sequence
SHOW ENGINE INNODB STATUS includes the most recently detected deadlock. Capture it promptly because another event can replace the example you need. If recurring events require logging, review innodb_print_all_deadlocks with the operator responsible for log volume and access. SQL text and object names can contain sensitive application context.
SHOW ENGINE INNODB STATUS;
For each participant, note the statement, locks already held, lock requested and index involved. The final statement is only the visible end of a transaction. Reconstruct earlier statements from application tracing or approved logs. A transaction that first changes stock and then an order can conflict with another path that changes the order first, even when the two displayed statements look individually reasonable.
Example: inventory updates in opposite order
Consider two concurrent checkouts touching products 10 and 20. Checkout A updates 10 then 20; checkout B updates 20 then 10. A consistent product ordering removes this particular cycle. It does not prove all deadlocks are gone: other rows, foreign-key checks and other application paths can introduce different dependencies. Test with competing requests, not just a single successful checkout.
A retry must restart the business transaction after the database chooses a victim. Replaying only the last statement can omit earlier work. Use bounded retries with backoff and an idempotency strategy for external side effects, and distinguish deadlock handling from lock-wait timeout handling because rollback behaviour is not identical in every case.
Know when killing a blocker makes things worse
A large transaction can require substantial rollback work. Terminating it may leave other requests waiting while recovery completes. Before an emergency action, preserve the evidence, identify the business owner and estimate the affected operation. A request timeout in the web tier does not necessarily mean the database transaction has ended.
Track deadlocks relative to completed transactions, not only as an absolute count. If throughput doubles, the raw count may rise while the failure probability falls. Pair that rate with customer failures, retry duration and lock-wait time. A practical acceptance test deliberately overlaps the previously conflicting paths and checks both resulting data and tail latency.
Keep the evidence for the next incident
Mini DBA MySQL monitoring brings query and metric history together with InnoDB and connection diagnostics for investigating recurring incidents. Available evidence depends on the monitored version, permissions and configured collection.
References and further reading