Fix MySQL "Deadlock Found"

Understanding Deadlocks

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

Deadlocks occur when two transactions each hold locks the other needs.

View Recent Deadlocks

SHOW ENGINE INNODB STATUSG

Look for the "LATEST DETECTED DEADLOCK" section.

Common Causes

1. Out of Order Access

Transaction A: UPDATE table1 → UPDATE table2
Transaction B: UPDATE table2 → UPDATE table1

Fix: Always access tables in same order.

2. Missing Indexes

Without indexes, MySQL locks more rows:

SHOW INDEX FROM tablename;
CREATE INDEX idx_name ON tablename(column);

3. Long-Running Transactions

SELECT * FROM information_schema.innodb_trxG

Kill hanging transaction:

KILL transaction_id;

Application-Level Fixes

• Add retry logic for deadlock errors
• Keep transactions short
• Use READ COMMITTED isolation level
• Avoid SELECT ... FOR UPDATE when possible

Change Isolation Level

SET GLOBAL transaction_isolation = 'READ-COMMITTED';
# Or in my.cnf
transaction-isolation = READ-COMMITTED

Reduce Lock Contention

# For hot rows, consider
# - Partitioning tables
# - Sharding data
# - Using optimistic locking