Error 1213: Deadlock found when trying to get lock. Your application retried and the user never noticed — this time. But your error logs show 400 deadlocks per hour and the root cause is a 6-line transaction that every engineer on your team has written without realizing it's a deadlock factory. Here's how to find and eliminate them.
TL;DR
- Deadlocks occur when two transactions hold locks the other needs — MySQL kills the cheaper one automatically
SHOW ENGINE INNODB STATUSshows the last deadlock with full transaction details- Most deadlocks are fixed by ensuring all transactions access rows in the same order
- Enable
innodb_deadlock_detect=ON(default) andinnodb_print_all_deadlocks=ONto log every deadlock
Reading a Deadlock Report
sql
-- Show the last deadlock (extremely detailed output)
SHOW ENGINE INNODB STATUS\G
-- Look for the LATEST DETECTED DEADLOCK section
-- Enable logging ALL deadlocks to error log
SET GLOBAL innodb_print_all_deadlocks = ON;
-- Sample deadlock section output:
-- TRANSACTION 1, ACTIVE 0 sec starting index read
-- MySQL thread id 42, query id 1234 UPDATE orders SET status='done' WHERE id=100
-- HOLDS THE LOCK: RECORD LOCKS space id 123 page no 4 n bits 72
-- index PRIMARY of table orders trx id 456 lock_mode X locks rec but not gap
-- WAITING FOR THIS LOCK:
-- index PRIMARY of table order_items ...
--
-- TRANSACTION 2, ACTIVE 0 sec
-- UPDATE order_items SET qty=2 WHERE order_id=100
-- HOLDS THE LOCK: order_items PRIMARY
-- WAITING FOR: orders PRIMARY
-- WE ROLL BACK TRANSACTION 2The Classic Deadlock Pattern
sql
-- Transaction A (thread 1):
START TRANSACTION;
UPDATE orders SET status = 'processing' WHERE id = 100; -- locks orders row 100
UPDATE order_items SET status = 'reserved' WHERE order_id = 100; -- waits for order_items
-- Transaction B (thread 2) -- running simultaneously:
START TRANSACTION;
UPDATE order_items SET status = 'cancelled' WHERE order_id = 100; -- locks order_items
UPDATE orders SET status = 'cancelled' WHERE id = 100; -- waits for orders
-- DEADLOCK: A waits for B, B waits for AFix: Consistent Lock Ordering
sql
-- Fix: ALWAYS lock tables in the same order: orders first, then order_items
-- Transaction A (fixed):
START TRANSACTION;
UPDATE orders SET status = 'processing' WHERE id = 100;
UPDATE order_items SET status = 'reserved' WHERE order_id = 100;
COMMIT;
-- Transaction B (fixed):
START TRANSACTION;
UPDATE orders SET status = 'cancelled' WHERE id = 100; -- same order: orders first
UPDATE order_items SET status = 'cancelled' WHERE order_id = 100;
COMMIT;
-- No deadlock: both transactions acquire locks in the same orderOther Common Deadlock Patterns
Gap Locks on Range Queries
sql
-- Deadlock from gap locks on range INSERT + SELECT FOR UPDATE
-- Transaction A:
SELECT * FROM orders WHERE id BETWEEN 100 AND 200 FOR UPDATE;
-- Acquires gap lock on (100, 200)
-- Transaction B (concurrent):
INSERT INTO orders (id, ...) VALUES (150, ...); -- blocked by gap lock
-- Fix: use READ COMMITTED isolation (removes gap locks)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- OR use explicit row locks on specific IDs instead of rangesMonitoring Deadlock Rate
sql
-- Track deadlock count over time
SELECT variable_value AS deadlocks_total
FROM performance_schema.global_status
WHERE variable_name = 'Innodb_deadlocks';
-- If this number is growing fast, check innodb status for patterns
-- Alert in Prometheus:
-- rate(mysql_global_status_innodb_deadlocks[5m]) > 1Important
Applications must always retry on deadlock (error 1213). MySQL rolls back the cheaper transaction automatically, but your application code must catch the error and retry the entire transaction from scratch — not just the failed statement.
Key Takeaways
- The most common deadlock fix is enforcing consistent lock ordering across all transactions that touch the same tables.
- Enable
innodb_print_all_deadlocks=ONto log every deadlock — the default only keeps the last one. - READ COMMITTED isolation eliminates gap locks and resolves many range-query deadlocks at the cost of non-repeatable reads.
- Application code must always retry on error 1213 — MySQL rolls back one side automatically but does not retry.
Working with JusDB on MySQL Locking
JusDB diagnoses MySQL deadlock and locking patterns for engineering teams experiencing transaction contention. We read InnoDB status output, identify lock ordering violations in application code, and implement fixes that eliminate recurring deadlocks without changing business logic.