MySQLのREPEATABLE READとREAD COMMITTEDのロック状況をdata_locksから観察する

前回MySQLのREPEATABLE READとREAD COMMITTEDの違いを知るために色々試した - $shibayu36->blog;という記事を書いたところ、yoku0825さんにMySQL 8.0以降だとperformance_schema.data_locksが使えると教えてもらったので試した。

ちなみに、後ろからロックがぶつかるクエリを実行しなくても、MySQL 8.0だとSELECT * FROM performance_schema.data_locksでロックの範囲を確かめることができます。

ギャップつきロックがInnoDBのスタンダードで、X lockがレコードとギャップのロック、X not gapが単なるレコードロックになります
— yoku0825 (@yoku0825) February 27, 2024

テーブル定義

CREATE TABLE `posts` (
  `id` int NOT NULL,
  `title` varchar(255) NOT NULL,
  `body` text NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

両方ともREPEATABLE READ

> select * from posts;
+----+--------+-------+
| id | title  | body  |
+----+--------+-------+
|  1 | title1 | body1 |
|  2 | title2 | body2 |
+----+--------+-------+

# トランザクション1
begin;

# トランザクション2
begin;

# トランザクション1
UPDATE posts SET title = 'title2updated' WHERE id >= 2 and id < 5;

# トランザクション2
insert into posts (id, title, body) values (10, 'title10', 'body10'); # -> lockされる

この時にperformance_schema.data_locksを確認する。

> SELECT ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks where OBJECT_SCHEMA = 'repeatable_read_test';
+-----------------------------------+-----------------------+-------------+------------+-----------------------+-----------+--------------------+-------------+------------------------+
| ENGINE_LOCK_ID                    | ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE          | LOCK_STATUS | LOCK_DATA              |
+-----------------------------------+-----------------------+-------------+------------+-----------------------+-----------+--------------------+-------------+------------------------+
| 275347546352:1579:275213534048    | 12672                 | posts       | <null>     | 275213534048          | TABLE     | IX                 | GRANTED     | <null>                 |
| 275347546352:516:4:1:275213531136 | 12672                 | posts       | id         | 275213531136          | RECORD    | X,INSERT_INTENTION | WAITING     | supremum pseudo-record |
| 275347545496:1579:275213527904    | 12670                 | posts       | <null>     | 275213527904          | TABLE     | IX                 | GRANTED     | <null>                 |
| 275347545496:516:4:4:275213524912 | 12670                 | posts       | id         | 275213524912          | RECORD    | X,REC_NOT_GAP      | GRANTED     | 2                      |
| 275347545496:516:4:1:275213525256 | 12670                 | posts       | id         | 275213525256          | RECORD    | X                  | GRANTED     | supremum pseudo-record |
+-----------------------------------+-----------------------+-------------+------------+-----------------------+-----------+--------------------+-------------+------------------------+

これを観察すると

トランザクション1がテーブルのIXロック、id 2のレコードロック、ギャップロックを保持している
トランザクション2がテーブルのIXロック、INSERTでのギャップロックで待ち状態

という状況を観察できる。確かにREPEATABLE READ時にレコードロック+ギャップロックを取っていることがわかる。

UPDATE側がREAD COMMITTED

# トランザクション1
set session transaction isolation level read committed;
begin;

# トランザクション2
begin;

# トランザクション1
UPDATE posts SET title = 'title2updated' WHERE id >= 2 AND id < 5;

# トランザクション2
insert into posts (id, title, body) values (10, 'title10', 'body10'); # -> lockされない

この時にperformance_schema.data_locksを確認する。

SELECT ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks where OBJECT_SCHEMA = 'repeatable_read_test';
+-----------------------------------+-----------------------+-------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE_LOCK_ID                    | ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------------------+-----------------------+-------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| 275347546352:1579:275213534048    | 12675                 | posts       | <null>     | 275213534048          | TABLE     | IX            | GRANTED     | <null>    |
| 275347545496:1579:275213527904    | 12674                 | posts       | <null>     | 275213527904          | TABLE     | IX            | GRANTED     | <null>    |
| 275347545496:516:4:5:275213524912 | 12674                 | posts       | id         | 275213524912          | RECORD    | X,REC_NOT_GAP | GRANTED     | 2         |
+-----------------------------------+-----------------------+-------------+------------+-----------------------+-----------+---------------+-------------+-----------+

トランザクション1がテーブルのIXロック、id 2のレコードロックのみ。ギャップロックはなし
トランザクション2がテーブルのIXロック。INSERT時に待たされていないためdata_locksに内容が表示されない

確かにREAD COMMITTEDにするとギャップロックを取っていないことがわかる。

まとめ

performance_schema.data_locksを使うことでロック状況が可視化できた。以前PostgreSQLでこういうものを使ったことがあったが、MySQL 8.0からperformance_schema.data_locksというものが使えると知らなかったのでこれから便利に使えそうだ。