MariaDB Deadlocks and Lock-Wait Errors

How to diagnose, recover from, and prevent InnoDB deadlocks (error 1213) and lock-wait-timeout errors (error 1205), including the Galera certification-deadlock-at-COMMIT case that is normal behavior on a multi-master cluster.

Quick Reference

• ER_LOCK_DEADLOCK (error 1213, SQLSTATE 40001) is RECOVERABLE : ALWAYS retry the ENTIRE transaction, not just the failed statement.
• ER_LOCK_WAIT_TIMEOUT_EXCEEDED (error 1205) means a transaction waited innodb_lock_wait_timeout seconds (default 50) for a row-lock and was killed. Not a deadlock ; it usually means a long-running other transaction is holding the lock.
• innodb_deadlock_detect=ON (default, global, dynamic) auto-aborts the smaller transaction on cycle-detection. NEVER turn this OFF on a workload with cycle-risk ; the alternative is silent stalls until innodb_lock_wait_timeout.
• innodb_print_all_deadlocks=ON (default OFF, global) writes every detected deadlock to the error log. ALWAYS turn this ON in production for forensic visibility.
• Lock-order discipline : ALWAYS acquire locks on tables in the same order across all transactions. Inconsistent order is the #1 cause of avoidable deadlocks.
• Galera certification-deadlock at COMMIT is NORMAL behavior, NOT a bug. The certification protocol detects write-write conflicts only at commit-time. ALWAYS retry on ER_LOCK_DEADLOCK returned from COMMIT.
• READ COMMITTED disables gap locks (per the InnoDB lock-modes KB), reducing deadlock count but allowing phantom-reads. Switch only after you understand the consistency trade-off.
• NEVER use READ UNCOMMITTED to "avoid deadlocks". It allows dirty reads of uncommitted data and is almost never the right answer.

Error Identification