How a deadlock forms
A deadlock occurs when two or more transactions wait on locks held by one another in a cycle. Transaction A may hold a lock on row one and wait for row two, while transaction B holds row two and waits for row one. Neither can proceed, so the database must break the cycle. InnoDB detects deadlocks and rolls back one transaction as a victim, allowing other work to continue. Deadlocks can occur in a correct application whenever concurrent operations touch shared rows in conflicting orders.
A deadlock is different from a lock wait timeout. A deadlock is a detected cycle; a timeout means a session waited longer than its configured limit. Both require handling, but retry policies should distinguish their error codes and semantics. A deadlock victim's transaction is rolled back, while application behavior after a timeout may depend on the statement and transaction context. Do not assume every failed query leaves a transaction in a state ready for the next operation.
Capture useful diagnostics
When a deadlock occurs, collect the database error code, involved statements, transaction boundaries, and relevant table/index definitions. InnoDB deadlock information can show the waiting and held locks; performance schema and engine status tools provide additional diagnostics, with exact interfaces varying by version. Logs should preserve enough context to correlate a failure without recording passwords, tokens, or unnecessary personal data. A generic message such as “database error” makes recurring contention difficult to diagnose.
Reproduce with controlled concurrent sessions and the same query order. Determine which rows and indexes are involved, including foreign-key checks and secondary indexes that writes may update. A deadlock may involve a different table than the application developer expected because one statement changes several indexed values or triggers other work. Avoid trying to fix the symptom by adding arbitrary delays; first understand the cycle.
Reduce deadlock probability
- Access shared rows and tables in a consistent order across workflows.
- Keep transactions short and avoid waiting on user input or remote calls while locks are held.
- Use precise predicates and suitable indexes to avoid examining unnecessary records.
- Update only the rows and columns required by the operation.
- Review triggers, foreign keys, and cascades that add indirect writes.
Consistent ordering often removes common cycles. For example, if a workflow transfers between two accounts, lock or update account identifiers in a stable sorted order regardless of transfer direction. This does not eliminate every possible deadlock, but it reduces conflicts among those operations. Indexes can reduce search and lock scope, though exact InnoDB locking depends on isolation, access path, and version.
Retry safely and narrowly
Applications may retry a transaction that was rolled back as a recognized transient deadlock. Retry the entire transaction from the beginning, not just the statement that happened to fail, because earlier reads and decisions may no longer be valid. Bound the number of attempts and use a small randomized backoff to avoid synchronized retries. Record retries and eventual failures for monitoring. Do not retry syntax errors, permission failures, invalid data, or every timeout under the same policy.
The transaction must be safe to repeat. A database rollback does not undo an email, message, or payment request already sent to an external system. Use idempotency keys, an outbox pattern, or another coordinated approach for side effects. If the connection drops during commit, the outcome may be uncertain; blindly rerunning can duplicate an operation. Design business actions so a stable operation identifier can determine whether they already completed.
Concurrency control beyond retries
Retries are resilience, not a substitute for sound modeling. Use unique constraints to protect uniqueness, conditional updates for simple state transitions, and locking reads where a read-modify-write sequence needs coordination. Higher isolation can change lock behavior and contention, so measure before changing it. Keep transaction scope aligned with the invariant: too little coordination risks inconsistent data, while unnecessarily broad transactions increase conflicts.
Monitor deadlock frequency, lock wait time, transaction duration, connection saturation, and query latency together. A rising deadlock rate may signal a new workload, missing index, or changed transaction order. Review isolation and locking, transaction boundaries, and index design.
Operational response
When deadlocks become frequent, first identify the affected workflows and reproduce them with competing sessions. Compare transaction statement order and inspect relevant indexes. Make one change, then measure deadlock rates and user-visible outcomes. Avoid disabling deadlock detection or setting extreme timeouts as a quick fix; those actions can convert clear failures into longer stalls. Document retry behavior and ensure alerts detect sustained spikes rather than every transient occurrence.
InnoDB deadlocks are a normal possibility in concurrent systems, but uncontrolled repetition is not. Diagnose the lock cycle, make access patterns consistent, keep transactions short, and retry only complete idempotent units with bounded policy.