What causes deadlocks in MySQL, and how do you handle them?

Summary

A deadlock happens when two transactions each hold a lock the other needs. InnoDB detects it and rolls back one transaction (the 'victim') with error 1213. Applications must retry the whole transaction, and reduce deadlocks by locking in a consistent order and keeping transactions short.

Longer Answer

Typical causes: two transactions updating the same rows in opposite order (transfer A to B while B to A); gap or next-key locks from concurrent inserts into the same range; unindexed lookups that lock many rows; foreign-key checks.

Diagnosis: SHOW ENGINE INNODB STATUS shows the latest deadlock with both statements and the locks involved; innodb_print_all_deadlocks=ON logs every deadlock. performance_schema.data_locks shows current locks.

Prevention: access rows in a consistent order (for example by primary key), keep transactions small and fast, index the columns in WHERE clauses of updates, and avoid user think-time inside transactions. Deadlocks can never be eliminated entirely in a busy system, so retry with a small backoff.

Key Points

• Circular waits; InnoDB rolls back one (error 1213).
• Retry the whole transaction.
• Consistent lock order, short transactions, good indexes.
• SHOW ENGINE INNODB STATUS for diagnosis.

Deep dive: Code Clinic: review a PHP money transfer