【MySQL(InnoDB)】ギャップロックによるデッドロックの調査

はじめに

業務でデッドロックが発生し、原因がわからずに時間を費やしてしまいました。そこで、今回はその原因と解決方法について調査した結果を備忘録としてまとめたいと思います。

状況

実際のテーブルやアプリケーションコードを直接お見せするわけにはいかないので、簡易テーブルを用意して、SQLにて当時の状況を再現してみたいと思います。

テーブル内容

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255)
);

CREATE TABLE rooms (
  id INTEGER PRIMARY KEY AUTO_INCREMENT
);

CREATE TABLE entries (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  room_id INTEGER,
  user_id INTEGER,
  FOREIGN KEY (room_id) REFERENCES rooms(id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  UNIQUE KEY (room_id, user_id)
);

INSERT INTO users (name) VALUES ('User1'), ('User2'), ('User3'), ('User4'), ('User5'), ('User6'), ('User7'), ('User8'), ('User9'), ('User10');

INSERT INTO rooms VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

mysql> select * from users;
+----+--------+
| id | name   |
+----+--------+
|  1 | User1  |
|  2 | User2  |
|  3 | User3  |
|  4 | User4  |
|  5 | User5  |
|  6 | User6  |
|  7 | User7  |
|  8 | User8  |
|  9 | User9  |
| 10 | User10 |
+----+--------+
10 rows in set (0.04 sec)

mysql> select * from rooms;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+
10 rows in set (0.02 sec)

select * from entries;
+----+---------+---------+
| id | room_id | user_id |
+----+---------+---------+
|  1 |       1 |       1 |
|  2 |       2 |       2 |
|  3 |       3 |       3 |
|  4 |       4 |       4 |
|  5 |       5 |       5 |
+----+---------+---------+
5 rows in set (0.01 sec)

操作内容

トランザクションA

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

mysql> delete from entries where room_id = 6;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into entries (room_id, user_id) values (6, 6);
Query OK, 1 row affected (14.86 sec)

トランザクションB

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

mysql> delete from entries where room_id = 7;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO entries (room_id, user_id) VALUES (7, 7);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

状況

	トランザクションA	トランザクションB
	begin;
		begin;
①	delete from entries where room_id = 6;
②		delete from entries where room_id = 7;
③	insert into entries (room_id, user_id) values (6, 6);		待機
④		insert into entries (room_id, user_id) values (7, 7);	トランザクションBにてデッドロックエラーが発生、トランザクションAの待機が解除

トランザクションAにてレコードを挿入しようとしたら、待機され、その後トランザクションBにレコードを挿入した後エラーが発生し、トランザクションAのレコード挿入の待ちが解除されました。

なぜこのような事象が発生したのか調査してみます。

調査結果

① のクエリが空振りした時点でのロックの状態です。

mysql> SELECT OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks\G;
*************************** 1. row ***************************
OBJECT_NAME: entries
  LOCK_TYPE: TABLE
  LOCK_MODE: IX
  LOCK_DATA: NULL
*************************** 2. row ***************************
OBJECT_NAME: entries
  LOCK_TYPE: RECORD
  LOCK_MODE: X
  LOCK_DATA: supremum pseudo-record
2 rows in set (0.01 sec)

注目すべきは２行目。
entries テーブルの最新レコードに排他ロック(Xロック)がかかっていることがわかります。
ギャップロックがかかっていそうですね。

ギャップロックとは

ギャップロックは、インデックスレコード間のギャップのロック、または最初のインデックスレコードの前または最後のインデックスレコードの後のギャップのロックです。 たとえば、SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;では、範囲内の既存のすべての値間のギャップがロックされているため、カラムにそのような値がすでに存在するかどうかにかかわらず、他のトランザクションが 15 の値をカラム t.c1 に挿入できなくなります。

ギャップは、単一のインデックス値、複数のインデックス値にまたがることも、空にすることもできます。
https://dev.mysql.com/doc/refman/8.0/ja/innodb-locking.html#innodb-gap-locks

room_id は外部キー制約を設定しているので、インデックスが貼られています。
空振りした場合は entries テーブルに対してギャップロックがかかるようです。

ちなみに① のクエリで削除レコードが存在する場合は、ギャップロックはかかっていないようでした。

SELECT OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks\G;
*************************** 1. row ***************************
OBJECT_NAME: entries
  LOCK_TYPE: TABLE
  LOCK_MODE: IX
  LOCK_DATA: NULL
*************************** 2. row ***************************
OBJECT_NAME: entries
  LOCK_TYPE: RECORD
  LOCK_MODE: X,REC_NOT_GAP
  LOCK_DATA: 5
2 rows in set (0.01 sec)

②のクエリ実行直後。

mysql> SELECT OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks\G;
*************************** 1. row ***************************
OBJECT_NAME: entries
  LOCK_TYPE: TABLE
  LOCK_MODE: IX
  LOCK_DATA: NULL
*************************** 2. row ***************************
OBJECT_NAME: entries
  LOCK_TYPE: RECORD
  LOCK_MODE: X
  LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
OBJECT_NAME: entries
  LOCK_TYPE: TABLE
  LOCK_MODE: IX
  LOCK_DATA: NULL
*************************** 4. row ***************************
OBJECT_NAME: entries
  LOCK_TYPE: RECORD
  LOCK_MODE: X
  LOCK_DATA: supremum pseudo-record

③のクエリ実行直後。

mysql> SELECT OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks\G;
*************************** 1. row ***************************
OBJECT_NAME: entries
  LOCK_TYPE: TABLE
  LOCK_MODE: IX
  LOCK_DATA: NULL
*************************** 2. row ***************************
OBJECT_NAME: entries
  LOCK_TYPE: RECORD
  LOCK_MODE: X
  LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
OBJECT_NAME: rooms
  LOCK_TYPE: TABLE
  LOCK_MODE: IS
  LOCK_DATA: NULL
*************************** 4. row ***************************
OBJECT_NAME: entries
  LOCK_TYPE: TABLE
  LOCK_MODE: IX
  LOCK_DATA: NULL
*************************** 5. row ***************************
OBJECT_NAME: entries
  LOCK_TYPE: RECORD
  LOCK_MODE: X
  LOCK_DATA: supremum pseudo-record
*************************** 6. row ***************************
OBJECT_NAME: entries
  LOCK_TYPE: RECORD
  LOCK_MODE: X,GAP
  LOCK_DATA: 11
*************************** 7. row ***************************
OBJECT_NAME: rooms
  LOCK_TYPE: RECORD
  LOCK_MODE: S,REC_NOT_GAP
  LOCK_DATA: 6
*************************** 8. row ***************************
OBJECT_NAME: entries
  LOCK_TYPE: RECORD
  LOCK_MODE: X,INSERT_INTENTION
  LOCK_DATA: supremum pseudo-record
8 rows in set (0.01 sec)

注目すべきは８行目。
インサートインテンションロックが取得されていますね。

インサートインテンションロックとは

挿入意図ロックは、行の挿入前に INSERT 操作によって設定されるギャップロックのタイプです。 このロックは、同じインデックスギャップに挿入する複数のトランザクションは、そのギャップ内の同じ場所に挿入しなければ相互に待機する必要がないように、意図的に挿入することを示しています。 値が 4 と 7 のインデックスレコードが存在すると仮定します。 5 と 6 の値をそれぞれ挿入しようとする個別のトランザクションでは、挿入された行の排他ロックを取得する前に、挿入意図ロックを使用して 4 と 7 のギャップがロックされますが、行が競合していないため相互にブロックされません。

次の例は、挿入されたレコードの排他ロックを取得する前に挿入意図ロックを取得するトランザクションを示しています。 この例には、A と B の 2 つのクライアントが登場します。

クライアント A は、2 つのインデックスレコード (90 および 102) を含むテーブルを作成し、100 を超える ID を持つインデックスレコードに排他ロックを設定するトランザクションを開始します。 排他ロックには、レコード 102 の前にギャップロックが含まれます:

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

クライアント B はトランザクションを開始して、ギャップにレコードを挿入します。 トランザクションは、排他ロックの取得を待機している間、挿入意図ロックを取得します。

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

https://dev.mysql.com/doc/refman/8.0/ja/innodb-locking.html#innodb-intention-locks

②のクエリで排他ロック(Xロック)をすでに取得しているため、「排他ロックの取得を待機している間」でインテンションインサートロックをしていますね。

この状態で④のクエリを実行すると、デッドロックエラーが発生します。
インテンションロックの仕様のようですね。

ロックに既存のロックとの互換性がある場合は、リクエスト元のトランザクションにロックが付与されますが、既存のロックと競合している場合は、ロックが付与されません。 トランザクションは、競合している既存のロックが解放されるまで待機します。 ロックリクエストが既存のロックと競合し、デッドロックが発生するために付与できない場合は、エラーが発生します。

https://dev.mysql.com/doc/refman/8.0/ja/innodb-locking.html#innodb-intention-locks

解決策

今回は、削除処理を実行する際に、レコードが存在しているかどうかにかかわらず、無条件で「delete」していたため、削除対象のレコードが存在する場合のみ「delete」するように変更しました。