第239回 SHOW ENGINE INNODB STATUSのLATEST DETECTED DEADLOCKの見方

デッドロックとは、複数のトランザクションが互いに必要とするロックを保持しているために、どのトランザクションも進行できない状況を指します。両方のトランザクションがリソースの利用可能状態を待機しているため、保持しているロックは解放されず、互いに待機状態になります。

MySQLではデッドロック検出が有効（innodb_deadlock_detectオプションがON）になっている場合、デッドロックが発生するとInnoDBがこの状況を検出し、いずれかのトランザクションをロールバックします。SHOW ENGINE INNODB STATUSコマンドを使用すると、直近で発生したデッドロック情報をLATEST DETECTED DEADLOCKセクションで確認することができます。ここに表示される情報を直近の検知したデッドロック1件のみとなっているので、ご注意ください。

そして、この情報だけではデッドロックのすべての原因や詳細を確認することはできません。発生したデッドロックの一部の情報が得られるため、それをもとに自身のワークロードと照らし合わせて調査を行う必要があります。

今回はSHOW ENGINE INNODB STATUSのLATEST DETECTED DEADLOCKの見方について紹介したいと思います。

`SHOW ENGINE INNODB STATUS`コマンド

はじめに、SHOW ENGINE INNODB STATUSコマンドについて簡単に紹介します。

InnoDB ストレージエンジンの状態に関する広範囲にわたる情報を表示します。以下のような項目があり、各種セクションに分けて表示されます。

メインバックグラウンドスレッドによって実行される作業
セマフォ待機
最新の外部キーおよびデッドロックエラーに関するデータ
トランザクションのロック待機
アクティブなトランザクションによって保持されているテーブルおよびレコードのロック
保留中の I/O 操作および関連する統計
挿入バッファおよび適応ハッシュインデックスの統計
redo ログデータ
バッファープールの統計
行操作データ

`LATEST DETECTED DEADLOCK`セクションを見てみる

それでは、実際にデッドロックを発生させて、LATEST DETECTED DEADLOCKセクションを見てみましょう。以下のように2つのトランザクションからSQLを実行して、デッドロックを起こします。

(1)TRANSACTION	(2)TRANSACTION
BEGIN	BEGIN
UPDATE ii SET id2=11 WHERE id=1;
	UPDATE ii SET id2=11 WHERE id=2;
UPDATE ii SET id2=111 WHERE id=2;(Waiting)
	UPDATE ii SET id2=111 WHERE id=1;
	(Rollback by Dead Lock)

デッドロック発生後、SHOW ENGINE INNODB STATUSコマンドを実行して、LATEST DETECTED DEADLOCKセクションを確認すると、以下のように表示されました。

------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-01-19 18:40:06 0x7fb134500700
*** (1) TRANSACTION:
TRANSACTION 68650250, ACTIVE 22 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 50, OS thread handle 140399063869184, query id 4887 localhost root updating
update ii set id2=111 where id=2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 187 page no 3 n bits 72 index PRIMARY of table `aa`.`ii` trx id 68650250 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00000417850c; asc       ;;
 2: len 7; hex 330000217b1a06; asc 3  !{  ;;
 3: len 4; hex 8000000b; asc     ;;
 
*** (2) TRANSACTION:
TRANSACTION 68650252, ACTIVE 16 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 51, OS thread handle 140399063598848, query id 4888 localhost root updating
update ii set id2=111 where id=1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 187 page no 3 n bits 72 index PRIMARY of table `aa`.`ii` trx id 68650252 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00000417850c; asc       ;;
 2: len 7; hex 330000217b1a06; asc 3  !{  ;;
 3: len 4; hex 8000000b; asc     ;;
 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 187 page no 3 n bits 72 index PRIMARY of table `aa`.`ii` trx id 68650252 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000417850a; asc       ;;
 2: len 7; hex 3100002172122c; asc 1  !r ,;;
 3: len 4; hex 8000000b; asc     ;;
*** WE ROLL BACK TRANSACTION (2)

`LATEST DETECTED DEADLOCK`セクションの解説

上から順に解説していきます。

はじめに、 (1) TRANSACTIONセクションです。

2025-01-19 18:40:06 0x7fb134500700
*** (1) TRANSACTION:
TRANSACTION 68650250, ACTIVE 22 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 50, OS thread handle 140399063869184, query id 4887 localhost root updating
UPDATE ii SET id2=111 WHERE id=2

(1) TRANSACTION は1つ目のトランザクションです。
UPDATE ii SET id2=111 WHERE id=2は (1) TRANSACTION がデッドロック検知した時点で実行していたSQLです。

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 187 page no 3 n bits 72 index PRIMARY of table `aa`.`ii` trx id 68650250 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

*** (1) WAITING FOR THIS LOCK TO BE GRANTEDは、(1) TRANSACTIONがロックによって待機したことを表しています。
RECORD LOCKS space id 187 page no 3 n bits 72 index PRIMARY of table `aa`.`ii` trx id 68650250 lock_mode X locks rec but not gap waitingは、
- RECORD LOCKS は行ロックを意味します。
- index PRIMARY of table `aa`.`ii` は aaデータベースのiiテーブルのPrimary Keyを表します。ここがPrimaryではなく、idx_createdなどそのテーブルに存在するインデックス名であれば、それはセカンダリーインデックスを表します。
- lock_mode X locks rec but not gap waiting は lock_mode X locks rec but not gap で待機したということを表しています。

ロックの種類はそれぞれ以下を意味します。

表示	ロックタイプ
lock_mode X locks gap before rec	ギャップロック
lock_mode X	ネクストキーロック
lock_mode X locks rec but not gap	レコードロック

 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00000417850c; asc       ;;
 2: len 7; hex 330000217b1a06; asc 3  !{  ;;
 3: len 4; hex 8000000b; asc     ;;

ここでは対象の行の情報を16進数で表示しています。

今回の対象はプライマリキーであるため、0番目（0:）はプライマリキーであるidカラムを指します。プライマリキーはbigint型で、80000002は10進数での2を意味します（ここの数値は16進数です⁠）⁠。先頭の8はsignedを意味し、unsignedであれば先頭が0になります。idカラムがunsignedであれば、00000002になります。

1番目（1:）はトランザクションIDで、2番目（2:）はロールバックポインターです。3番目（3:）以降はその他のカラムを指します。

余談ですが、トランザクションIDやロールバックポインターは各行に内部的に用意されています。SHOW EXTENDED COLUMNS構文を使用すると、ユーザーがアクセスできない非表示カラムに関する情報も表示されるので、確認できます。

mysql> SHOW EXTENDED COLUMNS FROM ii;
+-------------+--------+------+-----+---------+-------+
| Field       | Type   | Null | Key | Default | Extra |
+-------------+--------+------+-----+---------+-------+
| id          | bigint | NO   | PRI | NULL    |       |
| id2         | int    | YES  |     | NULL    |       |
| DB_TRX_ID   |        | NO   |     | NULL    |       |
| DB_ROLL_PTR |        | NO   |     | NULL    |       |
+-------------+--------+------+-----+---------+-------+

mysql> SHOW COLUMNS FROM ii;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id    | bigint | NO   | PRI | NULL    |       |
| id2   | int    | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+

ここまで、(1) TRANSACTIONでわかることは、以下となります。

UPDATE ii SET id2=111 WHERE id=2で待機した。
iiテーブルのPrimary Keyのid=2の行ロックで待機したので、他トランザクションがロックを保持していた。

続いて、(2) TRANSACTIONセクションです。

*** (2) TRANSACTION:
TRANSACTION 68650252, ACTIVE 16 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 51, OS thread handle 140399063598848, query id 4888 localhost root updating
update ii set id2=111 where id=1

(2) TRANSACTION は2つ目のトランザクションです。
UPDATE ii SET id2=111 WHERE id=1は、(2) TRANSACTION がデッドロック検知した時点で実行していたSQLです。

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 187 page no 3 n bits 72 index PRIMARY of table `aa`.`ii` trx id 68650252 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00000417850c; asc       ;;
 2: len 7; hex 330000217b1a06; asc 3  !{  ;;
 3: len 4; hex 8000000b; asc     ;;

*** (2) HOLDS THE LOCK(S): は (2) TRANSACTIONが取得していたロックです。

 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 187 page no 3 n bits 72 index PRIMARY of table `aa`.`ii` trx id 68650252 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000417850a; asc       ;;
 2: len 7; hex 3100002172122c; asc 1  !r ,;;
 3: len 4; hex 8000000b; asc     ;;
 *** WE ROLL BACK TRANSACTION (2)

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:は、(2) TRANSACTIONが取得しようとしたロックです。これが取得できなくてデッドロックになったことを表します。
*** WE ROLL BACK TRANSACTION (2)はデッドロックを検知し、(2) TRANSACTIONがロールバックしたことがわかります。

ここまで、(2)TRANSACTIONでわかることは、以下です。

aaデータベースのiiテーブルのid=2の行ロックを取得していた。
aaデータベースのiiテーブルのid=1の行ロックを取得しようとしていた。

まとめ

ここまでの解析から考えられるトランザクションの流れは以下の通りです。

(1)TRANSACTION	(2)TRANSACTION
不明	SQL内容は不明だがiiテーブルのid=2をロック
UPDATE ii SET id2=111 WHERE id=2;(Waiting)
	UPDATE ii SET id2=111 WHERE id=1;
	(Rollback by Dead Lock)

LATEST DETECTED DEADLOCKセクションから得られる情報は限られています。このセクションはデッドロックが発生した瞬間の情報を表示しますが、直前に実行したSQLの詳細は含まれていません。そのため、前述の通り、自身のワークロードと照らし合わせて調査を行う必要があります。この情報は、デッドロックを分析するための最初の参考程度に考えてください。

また、3つ以上のトランザクションが関与してデッドロックが発生している場合、この情報だけでは原因を特定することは難しいでしょう。

今回は以下資料を参考にしました。

15.17 InnoDB モニター | MySQL 8.0 リファレンスマニュアル

SHOW ENGINE INNODB STATUSコマンド

LATEST DETECTED DEADLOCKセクションを見てみる

LATEST DETECTED DEADLOCKセクションの解説

まとめ

`SHOW ENGINE INNODB STATUS`コマンド

`LATEST DETECTED DEADLOCK`セクションを見てみる

`LATEST DETECTED DEADLOCK`セクションの解説