What a deadlock means
A deadlock occurs when concurrent transactions hold locks that the other transactions need. InnoDB detects the cycle and rolls back one transaction so the others can continue. Deadlocks are a normal possibility in concurrent systems; the goal is to reduce their frequency and handle them correctly.
Read the diagnostic output
Use SHOW ENGINE INNODB STATUS soon after a deadlock to inspect the latest detected event. The output can show the statements, indexes and locks involved. For recurring incidents, configure appropriate monitoring and retain database logs according to operational policy.
Reduce lock contention
Keep transactions short. Do not call remote APIs, send email or wait for user input while a database transaction is open. Access rows in a consistent order across code paths, and make sure predicates use suitable indexes so a statement does not lock more rows than expected.
Retry only the complete transaction
Applications may retry a transaction after a deadlock, but the retry must start from the beginning. Do not retry only the statement that failed if earlier statements in the transaction have been rolled back. Use a small bounded retry count with jitter.
$attempt = 0;
$maxAttempts = 3;
while (true) {
try {
$pdo->beginTransaction();
performBusinessChanges($pdo);
$pdo->commit();
break;
} catch (PDOException $e) {
if ($pdo->inTransaction()) {
$pdo->rollBack();
}
$attempt++;
if (!isDeadlock($e) || $attempt >= $maxAttempts) {
throw $e;
}
usleep(random_int(20_000, 100_000) * $attempt);
}
}
isDeadlock() must inspect the driver-specific SQLSTATE or error code for the database in use. Never retry every database exception indiscriminately.
Checklist
- Keep transactions short and database-only.
- Access shared rows in a consistent order.
- Index predicates used by updates and deletes.
- Capture InnoDB deadlock diagnostics.
- Retry the whole transaction a limited number of times.
- Make external side effects idempotent or defer them until after commit.