最近あるシステムで大量にdeadlockが発生していたのを修正しました。
修正方法が初めてやるタイプの修正だったのと、解決するまでにかなり時間がかかったので記事書いてみました。
具体的な事例
下記の1~3の実装がされている箇所でdeadlockが発生していました。
- バッチ処理の一部に、ループ内でinsert ignore into ~している処理が含まれている。バッチ処理なので複数回呼ばれる。
- show engine innodb statusを叩いて、「LATEST DETECTED DEADLOCK」セクションの「WAITING FOR THIS LOCK TO BE GRANTED」に「lock_mode X insert intention waiting」と表示されている。
- 特定のカラムでUNIQUE KEYが設定されている。
それぞれのイメージは下記です。
1番の実装イメージ
// トランザクション開始
start_transanction();
// 保存するデータの配列
$data_list = [
[1, 11],
[2, 22],
// こんな感じのデータが大量にある
];
// 過去の経緯的にデータを分割して入れないとエラーになってしまうらしく、50件ずつデータをinsertするためarray_chunk()で50件毎に分割する。
$chunks = array_chunk($data_list, 50);
// chunk毎(=50件毎)にデータをinsert ignore intoするためのループ
foreach($chunks as $i => $chunk) {
// 下記のようなinsert ignore into文がDBに投げられて処理される。ここでdeadlockが多発していた。
insert ignore into test_table values(1, 11), (2, 22)・・・(50, 5050)
}
// 他の関係あるテーブルの更新をする処理が続く
// 処理終わったらコミット
commit;
2番のイメージ例。(関係なさそうなところは省略。supremumと「lock_mode X insert intention waiting」が出力されてることが大事)。
------------------------ LATEST DETECTED DEADLOCK ------------------------ *** (1) TRANSACTION: 省略 INSERT IGNORE INTO 省略 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: 省略 lock_mode X insert intention waiting 省略 0: len 8; hex 73757072656d4c23; asc supremum;; *** (2) TRANSACTION: 省略 INSERT IGNORE INTO 省略 *** (2) HOLDS THE LOCK(S): 省略 0: len 8; hex 73757072656d4c23; asc supremum;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: 省略 lock_mode X insert intention waiting 省略 0: len 8; hex 73757072656d4c23; asc supremum;; *** WE ROLL BACK TRANSACTION (1)
3番のテーブルイメージ
CREATE TABLE `test_table` ( `test_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `a_id` bigint(20) NOT NULL, `b_id` bigint(20) NOT NULL, PRIMARY KEY (`test_id`), UNIQUE KEY `a_b_id` (`a_id`,`b_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
前提知識
- TX: トランザクションの略。
- TX分離レベル: トランザクション分離レベルの略。MySQLのデフォルト値はREPEATABLE READ。
- deadlockが何か分かること。
- 排他ロックが何か分かること。
- インテンションロックが何か分かること。
- MySQL公式ドキュメントのインテンションロックの項目を読むと分かりやすい。
- IXロック: インテンションロックの1種、innodbではある行の排他ロックを取得する前にIXロックを取得する。IXロックは排他ロックと競合する。
- supremumレコード: 保存されているインデックスの最大値を超えた範囲を表す仮想的な行のこと。
対応方法
結論的には、TX分離レベルをREPEATABLE READからREAD COMMITTEDに変更したらdeadlockが発生しなくなりました。
修正方法のイメージは下記です。
修正イメージ
// 下記のSQL文で、TX分離レベルをREAD COMMITTEDに変更しておく。
$this->db->query('SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;');
// トランザクション開始
start_transanction();
// 以下変更なし
原因と対応方法解説
今回のdeadlock発生原因はsupremumレコードがIXロックされてしまい発生している感じでした。
具体的には下記のような流れでdeadlockが発生していると思われました(多分)。
- TX2: insert文で、supremumレコードに対してXロックを取得する。
- TX1: insert文で、supremumレコードに対してIXロックを取得待ちになる。
- TX2: insert文で、supremumレコードに対してIXロックを取得する。(既にTX1がIXロック取得待ちなため、TX2にIXロックを付与できずdeadlockすると思われる)
上記のdeadlockはIXロックを取得してしまうことが原因なので、TX分離レベルをREAD COMMITTEDに変更することによってIXロックを取得しないようにしました。
今回の場合、insert ignore文だったため、同一レコードがinsertされる時にエラーが出ても特に問題なく処理が進むので上記で対応できました。
また、TX分離レベルをREAD COMMITTEDに変更するとファントムリードや反復不能読み取りが発生する可能性が発生しますが、今回はトランザクション内でSELECTした結果を使って何か別の処理をしたりなどはしていなかったため無視できました。
感想
解決するにはしたが、めちゃくちゃ時間かかったし今でも本当に理解があっているのかちょっと謎い。
MySQLは難C。
