はじめに
タイトル通り、MySQL5.x系だとデッドロックになるんだけど、 MySQL8.0だとロック機構が変わってデッドロックにならないよ という組み合わせのお話。
僕はこの話をどっかで見た記憶が無かった（忘れた？）ので 教えてもらったとき結構驚いたんだけど、GA（8.0.11）では 既にこれがあったんで、まあ古い話なんだと思うし、 何をイマサラなのかもしれないので、ご存じの方は笑ってやってください。
ちなみに、REPEATABLE READでのお話なので、 READ COMMITTEDの場合は5系でもデッドロックにはなりまへん。
検証
今回やるのはこういうこと
- 最初のトランザクション（以下Tx1）でトランザクション開始、共有（S）ロックで全レコードを参照。
- 別のトランザクション（以下Tx2）でトランザクション開始、INSERTによる排他（X）ロック
- Tx1でINSERTによる排他（X）ロック
結論を先にいうと、3による結果がMySQL5.xではデッドロック発生（Tx1のINSERT死亡）、 MySQL8.0ではすんなりTx1のINSERTが通る（Tx2は待ちのまま）。
以下は自分で試してみたい人・どういうふうになるのか知りたい人のため。
準備
MySQL5.7(今回は.29を使いました)とMySQL8.0.11で以下のようなテーブルを作成する。
データベース作成
とりあえずいつものtestスキーマｗ
(root@127.0.0.1) [(none)]> create database test; Query OK, 1 row affected (0.01 sec)
testにログイン
(root@127.0.0.1) [(none)]> use test; Database changed
テーブル作成
とりあえずのtテーブル（とりあえずすぎるネーミング）
(root@127.0.0.1) [test]> CREATE TABLE `t` (
-> `id` int NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.08 sec)
1行インサート
(root@127.0.0.1) [test]> insert t select null; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 (root@127.0.0.1) [test]> select * from t; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec)
ここまでで準備はOK
MySQL5.x系（デッドロックになるパターン）
Tx1で共有ロック
$ mysql -uroot -pxxxxxx -h127.0.0.1 -P5729 test mysql: [Warning] Using a password on the command line interface can be insecure. … Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (root@127.0.0.1) [test]> begin; select * from t where id >=1 lock in share mode; Query OK, 0 rows affected (0.00 sec) +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec)
Tx2で排他ロック
$ mysql -uroot -pxxxxxx -h127.0.0.1 -P5729 test mysql: [Warning] Using a password on the command line interface can be insecure. … Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (root@127.0.0.1) [test]> begin; insert into t select null; Query OK, 0 rows affected (0.00 sec)
beginは通ったが、insertは待たされている。
Tx1でINSERTするとデッドロック発生
違和感はあるものの、MySQLのRRでのロックから考えると納得はする。（MySQLにマヒしてる？）
(root@127.0.0.1) [test]> insert into t select null; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
とはいえ、別トランザクションでロックかけられて、自分のトランザクションで知らずにデッドロックとかやはり悲しいものがある。
それがMySQL8.0では改善していた！
MySQL8.0系（デッドロックにならないパターン）
Tx1で共有ロック
$ mysql -uroot -pxxxxx -h127.0.0.1 -P8011 test mysql: [Warning] Using a password on the command line interface can be insecure. … Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (root@127.0.0.1) [test]> begin; select * from t where id >=1 lock in share mode; Query OK, 0 rows affected (0.00 sec) +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec)
Tx2で排他ロック
$ mysql -uroot -pxxxxx -h127.0.0.1 -P8011 test mysql: [Warning] Using a password on the command line interface can be insecure. … Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (root@127.0.0.1) [test]> begin; insert into t select null; Query OK, 0 rows affected (0.00 sec)
beginは通ったが、insertは待たされている。 ここまで全く同じだが、次が違う
Tx1ではTx2の排他ロックを気にしない
(root@127.0.0.1) [test]> insert t select null; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 (root@127.0.0.1) [test]> select * from t; +----+ | id | +----+ | 1 | | 3 | +----+ 2 rows in set (0.00 sec)
でも、ちゃんと「id=2」はTx2が抑えているのを考慮している！
Tx2は待ち疲れでタイムアウトｗ
でも、Tx1がいつまでもコミットしてくれないと、Tx2はタイムアウトしちゃいますｗ
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MySQL8.0のロック、だいぶ賢い！
逆に変わりすぎてて、こことかに明記されててもおかしくないと思ったんだが…。
追記（変わった理由）
この記事書いたとき気づいてなかったんだけど、innodb_autoinc_lock_modeがデフォルト値1から2に変わったことが理由ですね。
パラメータの差異確認して、かつtom__boさんのWeeklyのコメントも見ておそらくこれやなと思ってたけど、検証するのサボってましたｗ
innodb_autoinc_lock_modeについては第49回のMySQL道普請を参考にすると良いです。
MySQL5.7の段階でbinlog-formatがROWに変わってるのでinnodb_autoinc_lock_modeのデフォルトも変わってても良かった気がする。
