InnoDB のデッドロックを分析し、すべてをログに記録する

コマンド

-- 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;

概要

デッドロックに関与した 2 つのトランザクション、それぞれが保持・待機しているロック (インデックス、lock_mode、レコード)、および InnoDB がどちらをロールバックしたかを表示する。カウンターで頻度を追跡し、エラーログに過去のデッドロックを残す。

使用する場面

アプリケーションがエラー 1213 'Deadlock found when trying to get lock' を受け取る場合、またはデッドロックアラートが増加した場合。

注意

LATEST DETECTED DEADLOCK は最後のイベントのみを保持し、再起動で失われる。'HOLDS THE LOCK(S)' / 'WAITING FOR THIS LOCK TO BE GRANTED' 行にあるインデックス名と lock_mode (X、S、gap、insert intention) が原因を示し、'WE ROLL BACK TRANSACTION (n)' が犠牲となったトランザクションを示す。1213 が発生した場合、アプリケーションはトランザクション全体を再試行する必要がある。恒久的な対策: 行へのアクセス順序を一定にする、トランザクションを短く保つ、適切なインデックスを使用してギャップロックを減らす、適切な場合は READ COMMITTED を使用する。log_error_verbosity=3 はすべての Note メッセージも有効にするため、エラーログの増加に注意する。Innodb_deadlocks ステータス変数は MySQL Community には存在しないため、カウンターには INNODB_METRICS を使用する。