複数セッションからINSERT ... ON DUPLICATE KEY UPDATEを実行した際にデッドロックが発生する問題（MySQL）
TL;DR
- MySQLにおいて、複数セッションから同時に
INSERT ... ON DUPLICATE KEY UPDATEで複数行の挿入を実行するとデッドロックが発生する場合がある - この現象はMySQLの5.7.26（8系なら8.0.16）でのバグ修正に起因すると考えられる
- MySQL自体のバグでは無いため、サーバー側で対策する必要がある（以下対処例）
目次
INSERT ... ON DUPLICATE KEY UPDATEでデッドロックが発生する
INSERT ... ON DUPLICATE KEY UPDATEによる複数行のBULK INSERT/UPDATEを複数プロセスのバッチ処理で実行した際、デッドロックが度々発生するという問題が発生しました。
今回はその再現検証と原因・対策について調査した内容をまとめています。
再現検証
- MySQLバージョン： 8.0.32
- 検証用テーブル
CREATE TABLE `tests` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `foo` int(11) NOT NULL, PRIMARY KEY (id), UNIQUE KEY `index_unique` (`foo`) ) ;
以下のクエリを複数セッションから繰り返し実行したところ、重複レコードが存在するタイミングでデッドロックが発生しました。
INSERT INTO tests (`foo`) VALUES (699422), (699421) ON DUPLICATE KEY UPDATE foo = VALUES (foo); INSERT INTO tests (`foo`) VALUES (699439), (699439) ON DUPLICATE KEY UPDATE foo = VALUES (foo);
エラーメッセージはこちらです。
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
ログ調査
デッドロックの詳細ログを表示した結果は以下の通りです。
mysql> SHOW ENGINE INNODB STATUS; 〜〜〜〜〜〜〜〜〜（略）〜〜〜〜〜〜〜〜〜〜〜 ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2023-07-03 05:46:41 281472373108672 *** (1) TRANSACTION: TRANSACTION 41226, ACTIVE 29 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1128, 4 row lock(s) MySQL thread id 6154, OS thread handle 281472697032640, query id 21610 172.19.0.1 mysql_user update INSERT INTO tests (`foo`) VALUES (699422), (699421) ON DUPLICATE KEY UPDATE foo = VALUES (foo) *** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 2093 page no 4 n bits 80 index PRIMARY of table `app`.`tests` trx id 41226 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2093 page no 4 n bits 80 index PRIMARY of table `app`.`tests` trx id 41226 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** (2) TRANSACTION: TRANSACTION 41227, ACTIVE 15 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 6 lock struct(s), heap size 1128, 4 row lock(s) MySQL thread id 6147, OS thread handle 281472700202944, query id 21625 172.19.0.1 mysql_user update INSERT INTO tests (`foo`) VALUES (699422), (699421) ON DUPLICATE KEY UPDATE foo = VALUES (foo) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 2093 page no 4 n bits 80 index PRIMARY of table `app`.`tests` trx id 41227 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2093 page no 4 n bits 80 index PRIMARY of table `app`.`tests` trx id 41227 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 41229 Purge done for trx's n:o < 41187 undo n:o < 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 562947829962320, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 562947829959896, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 562947829960704, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 562947829959088, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 562947829958280, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 41228, ACTIVE 171 sec 1 lock struct(s), heap size 1128, 1 row lock(s) MySQL thread id 6155, OS thread handle 281472364715968, query id 21619 172.19.0.1 mysql_user ---TRANSACTION 41227, ACTIVE 176 sec 6 lock struct(s), heap size 1128, 8 row lock(s) MySQL thread id 6147, OS thread handle 281472700202944, query id 21625 172.19.0.1 mysql_user -------- 〜〜〜〜〜〜〜〜〜（略）〜〜〜〜〜〜〜〜〜〜〜
デッドロック発生直前にperformance_schema.data_locksで取得したロックのステータスは以下の通りです。
mysql> SELECT ENGINE_TRANSACTION_ID,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks;
| ENGINE_TRANSACTION_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
| 41226 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
| 41226 | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record |
| 41226 | PRIMARY | RECORD | X,INSERT_INTENTION | WAITING | supremum pseudo-record |
| 41227 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 3 |
| 41227 | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record |
| 41228 | PRIMARY | RECORD | X,INSERT_INTENTION | WAITING | supremum pseudo-record |
上記の表の見方は以下の通りです。
- ENGINE_TRANSACTION_ID:
- ロックを要求したトランザクションのID
- INDEX_NAME:
- NULL - テーブル・ロック
- PRIMARY - プライマリインデックス
- index_unique - セカンダリインデックス
- LOCK_MODE:
- LOCK_STATUS:
- GRANTED - トランザクションがロックを所有している
- WAITING - 競合するロックが解放されるまで待機している
- LOCK_DATA:
- supremum pseudo-record - インデックスの最大値を超える疑似値とその範囲に対してギャップロック
- NULL - テーブルロック
- その他
- primary indexの場合 - プライマリ・キーのすべてのカラム
- secondary indexの場合 - インデックスの定義で明示されているカラムの値の後に、残りのプライマリキーのカラムが続く
上記から待機状態のロックを抜き出すと以下の通りです。
| ENGINE_TRANSACTION_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
| 41226 | PRIMARY | RECORD | X,INSERT_INTENTION | WAITING | supremum pseudo-record |
| 41228 | PRIMARY | RECORD | X,INSERT_INTENTION | WAITING | supremum pseudo-record |
インテンションロック（排他ロック）で待機状態となっており、これが競合してデッドロックが発生したと考えられます。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 15.7.1 InnoDB ロック
原因分析
以下のバグ報告のやり取りから、MySQLの5.7.26（8系なら8.0.16）で別のバグ修正のために追加されたロックの影響によって発生した事象だと考えられます。
MySQL Bugs: #98324: Deadlocks more frequent since version 5.7.26
しかし上記の変更は他のバグに対する修正であり、以下の通り今回のデッドロックについてはバグでは無いというのが公式の見解のため、サーバー側での対処が必要となります。
"Deadlock happens" is not a bug - deadlocks should be handled by a properly written application, as they are one of possible outcomes of each transaction.
"Deadlocks happen more often than before" is also not a bug (Yes, it might be a symptom of a bug if the change was a surprising result of some unrelated change we haven't thought through. But in our case, the deadlocks happen more often because we lock more often, and this was an intended change)
"I'd like deadlocks to happen less often" is a feature request in my opinion, unless one can point out where we make a mistake by taking a lock.
対処方法
リトライ処理を追加する
デッドロック発生時の一般的な対処方法としてリトライ処理が上げられます。
シンプルな処理なのでデッドロックの発生頻度が低いのであれば有効ですが、高頻度で発生するようであればパフォーマンス面で難があると考えられます。
クエリを分割する
以下３通りありますが、クエリを分割することで対処可能です。
一行づつINSERTする
シンプルですが、大量にレコードが存在する場合にはパフォーマンスに難があると思われます。
BEGIN; INSERT new row1; INSERT new row2; INSERT new row3; COMMIT;
UPDATEとINSERTを分ける
前述のINSERTよりは若干処理が煩雑ですが、大量のレコードの挿入が発生するなら、こちらの方がパフォーマンスは優れていると考えられます。
BEGIN; SELECT rows that conflict on secondary indexes; UPDATE conflicting rows; INSERT new rows; COMMIT;
重複レコードをDELETE後にINSERTを行う
前述のUPDATEとINSERTを分ける方法よりは、サーバー側の処理がシンプルに実装できるかもしれません。
但しAUTO_INCREMENTだとIDがどんどん採番されるのと、削除によるデータ喪失のリスクを考慮すると、前述のUPDATEとINSERTを分ける方法の方が無難かもしれません。
またDELETE文でWHERE句を利用した絞り込みを行う場合、条件に該当するレコードが存在しない場合はテーブルのすべての行がロック（ギャップロック）されてしまい、今度はそれがデッドロックの原因となり得るので注意が必要です。
BEGIN; DELETE conflicting rows; INSERT new rows; COMMIT;
トランザクション分離レベルをREAD COMMITTEDに変更する
インテンションロックはギャップロックの一種ですが、READ COMMITTEDにするとファントムリード（ファジーリード）を許容するため、ギャップロックは取得されずにデッドロックが発生しなくなります。
ファントムリード（ファジーリード）が許容できるのであれば、こちらの方法も良いかもしれません。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 15.7.4 ファントム行
テーブルロックする
アクセス頻度が低いテーブルなら良いかもしれないです。
但しアクセス頻度が高いテーブルであれば、待ち状態が頻繁に発生するので、パフォーマンス面で難があります。
今回のようなデッドロックが発生していて対策を要する状況であれば、既に一定のアクセス頻度が想定されるので、あまり良い選択肢では無いかもしれません。
サーバー側で並列ではなく直列にクエリが実行されるように制御する
無理に並列実行する必要が無いような時などは、単一プロセスで動かすようにして対処するのも方法かもしれません。
但しサーバー側で排他制御の仕組みを導入したりしてまで、サーバー側での制御に固執する必要は無いと思われます。
諦める（デッドロックを許容）
発生頻度が非常に少なく、ユーザー側で再実行が容易に出来たりするのであれば、あえて対策せず許容するのも選択肢として考慮しても良いと考えられます。
まとめ
- リトライする
- 一行づつINSERTする
- メリット：実装がシンプル
- デメリット：大量のレコードをINSERTする場合はパフォーマンスに難がある
- 採用シーン：INSERTするレコードが少数の場合
- UPDATEとINSERTを分ける
- メリット：DELETE後にINSERTを行うよりパフォーマンス・安全性ともに有利
- デメリット：リトライや一行づつINSERTに比べると実装コストが高い。
- 採用シーン：トランザクション分離レベルの変更が難しく、リトライで対処困難な場合
- 重複レコードをDELETE後にINSERTを行う
- メリット：UPDATE/INSERTに比べると実装がシンプルになりそう
- デメリット：AUTO_INCREMENT使用中は連番が歯抜けになる。データ喪失のリスク。ギャップロックが発生する可能性あり。
- 採用シーン：上記デメリットが許容できて、極力実装コストを下げたい場合（UPDATEとINSERTを分ける場合との比較）
- トランザクション分離レベルを
READ COMMITTEDに変更する - テーブルロックする
- メリット：別要因で発生するデッドロックも抑制できそう
- デメリット：アクセス頻度が多いテーブルだとパフォーマンスに悪影響を及ぼす
- 採用シーン：テーブルへのアクセス頻度が低い場合
- サーバー側で並列ではなく直列にクエリが実行されるように制御する
- メリット：大きな改修が不要
- デメリット：並列処理では無くなるので想定したパフォーマンスを出せなくなるかもしれない
- 採用シーン：単一プロセスでの実行が許容できる場合
- 諦める（デッドロックを許容）
- メリット：改修不要
- デメリット：何も解決しない
- 採用シーン：解決する必要が無い場合
