ERROR database concurrency_error ai_generated true

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

ID: database/mysql-innodb-deadlock-timeout

Also available as: JSON · Markdown
85%Fix Rate
88%Confidence
58Evidence
2023-01-01First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
8 active — — —

Root Cause

InnoDB detected a deadlock cycle between two or more transactions and rolled back the one with the fewest row changes (the victim). This is different from MySQL's lock wait timeout (ERROR 1205). InnoDB has built-in deadlock detection that immediately resolves deadlocks by choosing a victim, rather than waiting for a timeout.

generic

Workarounds

  1. 88% success Retry the deadlocked transaction with exponential backoff 
    Catch SQLSTATE 40001 (or MySQL error 1213) and retry the entire transaction. Use exponential backoff: 50ms, 100ms, 200ms with random jitter. Limit to 3-5 retries. Most deadlocks succeed on the first retry since the conflicting transaction has already completed.
  2. 85% success Access tables and rows in a consistent order across all transactions 
    Analyze the deadlock: SHOW ENGINE INNODB STATUS\G; Look at the LATEST DETECTED DEADLOCK section. Ensure transactions acquire locks in the same order (e.g., always update table A before table B, lock rows by ascending PK). Add appropriate indexes to reduce gap locking. Keep transactions as short as possible.

Dead Ends

Common approaches that don't work:

  1. Setting innodb_deadlock_detect = OFF to disable deadlock detection 85% fail

    With deadlock detection disabled, InnoDB falls back to innodb_lock_wait_timeout to resolve deadlocks. This means transactions wait the full timeout (default 50s) before failing, severely degrading performance. Deadlocks that were resolved in milliseconds now take 50 seconds.

  2. Using LOCK TABLES to prevent deadlocks by locking entire tables 80% fail

    Table-level locks serialize all access, destroying concurrency. InnoDB's row-level locking allows much higher throughput. LOCK TABLES also does not work with InnoDB's transactional semantics and can cause more issues than it solves.

Error Chain

Leads to:
Preceded by:
Frequently confused with: