トランザクション開始直後、一発目のクエリがデッドロックしてしまいました

mysql> BEGIN;
mysql> SELECT * FROM `member` WHERE `role` > 0 AND `team` = 2 FOR UPDATE;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

ある日遭遇したちょっと不思議な現象です。一体何が起こっているのでしょうか。

なぜこれが不思議なのか

デッドロックは通常、並列で動く二つの処理（トランザクション）が互いに相手の所有するロックを待ち合うことで起こります。例えば、資源aをロックしている処理Aと、資源bをロックしている処理Bが並列で動いている状態で、 Aがbの解放を待っている時にBもaの解放を待ってしまうと、お互い待ち状態から動けなくなってしまいます。

MySQL(InnoDB)ではこのようなデッドロックを検知すると、トランザクションをエラーとして終了し、ロックを解放してくれます。もちろんトランザクション中に行われていた変更は無かったことになります。

今回のケースでは、トランザクション開始直後、つまりまだ何もロックを取得していない状態から、最初のロックを取得しようとしたクエリがなぜかデッドロック検知機構に引っかかっています。不思議ですね！

再現してみる

このデッドロックを再現する最小構成を、具体的なスキーマとクエリの設計から見てみようと思います。なお、実行環境としてDockerのmysql:5.7.20およびmysql:8.0.3のイメージで検証しています。

スキーマの設計

ここにチームのメンバーを管理するテーブルがあります。チーム内の役職も管理できるやつです。

カラム名	概要
id	メンバーのID (主キー)
team	グループのID
role	役職ID (0は役職なし)

チーム内のメンバー全員を取得・操作することがあるのでteamカラムにはインデックスを張っておきます。また、チーム横断で同じ役職の会合も開くのでroleカラムにもインデックスを張りましょう。これをCREATE文にするとこのようになります。

CREATE TABLE `member` (
  `id`   INTEGER NOT NULL,
  `team` INTEGER NOT NULL,
  `role` INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_team` (`team`),
  KEY `idx_role` (`role`)
) Engine=InnoDB;

クエリの設計

あるとき、各チームの役職持ちの中から(なんらかのロジックで)一人選んで特別な役職(role=1000)にすることになりました。これを実行する処理としては次のようになると思います。

特定のチームIDの、roleが0でない人を取得する
ここで他プロセスから取得した人のroleを操作できないように排他ロックを取得する
取得した人から何らかのロジックで一人選ぶ
選ばれた人のroleを変更する
この2つを一つのトランザクションとして処理する

これをそのままSQLで書くとこのようになることでしょう。

BEGIN;
SELECT * FROM `member` WHERE `team` = ? AND `role` > 0 FOR UPDATE;
-- ここで一人選び`id`を調べる
UPDATE `member` SET `role` = 1000 WHERE `id` = ?;
COMMIT;

シンプルな処理ですね。

では動かしてみましょう

まずはメンバーのデータを用意します。

INSERT INTO `member` VALUES
  (1,1,1),(2,1,2),(3,2,1),(4,2,2),(5,2,0),(6,2,0),(7,2,0),(8,2,0),(9,2,0),
  (10,2,0),(11,2,0),(12,2,0),(13,2,0),(14,2,0),(15,2,0),(16,2,0),(17,2,0),
  (18,2,0),(19,2,0),(20,2,0),(21,2,0),(22,2,0),(23,2,0),(24,2,0),(25,2,0);

ターミナルを2つ開き、それぞれをセッションA、セッションBとします。セッションAではteam = 1に対して、セッションBではteam = 2に対して並列に処理を実行していきましょう。

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `member` WHERE `team` = 1 AND `role` > 0 FOR UPDATE;
+----+------+------+
| id | team | role |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    1 |    2 |
+----+------+------+
2 rows in set (0.00 sec)

mysql>

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `member` WHERE `team` = 2 AND `role` > 0 FOR UPDATE;
 -- 待たされている --

セッションAで取得できたメンバーのうち、id = 1の人を特別な役職にします。

mysql> UPDATE `member` SET `role` = 1000 WHERE `id` = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
 -- !? --

セッションAでUPDATE文を投げてからセッションBに戻ってみると、待たされていたクエリがデッドロックでエラー終了していました。

なぜこんなことになったのか

そもそもどういう範囲のロックをとっているか調べてみましょう。

MySQL(InnoDB)は行ロックを取得するときにインデックスを利用しています。なのでまずは、SELECT文の実行計画を見て、どのインデックスが使われるのか確認しましょう。

mysql> EXPLAIN SELECT * FROM `member` WHERE `team` = 1 AND `role` > 0 FOR UPDATE;
+----+-------------+--------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys     | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | member | NULL       | ref  | idx_team,idx_role | idx_team | 4       | const |    2 |    20.00 | Using where |
+----+-------------+--------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM `member` WHERE `team` = 2 AND `role` > 0 FOR UPDATE;
+----+-------------+--------+------------+-------+-------------------+----------+---------+------+------+----------+------------------------------------+
| id | select_type | table  | partitions | type  | possible_keys     | key      | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+--------+------------+-------+-------------------+----------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | member | NULL       | range | idx_team,idx_role | idx_role | 4       | NULL |    5 |    92.00 | Using index condition; Using where |
+----+-------------+--------+------------+-------+-------------------+----------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

team = 1のクエリではidx_teamが使われているのに対し、 team = 2ではidx_roleが使われていることがわかりました。

設計段階での想定では、team = ?の指定によりidx_teamが使われ、チーム単位でロックされることを期待していました。ところが、このクエリ自体はidx_roleもpossible keysに出てきており、オプティマイザのさじ加減でidx_roleも使われうる状態でした。

セッションBのクエリは実際にidx_roleが使われていたため、想定外のロック待ちが発生していただけでなく、ロック待ち中にroleカラムが変更されてしまったことで整合性が取れない状態におちいっていたようです。

なぜこの状態が”デッドロック”として検出されるのかまではまだ追えていませんが、詳しい方ぜひ解説をお願いします。。。

回避するためには

ロックを取得するクエリで使われるインデックスを固定しておくことが鉄則です。それを実現する方法をいくつか挙げてみます。

roleでのフィルタをクエリに含めず、特別役職選出ロジック側でフィルタする
クエリにFORCE INDEXヒントを付けて使われるインデックスを固定する
idx_roleを貼らず、チーム横断の役職検索は別の方法で実現する
役職持ちメンバの取得ではロックを貼らず、選出後にあらためて
主キーでロックして整合性を確認してから役職を変更する

おわりに

検索のパフォーマンスチューニングで語られることが多いMySQLのインデックスですが、ロックにも使われるので設計するときは注意しましょう。