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というものが使えると知らなかったのでこれから便利に使えそうだ。

Action Graphを使ってGoのpackageごとのビルド時間を可視化する

Goのパッケージごとのビルド時間を計測したいんだけど (どのパッケージのビルドに何秒かかってる、とか見たい) どうしたらいいのか、ちょっとググってみたけどランタイムにおけるパフォーマンス測定の話題ばっかり出てくる

— うたがわきき (@utgwkk) February 26, 2024

前やったことあるがブログに書いてなかったのでメモしておく。

まずGoのビルド時間についてはAnalyzing Go Build Times | howardjohn's blogが非常に分かりやすく参考になる。この中でAction Graphというものに言及があり、これを使うことでパッケージごとのビルド時間を可視化できる。

例えば自分のgo_todo_appというものを使ってみる。 まずgo buildでactiongraph.jsonを吐き出し

$ go build -debug-actiongraph=actiongraph.json ./...

その後時間がかかっている順にランキングを出せる。今回のレポジトリは非常に小さいのでruntime/cgoのビルドが一番重そうだ。

$ actiongraph -f ./actiongraph.json top
  0.956s   6.88%  build runtime/cgo
  0.768s  12.41%  build runtime
  0.560s  16.44%  build net
  0.473s  19.85%  build net/http
  0.401s  22.74%  build debug/dwarf
  0.378s  25.45%  build math/big
  0.354s  28.01%  build vendor/golang.org/x/text/unicode/norm
  0.318s  30.30%  build gopkg.in/yaml.v3
  0.289s  32.38%  build golang.org/x/net/html
  0.275s  34.36%  build github.com/google/go-cmp/cmp
  0.258s  36.22%  link  github.com/shibayu36/go_todo_app
  0.250s  38.01%  build encoding/xml
  0.243s  39.77%  build crypto/tls
  0.239s  41.48%  build encoding/json
  0.222s  43.08%  build golang.org/x/text/internal/language
  0.205s  44.55%  build reflect
  0.190s  45.92%  build testing
  0.186s  47.26%  build database/sql
  0.166s  48.45%  build github.com/jmoiron/sqlx
  0.161s  49.61%  build github.com/go-playground/validator/v10

また、treeを使うことでnestされたpackageのどれに時間がかかっているかを可視化したり

$ actiongraph -f ./actiongraph.json tree -L 2
 13.634s          (root)
 10.935s            std
  1.860s   0.768s     std/runtime
  1.644s   0.012s     std/crypto
  1.293s   0.560s     std/net
  1.037s              std/vendor
  0.861s              std/internal
  0.737s   0.018s     std/encoding
  0.550s   0.089s     std/math
  0.459s              std/debug
  0.245s              std/database
  ...
  1.682s            github.com
  0.357s              github.com/google
  0.261s              github.com/go-playground
  0.197s              github.com/gabriel-vasile
  0.189s              github.com/jmoiron
  0.179s              github.com/stretchr
  0.140s              github.com/shibayu36
  0.103s              github.com/go-sql-driver
  0.068s              github.com/davecgh
  0.057s              github.com/caarlos0
  0.048s              github.com/pmezard
  0.043s              github.com/leodido
  0.040s              github.com/go-chi
  0.699s            golang.org
  0.699s              golang.org/x
  0.318s            gopkg.in
  0.318s   0.318s     gopkg.in/yaml.v3

actiongraphのREADMEに書かれているように、あるパッケージがどこから依存されているかの可視化もできる。

便利ですね。

参考

• Analyzing Go Build Times | howardjohn's blog
• https://github.com/icio/actiongraph

MySQLのREPEATABLE READとREAD COMMITTEDの違いを知るために色々試した

MySQLのトランザクション分離レベルについてふんわりとした理解しかないなと感じた。もう少し理解するために、とくにREPEATABLE READとREAD COMMITTEDの違いを手を動かして色々確認してみた。

以下の記事を参考にした。

• [RDBMS][SQL]トランザクション分離レベルについて極力分かりやすく解説 #SQL - Qiita
• MySQL :: MySQL 8.0 リファレンスマニュアル :: 15.7.2.1 トランザクション分離レベル

大まかな違い

公式ドキュメントを見る限り

• ノンリピータブルリード、ファントムリードが発生するか
• 範囲に含まれるギャップへのほかのセッションによる挿入をブロックするか

の違いがありそうに見える。

ノンリピータブルリード、ファントムリードが発生するかを試す

以下のテーブルを作る。

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

REPEATABLE READの場合

まずデフォルトのREPEATABLE READから。

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

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

# トランザクション1
insert into posts (title, body) values('title1', 'body1');

# トランザクション2
# ダーティリードが発生していない
select * from posts;
Empty set (0.01 sec)
select count(*) from posts;
+----------+
| count(*) |
+----------+
|        0 |
+----------+

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

# トランザクション2
# ノンリピータブルリードが発生していない
select * from posts;
Empty set (0.01 sec)
# ファントムリードが発生していない
select count(*) from posts;
+----------+
| count(*) |
+----------+
|        0 |
+----------+

# トランザクション2
rollback;
# トランザクション1のCOMMITが見えるようになる
select * from posts;
+--------+-------+
| title  | body  |
+--------+-------+
| title1 | body1 |
+--------+-------+
select count(*) from posts;
+----------+
| count(*) |
+----------+
|        1 |
+----------+

ノンリピータブルリードもファントムリードも発生していないことが確認できる。REPEATABLE READの説明ではファントムリードは起こると書いていることが多いが実装次第であり、MySQLにおいてはファントムリードも起こらなくなっている。

READ COMMITTEDの場合

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

# トランザクション1
begin;
insert into posts (title, body) values('title1', 'body1');

# トランザクション2
# ダーティリードが発生していない
select * from posts;
Empty set (0.01 sec)

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

# トランザクション2
# ノンリピータブルリードが発生した
> select * from posts;
+--------+-------+
| title  | body  |
+--------+-------+
| title1 | body1 |
+--------+-------+

ちゃんとノンリピータブルリードが起こることが確認できる。

範囲に含まれるギャップへのほかのセッションによる挿入をブロックするか

続いて、ロック範囲についても検証する。テーブル構造をこのようにする。

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される

特定範囲をUPDATEかけた場合、他のトランザクションからギャップ範囲に対してINSERTをしたときにロックされることが確認できた。

UPDATE側がREPEATABLE READ, INSERT側がREAD COMMITTED

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

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

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

UPDATE側がREPEATABLE READなら、INSERT側がREAD COMMITTEDだとしてもロックされることが確認できた。

UPDATE側がREAD COMMITTED, INSERT側がREPEATABLE READ

# トランザクション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されない

UPDATE側がREAD COMMITTEDなら、INSERT側がロックされないことを確認できた。

まとめ

MySQLにおいてはREPEATABLE READとREAD COMMITTEDの違いは大まかに以下がありそうだ。

• ノンリピータブルリード、ファントムリードが発生するか
• 範囲に含まれるギャップへのほかのセッションによる挿入をブロックするか

実際に手を動かして試したところ、

• ノンリピータブルリード・ファントムリードは、REPEATABLE READでは発生せず、READ COMMITTEDでは発生する
• REPEATABLE READではUPDATEやDELETEの範囲のギャップロックがかかるのでほかセッションによるギャップ範囲への挿入はブロックされる。READ COMMITTEDではギャップロックがかからない

ということを確認できた。