この記事はMySQL Advent Calendar 2023 23日目の記事です。
はじめに
MySQLのバージョンアップをした際に、今までデッドロックが起きなかったクエリでデッドロックが頻発するようになり、調べてみたところ下記のバグ報告（ページはバグ報告のものですがこの挙動自体はバグではなくて意図通りのものと説明されています）を見つけ記載されている回避策を試してみたところデッドロックの解消も出来たので日本語での情報としてだいぶ簡潔ではありますがまとめさせていただきます。
（そのため詳しい情報や正確な情報を知りたい際には下記のリンク先を確認していただければと思います）
また、英語も得意ではないのとMySQLの実装も理解しているわけではないので間違っていることを書いていたらコメントなどで指摘していただけると助かります 
デッドロックが起きる例
下記のようにAUTO INCREMENTのPRIMARY KEYとUNIQUE KEYを持つテーブルがあるとします。
CREATE TABLE `user` (
`id` int AUTO_INCREMENT,
`user_id` int,
`value` int,
PRIMARY KEY (`id`),
UNIQUE KEY (`user_id`)
)
このテーブルに対して、下記のようなON DUPLICATE KEY UPDATEによるクエリや
INSERT INTO `user` (`user_id`, `value`) VALUES (?, ?), (?, ?)
ON DUPLICATE KEY UPDATE `value` = VALUES (`value`)
下記のようなREPLACE INTOクエリを実行するとクエリの実行頻度などによってはPKでデッドロックが発生しました。
REPLACE INTO `user` SET id = NULL, user_id = ?, value = ?;
ちなみに、このときにデッドロックが起きる条件として新規INSERTの場合や同じUNIQUE KEYに対して更新しようとしているみたいな条件はなく、
| id | user_id | value |
|---|---|---|
| 1 | 1001 | 1 |
| 2 | 1002 | 2 |
| 3 | 1003 | 3 |
| 4 | 1004 | 4 |
上記のようなレコードが存在する状態で下記のようなクエリを別々のトランザクションから発行された際にもデッドロックが起きる可能性があります。
-- トランザクション1
INSERT INTO `user` (`user_id`, `value`) VALUES (1001, 10), (1002, 20)
ON DUPLICATE KEY UPDATE `value` = VALUES (`value`)
-- トランザクション2
INSERT INTO `user` (`user_id`, `value`) VALUES (1003, 30), (1004, 40)
ON DUPLICATE KEY UPDATE `value` = VALUES (`value`)
回避策
こちらも上記のバグ報告のスレッドに記載されている一部の方法ですが、下記のような方法を試すことで改善する場合があります。
（下記の2つの方法は実際に試してみて改善したところまで確認済みです）
-
ON DUPLICATE KEY UPDATEやREPLACE INTOをやめて複数クエリでの実行に変更する
1.1.ON DUPLICATE KEY UPDATEの場合は、UNIQUE KEYでSELECTしてみてレコードが見つかればUPDATE, 見つからなければINSRTする
1.2.REPLACE INTOの場合は、UNIQUE KEYでレコードを削除してからINSERTする -
idカラムをドロップしてUNIEQUE KEYをPKに変更する
なぜデッドロックが起きるのか
（ここから先は自分でもあまり理解できていない&間違っていることを書いているかもしれないのであまり参考にはしないで何となく読んでいただければと思います）
競合するレコードが存在する状態でINSERT INTO .. ON DUPLICATE KEY UPDATEクエリを実行した際には、内部実装的には下記のように処理されているらしいです。
- テーブルにインテンション排他ロックを取る
- 最初にレコードのINSERTを試みる
1.1. ここではUNIEUQ KEYが一致するレコードがあったとしても無視してINSERTされる
1.2. このタイミングでPKに対して挿入意図ロックも取られる - INSERTしたレコードのUNIQUE KEYが重複するものがあるのかチェックする
- 重複したものがあったらINSERTしたレコードを削除する
4.1. その際に削除したレコードのPKの範囲をロックする
4.2. そのためPKがsupremum pseudo-recordまでギャップロックされる - 競合するレコードに対してUPDATEを実行する
そのため、1トランザクション内で2レコード以上処理するクエリが複数トランザクションで発行されると、タイミングによってはデッドロックが発生するという認識です。
- 各トランザクションで1レコード目を同時に処理し、その結果PKにsupremum pseudo-recordまでのギャップロックが2つのトランザクションから取られる。
- 各トランザクションが2レコード目を同時に処理しようとした際に、PKがsupremum pseudo-recordまでギャップロックを取られているので、どちらのトランザクションもPKの挿入意図ロックを取ろうとするが待ちになりデッドロックになる。
なぜMySQLをアップデートしたら起きるようになったのか
MySQL 5.7.26と8.0.16で下記の修正が入っており、こちらはざっくりいうと多分内部処理の説明をした際の4.1~4.2のPKのギャップロックを取るようにした修正らしいです。
それまではPKのギャップロックを取っていなかったことにより、十分なロックが取られておらず直列化可能性(serializability)が保証出来ていないという重大な問題があったとのことです。
そのため、この挙動はMySQLのバグではなくて意図した挙動とのことです。