Use when ER_LOCK_DEADLOCK or ER_LOCK_WAIT_TIMEOUT errors appear, designing lock-discipline for concurrent transactions, reading SHOW ENGINE INNODB STATUS deadlock section, tuning innodb_deadlock_detect, or handling Galera certification-COMMIT deadlocks. Prevents the common mistake of expecting deadlocks to vanish via retry-without-design-fix, missing gap-lock dynamics, or treating Galera ER_LOCK_DEADLOCK as a bug instead of expected outcome. Covers InnoDB deadlock detection (innodb_deadlock_detect ON default), reading deadlock section of SHOW ENGINE INNODB STATUS, lock-order discipline (always-same-table-order), gap locks vs record locks vs next-key locks, transaction isolation levels, application-layer retry strategy, Galera COMMIT-time deadlock semantics. Keywords: deadlock, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, 1213, 1205, SHOW ENGINE INNODB STATUS, innodb_lock_wait_timeout, innodb_deadlock_detect, gap lock, next-key lock, record lock, lock order, retry transaction, REPEATABLE READ, READ COMMITTED, Galer
Use when ER_LOCK_DEADLOCK or ER_LOCK_WAIT_TIMEOUT errors appear, designing lock-discipline for concurrent transactions, reading SHOW ENGINE INNODB STATUS deadlock section, tuning innodb_deadlock_detect, or handling Galera certification-COMMIT deadlocks. Prevents the common mistake of expecting deadlocks to vanish via retry-without-design-fix, missing gap-lock dynamics, or treating Galera ER_LOCK_DEADLOCK as a bug instead of expected outcome. Covers InnoDB deadlock detection (innodb_deadlock_detect ON default), reading deadlock section of SHOW ENGINE INNODB STATUS, lock-order discipline (always-same-table-order), gap locks vs record locks vs next-key locks, transaction isolation levels, application-layer retry strategy, Galera COMMIT-time deadlock semantics. Keywords: deadlock, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, 1213, 1205, SHOW ENGINE INNODB STATUS, innodb_lock_wait_timeout, innodb_deadlock_detect, gap lock, next-key lock, record lock, lock order, retry transaction, REPEATABLE READ, READ COMMITTED, Galera deadlock at commit, my transaction keeps failing, lock wait timeout exceeded, transaction was deadlocked, why does my insert hang, my update is stuck
license
MIT
compatibility
Designed for Claude Code. Requires MariaDB 10.6-LTS, 10.11-LTS, 11.x, 12.x.
metadata
{"author":"OpenAEC-Foundation","version":"1.0"}
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
Symptom
Error number
Symbol
SQLSTATE
Meaning
Transaction rolled back automatically
1213
ER_LOCK_DEADLOCK
40001
InnoDB detected a lock-cycle and aborted this transaction
Statement returned after ~50 seconds with "Lock wait timeout exceeded"
1205
ER_LOCK_WAIT_TIMEOUT_EXCEEDED
HY000
A row-lock was held by another transaction longer than innodb_lock_wait_timeout
Galera COMMIT returned deadlock on a transaction that touched no obviously-contended row
1213
ER_LOCK_DEADLOCK (cluster)
40001
Certification-conflict on the cluster ; another node committed a write-set that conflicts with yours
The two errors are DIFFERENT and the fix path is DIFFERENT :
1213 means the cycle is GONE (server already rolled you back) : retry.
1205 means another transaction is STILL holding the lock : find and kill the blocker, do not just retry blindly.
Decision Tree
Got a lock-related error
|
+-- Error 1213 (ER_LOCK_DEADLOCK) ?
| |
| +-- Is this Galera (wsrep_on=ON) and did it happen at COMMIT ?
| | YES : certification-conflict, NORMAL. Retry full transaction.
| | NO : standard InnoDB deadlock. Retry full transaction.
| |
| +-- Has the same transaction deadlocked > 3 times ?
| YES : design problem (hot-row / lock-order). Stop retrying ; fix the design.
| NO : exponential-backoff retry, max 3-5 attempts.
|
+-- Error 1205 (ER_LOCK_WAIT_TIMEOUT_EXCEEDED) ?
|
+-- Find blocking transaction :
| SELECT * FROM information_schema.INNODB_TRX
| ORDER BY trx_started ASC LIMIT 5;
|
+-- Is the blocker a long-running OLTP transaction ?
| YES : the blocker has a design problem (transaction holding locks too long).
| Fix the blocker, do NOT just raise innodb_lock_wait_timeout.
| NO : transient ; retry once after a short backoff.
|
+-- Has this happened repeatedly under load ?
YES : check innodb_buffer_pool_size, hot-row contention, missing indexes
causing full-scan locks. See anti-patterns.md.
Key System Variables
Variable
Default
Scope
Dynamic
Purpose
innodb_deadlock_detect
ON
Global
Yes
Detects lock-cycles ; aborts smaller transaction with 1213
innodb_lock_wait_timeout
50 (seconds)
Global, Session
Yes
Max seconds a transaction waits for a row-lock before error 1205
innodb_print_all_deadlocks
OFF
Global
Yes
When ON, every detected deadlock is written to the error log
Default isolation level ; READ COMMITTED disables gap locks
ALWAYS set innodb_print_all_deadlocks=ON in production. The cost is negligible and the forensic value is high.
Lock-Type Mental Model
InnoDB lock-modes (confirmed per innodb-lock-modes KB) :
Lock
Level
Purpose
Shared (S)
Row
Read-lock ; multiple S allowed on same row
Exclusive (X)
Row
Write-lock ; blocks all other locks
Intention Shared (IS)
Table
Signals intent to take S on some row in this table
Intention Exclusive (IX)
Table
Signals intent to take X on some row in this table
Record lock
Index entry
Lock on a specific index record
Gap lock
Between index entries
Prevents phantom inserts (REPEATABLE READ only)
Next-key lock
Index entry + gap before
Default REPEATABLE READ lock for range scans
Insert-intention lock
Gap
Special gap lock indicating an INSERT is about to happen
AUTO-INC lock
Table
Serializes assignment of AUTO_INCREMENT values
Compatibility matrix (per the KB) :
X blocks all other locks.
S blocked by X or IX.
IX blocked by X or S.
IS blocked only by X.
REPEATABLE READ (the default) uses next-key locks on range scans, which is why an indexed WHERE range can lock more rows than the actual matches. READ COMMITTED disables gap locks (KB : "Gap locks are disabled if the isolation level is set to READ COMMITTED").
See references/methods.md for the complete lock-mode taxonomy and the SHOW ENGINE INNODB STATUS deadlock-section read-guide.
Reading SHOW ENGINE INNODB STATUS
When a deadlock occurs, the engine records the most recent one in the LATEST DETECTED DEADLOCK block. Run :
SHOW ENGINE INNODB STATUS\G
The block shows TWO transactions, what each held, what each waited for, and which one was chosen as the victim (auto-rolled-back). To capture EVERY deadlock (not only the latest), enable innodb_print_all_deadlocks=ON and tail the MariaDB error log.
-- 10.6+SETGLOBAL innodb_print_all_deadlocks =ON;
The block names a victim at the end :
*** WE ROLL BACK TRANSACTION (1)
Transaction (1) was killed. The other (transaction (2)) succeeded. The victim is typically the transaction that has modified the FEWEST rows so far (cheapest to roll back).
See references/methods.md for a labelled walkthrough of every line of the deadlock-block.
Lock-Order Discipline
The #1 cause of avoidable deadlocks : two transactions acquire locks on the same set of tables in DIFFERENT order. Two transactions doing UPDATE accounts ; UPDATE audit_log and UPDATE audit_log ; UPDATE accounts will deadlock under any concurrent load.
ALWAYS pick a canonical order (alphabetical, foreign-key dependency order, whatever) and enforce it across the entire codebase. Code review should reject any transaction that locks tables in a different order than its peers.
-- 10.6+ : canonical order across ALL business transactionsSTART TRANSACTION;
SELECT id FROM accounts WHERE id = ? FORUPDATE; -- always FIRSTINSERT INTO audit_log (account_id, action, ts) VALUES (?, ?, NOW()); -- always SECONDUPDATE accounts SET balance = balance + ? WHERE id = ?;
COMMIT;
See references/examples.md for a reproducer of a 2-transaction deadlock and the fix.
Application-Layer Retry Strategy
When a deadlock occurs, retry the ENTIRE transaction (not just the failed statement). Use exponential backoff with a maximum retry count. Log every retry.
Retry budget is 3-5 attempts. If a transaction deadlocks more than that, the root cause is a DESIGN problem (hot-row, bad lock-order, missing index) and retries will just mask it.
See references/examples.md for PHP, Node.js, and Java retry implementations.
Galera Certification-Deadlock
On a Galera cluster (wsrep_on=ON), write-write conflicts between nodes are detected at CERTIFICATION time, which is COMMIT. The cluster returns ER_LOCK_DEADLOCK (1213) on the local node even if the local transaction never touched a conflicting row on this node directly.
This is NORMAL behavior, NOT a bug. The application MUST handle it identically to a standalone-InnoDB deadlock : retry the transaction.
A non-zero wsrep_local_cert_failures is expected on a healthy multi-master cluster under write load. Alert ONLY on sudden multi-order-of-magnitude jumps, not on the absolute number. See references/anti-patterns.md for the "treating Galera commit-deadlock as a bug" anti-pattern.
Isolation-Level Impact on Locks
Level
Gap locks
Phantom reads
Deadlock incidence
READ UNCOMMITTED
No
Yes (and dirty reads)
Low (almost no locking)
READ COMMITTED
NO (disabled)
Yes
Lower than RR
REPEATABLE READ (default)
Yes (next-key)
No (until 11.4 limits)
Highest
SERIALIZABLE
Yes (broadest)
No
Highest plus contention
Switching from REPEATABLE READ to READ COMMITTED reduces deadlocks but introduces phantom reads. This is a SEMANTIC change, not a tuning knob. Verify your application can tolerate phantom-reads before switching.
-- 10.6+ : per-session change for an OLTP serviceSET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
NEVER switch the GLOBAL default without auditing every read path. NEVER use SERIALIZABLE on an OLTP workload (it converts plain SELECT into locking reads when autocommit is OFF).
Hot-Row Contention
A single row contended by many writers will deadlock or lock-wait under load no matter how clean your lock-order discipline is. Symptoms : a single counter table, a single sequence row, an inventory row for a popular SKU, a single rate-limit bucket.
Redesign options :
Shard the hot key across N sub-keys, sum at read time.
Use a sequence object (CREATE SEQUENCE, 10.3+) instead of UPDATE counter SET v = v + 1.
Use INSERT ... ON DUPLICATE KEY UPDATE with a sharded primary-key prefix.
Use a queue table with rows-per-message instead of an UPDATE-in-place.
See references/examples.md for a hot-row redesign and references/anti-patterns.md for the single-counter-table anti-pattern.
What This Skill Does NOT Cover
General InnoDB tuning : see mariadb-impl-performance-tuning.
Galera cluster setup : see mariadb-impl-galera-cluster.
Replication conflicts (non-Galera) : see mariadb-core-replication-model.
Optimistic-concurrency patterns (versioned rows) : application-layer concern, out of scope.
Reference Links
references/methods.md : lock-types matrix, SHOW ENGINE INNODB STATUS deadlock-section read-guide, system-variable details, isolation-level impact on locks.
references/examples.md : 10+ working examples (reproduce a deadlock, read INNODB STATUS, retry loops in Python/PHP/Node.js/Java, switch to READ COMMITTED, hot-row redesign, Galera retry pattern).
All facts in this skill were verified via WebFetch against :
mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-system-variables : innodb_deadlock_detect default ON / global / dynamic ; fall-back to innodb_lock_wait_timeout.
mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-lock-modes : S / X / IS / IX lock-modes, compatibility matrix, gap locks disabled at READ COMMITTED.
mariadb.com/kb/en/innodb-system-variables/ : innodb_lock_wait_timeout default 50 / range 1-1073741824 / global+session / dynamic ; innodb_print_all_deadlocks default OFF / global ; error 1205 ER_LOCK_WAIT_TIMEOUT_EXCEEDED.
mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/set-commands/set-transaction : four isolation levels, default REPEATABLE READ, SET TRANSACTION syntax with GLOBAL/SESSION/no-scope semantics.
Vooronderzoek Cluster-3 §2 : Galera certification-conflict at COMMIT semantics, wsrep_local_cert_failures status variable.
