InnoDBでは、以下4つのトランザクション分離レベルが提供されます。 デフォルトの分離レベルはREPEATABLE READです。

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

InnoDBは、これらのトランザクション分離レベルを異なるロックの方法を使用してサポートしています。今回は、この中でREAD COMMITTEDにおけるロックの挙動について紹介したいと思います。

REPEATABLE READ

まずは、InnoDBのデフォルトであるREPEATABLE READについて簡単に確認します。

REPEATABLE READはトランザクション開始後の最初の読み取りでスナップショットを確立します。同時に実行されている他のトランザクションによって実行された変更に関係なく、トランザクション内では最初に取ったスナップショットから読み取りが行わえます。つまり、同じトランザクション内でロッキングリード以外のSELECTステートメントを発行すると、他のトランザクションがコミットした更新に影響なく一貫性が保たれます。

ネクストキーロックやギャップロックを使用して、走査したインデックス範囲、インデックスレコードと最初のインデックスレコードの前や最後のインデックスレコードの後のギャップをロックします。そうすることで、ギャップに対する他のトランザクションからの挿入をブロックします。一意キーに対する更新ではこれらのロックは取られません。

READ COMMITTED

READ COMMITTEDは、同じトランザクション内であっても独自の新しいスナップショットを設定して読み取ります。インデックスレコードのみをロックし、その前のギャップはロックしないため、ロックされたレコードの横に新しいレコードを自由に挿入できます。前述のネクストキーロックやギャップロックが無効化されます。

READ COMMITTEDでのロックの挙動の注意点

先ほど、READ COMMITTEDではネクストキーロックやギャップロックが無効化されていると説明しました。しかし、注意の必要な点があります。それは、READ COMMITTEDでは、ステートメント実行中は走査した行のロックを取得し、必要なロックだけ残してその他を開放するという挙動となっていることです。

どのようなロックを取得する、以下のようなテーブルとデータを用意して説明します。

mysql> CREATE TABLE `test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `t` char(1) DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
 
mysql> INSERT INTO test  (t) values ('a'),('a'),('b'),('b'),('c');
 
 
mysql> select * from test;
+----+------+
| id | t    |
+----+------+
|  1 | a    |
|  2 | a    |
|  3 | b    |
|  4 | b    |
|  5 | c    |
+----+------+

REPEATABLE READの場合は上表のNo.5でトランザクション2のステートメントは待機状態となります。それは、No.4でインデックスのないカラムに対するDELETEステートメントをトランザクション1が実行しているため、ネクストキーロックとギャップロックにより全ての行と最後のインデックスレコードの後のギャップをロックするからです。これによりNo.5が待機しています。

innodb_status_output_locksオプションがONであれば、以下のようにSHOW ENGINE INNODB STATUSから保有しているロックを確認することができます。すべての行とsupremumの表示から、最後のインデックスレコードの後のギャップのロックが取得されているのがわかります。

TABLE LOCK table `sysbenchdb`.`test` trx id 85590847 lock mode IX
RECORD LOCKS space id 37424 page no 3 n bits 72 index id of table `sysbenchdb`.`test` trx id 85590847 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
 
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 0000000000000001; asc         ;;
 1: len 6; hex 0000051a0337; asc      7;;
 2: len 7; hex c10000084a0110; asc     J  ;;
 3: len 1; hex 61; asc a;;
 
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 0000000000000002; asc         ;;
 1: len 6; hex 0000051a0337; asc      7;;
 2: len 7; hex c10000084a0122; asc     J ";;
 3: len 1; hex 61; asc a;;
 
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 8; hex 0000000000000003; asc         ;;
 1: len 6; hex 0000051a033f; asc      ?;;
 2: len 7; hex 470000401b1763; asc G  @  c;;
 3: len 1; hex 62; asc b;;
 
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 8; hex 0000000000000004; asc         ;;
 1: len 6; hex 0000051a033f; asc      ?;;
 2: len 7; hex 470000401b178f; asc G  @   ;;
 3: len 1; hex 62; asc b;;
 
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 0000000000000005; asc         ;;
 1: len 6; hex 0000051a0337; asc      7;;
 2: len 7; hex c10000084a0158; asc     J X;;
 3: len 1; hex 63; asc c;;

READ COMMITTEDの場合も、No.5でトランザクション2のステートメントは待機されます。SHOW ENGINE INNODB STATUSから確認すると、No.4の対象行（ID3,4）のインデックスレコードのみをロックしています。READ COMMITTEDはインデックスレコードのみをロックするため、REPEATABLE READのときとは挙動が違います。

TABLE LOCK table `sysbenchdb`.`test` trx id 85590856 lock mode IX
RECORD LOCKS space id 37424 page no 3 n bits 72 index id of table `sysbenchdb`.`test` trx id 85590856 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 8; hex 0000000000000003; asc         ;;
 1: len 6; hex 0000051a0348; asc      H;;
 2: len 7; hex 4d0000402c1c5b; asc M  @, [;;
 3: len 1; hex 62; asc b;;
 
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 8; hex 0000000000000004; asc         ;;
 1: len 6; hex 0000051a0348; asc      H;;
 2: len 7; hex 4d0000402c1c87; asc M  @,  ;;
 3: len 1; hex 62; asc b;;

No.5はNo.4にてロックが取得された行をDELETEしませんが、では、なぜロック待機が発生するのでしょうか。それは、READ COMMITTEDでははじめに走査した行のロックを取得して、その後に必要なロックを残して不要なロックは開放するという挙動になっているためです。そのため、id=3の行でロックの競合が起こり、待機することになるのです。

このように、READ COMMITTEDはインデックスレコードのみをロックしますが、ステートメント実行中は走査した行もロックを取得するので、その挙動は覚えておいたほうが良いでしょう。

まとめ

InnoDBにおけるトランザクション分離レベル READ COMMITTEDでのロックの挙動について紹介しました。

READ COMMITTEDはインデックスレコードのみをロックしますが、走査した行のロックを試みて、その後に不要なロックを開放するという挙動になっています。OracleのREAD COMMITTEDの挙動とは異なっているのでご注意ください。必要なデータのみ走査するようにインデックス設計をしたほうがよいでしょう。

今回は15.7.2.1 トランザクション分離レベル を基に記事を作成しましたので、詳しく知りたい方はこちらをご確認ください。