【MySQL】delete、updateの空振りのギャップロックに注意

ギャップロックとは

インデックスレコード間にあるギャップのロック、または先頭のインデックスレコードの前や末尾のインデックスレコードのあとにあるギャップのロックです。¹

ギャップロックは明示的に無効化できます。これは、トランザクション分離レベルを READ COMMITTED に変更するか、または innodb_locks_unsafe_for_binlog システム変数 (現在は非推奨です) を有効にすると発生します。²

通常はレコードロックで単一の行にのみロックがかかるが、範囲指定や存在しないキーに対してロックを取得しようとしたときに存在しないレコード(ギャップ)にもロックを取得します。

例えば、companiesテーブルにid(PK)が1~10のデータが存在しているとして、id=10に対してトランザクションを張ってデータを削除してみます。

-- sample sql
BEGIN;
DELETE FROM `companies` WHERE `companies`.`id` = 10;

そうすると、ロック状態は以下のようになります。

着目するのは、2行目のLOCK_TYPE、LOCK_MODE、LOCK_DATA

• LOCK_TYPEはRECORD
• LOCK_MODEはX,REC_NOT_GAP(ギャップロックではないという情報)
• LOCK_DATAは10(ロックしている行のPK)

となっており、レコードロックになっていることがわかります。 1行目はインテンションロックなのでここでは気にしない

次に存在しないレコードに対して削除処理を行ってみます。

-- sample sql
BEGIN;
DELETE FROM `companies` WHERE `companies`.`id` = 9999999;

そうすると、ロック状態は以下のようになります。

同じく2行目のLOCK_TYPE、LOCK_MODE、LOCK_DATAに着目する

• LOCK_TYPEはRECORD
• LOCK_MODEはX(REC_NOT_GAPの情報がなくなった)
• LOCK_DATAはsupremum pseudo-record(これはギャップロックを取得していることを意味する)

となっておりギャップロックを確認できます。 次に実際に起きたギャップロックによる問題を紹介します。

DELETE-INSERTでデッドロック

アプリケーション側でDELETE-INSERTを実行していた時にデッドロックが頻発する問題が発生しました。 サンプルのSQLとしては下記のような処理になります。

-- sample sql
BEGIN;
DELETE FROM `companies` WHERE `companies`.`id` = 20; -- 存在しないデータの削除。
INSERT INTO companies (name, created_at, updated_at) values ('insert', SYSDATE(), SYSDATE());

これを複数のトランザクション(tx1、tx2)で順番に実行します。トランザクションの時系列としては下記のようになります。

1. tx1: トランザクション開始。DELETE実行
2. tx2: トランザクション開始。DELETE実行
3. tx1: INSERT実行
4. tx2: INSERT実行=>Deadlock発生！

上から順番にロックの状況を確認していきます。

1の時のロック状況

ギャップロック取得

2の時のロック状況

別のトランザクションでもギャップロック取得 ロック取得待ちにはならない

さまざまなトランザクションによってギャップ上に競合するロックを保持できることも、ここで注目するべき点です。たとえば、トランザクション A はギャップ上に共有ギャップロック (ギャップ S ロック) を保持できる一方で、トランザクション B は同じギャップ上に排他ギャップロック (ギャップ X ロック) を保持します。 競合するギャップロックが許可される理由は、レコードがインデックスからパージされる場合に、さまざまなトランザクションによってレコード上に保持されたギャップロックをマージする必要があるためです。 InnoDB のギャップロックは、「単に抑制的」です。つまり、ほかのトランザクションによるギャップへの挿入が停止されるだけです。したがって、ギャップ X ロックの効果はギャップ S ロックと同じです。³

3の時のロックの状況

INSERT_INTENTIONのロックを取得 ここでtx2のロック解放待ちになる

この状況でtx2でINSERTを実行すると、tx1はtx2のギャップロックがあるので待ちになり、tx2もtx1のギャップロックがあり待ちになるのでDeadlockになる

まとめ

• トランザクションの分離レベルが「REPEATABLE READ」の場合は、不要なロックを取得しないようにも存在するレコードに対してのみ処理をする必要がある。もしくは、トランザクション分離レベルを「READ COMMITTED」に変更する
• MySQLのInnoDBはデフォルトのトランザクションの分離レベルが「REPEATABLE READ」なので他のDBを使用していた人は要注意。

Footnotes

1. ギャップロックの概要 ↩

2. ギャップロックの無効化 ↩

3. ギャップロックのロックについて ↩