This BAOI guide provides a structured method for diagnosing mysql 1213-2.
Avant de commencer : adaptez toujours les commandes et manipulations à votre environnement. Sur un système de production, prévoyez une backup ou un retour arrière lorsque l’action peut modifier la configuration.
Étapes à suivre
1
Collect
To identify the exact error, time time and context.
2
Limit
Identify the systems and users involved.
3
Test
Use appropriate tools or commands to confirm cause.
4
Correct
Apply a targeted and reversible correction.
5
Validate
Play the full screenplay and check the logs.
Commands utiles
SHOW ENGINE INNOBB STATUUS;
À retenir
Keep the initial state.
Avoid multiple simultaneous changes.
Document the final result.
Technical deep dive
MySQL 1213: a deadlock is a lock cycle, not just slowness
Technical checkpoints
InnoDB detects a lock cycle and chooses a victim transaction to roll back so others can continue.
SHOW ENGINE INNODB STATUS contains the latest deadlock with involved transactions, indexes and locks.
Shorter transactions and consistent row/table access order reduce deadlocks.
InnoDB evidence
Capture the deadlock before a later incident replaces the last report.
SHOW ENGINE INNODB STATUS\G
SET GLOBAL innodb_print_all_deadlocks = ON;
Topic-specific pitfalls
Increasing lock_wait_timeout does not fix a deadlock cycle.
Application retry is useful but should not replace analysis of frequent deadlocks.
How to validate
The concurrent scenario is replayed and deadlock rate falls to an acceptable level.
Operation/index order reduces locking and transactions remain short.
Operational context
MySQL error 1213 means InnoDB selected a transaction as a deadlock victim. The correct diagnosis is to capture the deadlock graph, identify the statements and lock order involved, then fix transaction access patterns rather than simply increasing timeouts.
Step-by-step checks
Capture the latest detected deadlock immediately after the incident and identify both transactions, locked records/indexes and the statement selected as victim.
Correlate the SQL with application request/job identifiers and transaction boundaries so the competing code paths can be reproduced.
Check whether the transactions access the same tables/rows in different orders, hold locks for unnecessarily long work or scan too many rows because of missing/selectivity-poor indexes.
Review deadlock frequency over time; occasional deadlocks may require application retry handling, while repeated patterns usually justify query or transaction redesign.
Useful verification commands
Use commands only on systems you administer and capture the read-only output before making a configuration change.
SHOW ENGINE INNODB STATUS;
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;
How to validate the result
The targeted workload must run repeatedly without reproducing the same deadlock graph, or the application must demonstrate bounded safe retries while database lock duration and throughput remain normal.
Evidence to keep
Keep SHOW ENGINE INNODB STATUS deadlock output, involved SQL and indexes, transaction boundaries, application correlation IDs and before/after deadlock frequency.
Frequently asked question
Will increasing innodb_lock_wait_timeout fix error 1213?
No. A deadlock is a cycle and InnoDB resolves it immediately by rolling back a victim transaction. Lock-wait timeout tuning does not remove the conflicting lock order.