はじめに
最近は主に花粉症に悩まされており、目が痒くてたまりません。
また、娘の生活がガラッと変わったせいで、毎日貧乏ヒマ無しです。
そんな中、たまたま早起きできたので奮起して久々に書いてみました。
問題が起きる環境
- MySQL8.0.17以前
- transaction_isolationがREAD-COMMITTED
- WHERE句の条件が一意ではない。（フルテーブルスキャンだと発生しやすくなる）
- キーの値がたすきがけになってる
- トランザクション開始＋SELECT ...FOR UPDATE→UPDATEのようにロックを取っている
- 先に実行されたトランザクションが、たすきがけになっているキー値の若い（っていうのかな？）方のロックを取る
何が起きるかと言うと、SELECT ...FOR UPDATEのWHERE句で抽出した行に対してロックを取ってるのに、 後から別セッションで実行されたSELECT ...FOR UPDATEのせいで、先取りしたほうがデッドロックして負けるというお話。
これ、世の中のシステムで意外と起きてそうだなーと思ったが、文章にするとわかりづらいので、具体例を記載しながら説明する。
詳細
準備
１．まず、こんなテストテーブルを構築。
mysql> use test; mysql> show create table tu\G *************************** 1. row *************************** Table: tu Create Table: CREATE TABLE `tu` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` varchar(10) DEFAULT NULL, `c` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `u` (`a`,`b`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
２．データはこちら
mysql> INSERT INTO tu VALUSE(1,1,'a','a'),(2,2,'c','2'),(3,3,'b','3'),(4,4,'d','4'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM tu; +----+------+------+------+ | id | a | b | c | +----+------+------+------+ | 1 | 1 | a | a | | 2 | 2 | c | 2 | | 3 | 3 | b | 3 | | 4 | 4 | d | 4 | +----+------+------+------+ 4 rows in set (0.00 sec)
キーはPKがid、UKがa,bで作られ、データはbカラムのところでid=2と3で入れ子にしている。
（PKはaカラムとbカラムの組み合わせでもOK）
検証
１． １つ目のトランザクションでSELECT...FOR UPDATEを行う。
３行目のデータを更新対象に見据えて、SELECT...FOR UPDATE文を実行する。
mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM tu WHERE c = '3' FOR UPDATE; +----+------+------+------+ | id | a | b | c | +----+------+------+------+ | 3 | 3 | b | 3 | +----+------+------+------+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT * FROM tu WHERE c = '3' FOR UPDATE; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tu | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using WHERE | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
ここではWHERE句の条件をインデックスを使わず、フルテーブルスキャンになるようにしている。
この時、どのようなロックが取得されているかと言うと、以下の通り。
> SELECT * FROM performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140229171137104:1061:140229071664344 ENGINE_TRANSACTION_ID: 3593 THREAD_ID: 47 EVENT_ID: 13 OBJECT_SCHEMA: test OBJECT_NAME: tu PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140229071664344 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140229171137104:4:4:7:140229071661352 ENGINE_TRANSACTION_ID: 3593 THREAD_ID: 47 EVENT_ID: 13 OBJECT_SCHEMA: test OBJECT_NAME: tu PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140229071661352 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 3 2 rows in set (0.00 sec)
テーブルに対し、インサートインテンションロック、対象行に対し、排他ロックがかかる。
（ちなみに、MySQL5.7以前だとperformance_schema.data_locksは無い）
２． ２つ目のトランザクションでもSELECT...FOR UPDATEを行う。
mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM tu WHERE c = '2' FOR UPDATE;
待たされる。
この時、どのようなロックが取得されているかと言うと、以下の通り。
mysql> SELECT * FROM performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140229171137968:1061:140229071670264 ENGINE_TRANSACTION_ID: 3595 THREAD_ID: 48 EVENT_ID: 16 OBJECT_SCHEMA: test OBJECT_NAME: tu PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140229071670264 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140229171137968:4:4:3:140229071667384 ENGINE_TRANSACTION_ID: 3595 THREAD_ID: 48 EVENT_ID: 16 OBJECT_SCHEMA: test OBJECT_NAME: tu PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140229071667384 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140229171137968:4:4:7:140229071667728 ENGINE_TRANSACTION_ID: 3595 THREAD_ID: 48 EVENT_ID: 16 OBJECT_SCHEMA: test OBJECT_NAME: tu PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140229071667728 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: WAITING LOCK_DATA: 3 *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140229171137104:1061:140229071664344 ENGINE_TRANSACTION_ID: 3593 THREAD_ID: 47 EVENT_ID: 13 OBJECT_SCHEMA: test OBJECT_NAME: tu PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140229071664344 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 5. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140229171137104:4:4:7:140229071661352 ENGINE_TRANSACTION_ID: 3593 THREAD_ID: 47 EVENT_ID: 13 OBJECT_SCHEMA: test OBJECT_NAME: tu PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140229071661352 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 3 5 rows in set (0.00 sec)
上の３つが２つ目のトランザクションによるロック。
"3. row" のロックが LOCK_STATUS: WAITING となっているのでこれにより待たされていることがわかる。
これだけを見ると、１つ目のトランザクションでは正常に後続のUPDATE文が通りそうなもんであるが、しかし・・・
３． １つ目のトランザクションでUPDATEが失敗する
mysql> UPDATE tu SET c='c2' WHERE c = '3'; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
ちなみに上記のデッドロック、もし２つ目のトランザクションの条件（where c = '2'）を先に実行した場合はデッドロックが発生しない。
原因
リリースノートにあるコレ
InnoDB: A deadlock was possible when a transaction tries to upgrade a record lock to a next key lock. (Bug #23755664, Bug #82127)
解決方法
3つあって
- MySQL8.0.18以降にする
- transaction_isolationをREPEATABLE-READにする
- 更新対象を一意にする
根治するならバージョンアップ一択なんですが、バージョンいきなりやれって言われてもツライと思うし、
transaction_isolation変えるなんて、それもまたシステムへの影響多いだろうから、
まずは更新対象をできるだけ絞る、可能なら一意にするってところから始めてくれればかと。
もし、EXPLAIN取ってtypeがALLなんて出てたら、それを潰すところからですね。
インデックス張りましょう。抽出対象を絞れるようにしましょう。
最後に
そういえば、２月１５日で勤続１年になりました。
チームのメンバー、イルカさん関係者の皆さん、外部のイルカ大好きな皆様、そしてお客様に恵まれ、非常にやりがいもあり成長させていただき、充実した一年でした。
まだまだ力不足でご迷惑をおかけすることもあるかもしれませんが、今後ともよろしくお願いします。
