Analyze InnoDB deadlocks and log all of them

Command

-- Latest deadlock: LATEST DETECTED DEADLOCK section (only the most recent is kept)
SHOW ENGINE INNODB STATUS\G
-- Total deadlock counter
SELECT NAME, COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'lock_deadlocks';
-- Write every deadlock to the error log (logged at Note level, needs verbosity 3)
SET PERSIST innodb_print_all_deadlocks = ON;
SET PERSIST log_error_verbosity = 3;

What it does

Shows the two transactions in the deadlock, the lock each holds and waits for (index, lock_mode, record) and which one InnoDB rolled back; the counter tracks frequency and the error log keeps past deadlocks.

When to use it

When the application gets error 1213 'Deadlock found when trying to get lock' or deadlock alerts increase.

Caution

LATEST DETECTED DEADLOCK keeps only the last event and is lost on restart. The index name and lock_mode (X, S, gap, insert intention) in the 'HOLDS THE LOCK(S)' / 'WAITING FOR THIS LOCK TO BE GRANTED' lines explain the cause; 'WE ROLL BACK TRANSACTION (n)' shows the victim. On 1213 the application must retry the whole transaction. Permanent fixes: access rows in a consistent order, keep transactions short, use the right index to reduce gap locks and READ COMMITTED where appropriate. log_error_verbosity=3 also enables every Note message; watch error log growth. The Innodb_deadlocks status variable does not exist in MySQL Community; use INNODB_METRICS for the counter.