インフィニットループ 技術ブログ

2024年07月04日 (木)

著者 : nob

MySQL/Aurora/TiDBロック入門 – 第6回 performance_schema【解説動画付】

MySQL のロックについて解説する入門シリーズの第6回です。今まで使ってきた InnoDB のロックモニターに代わり、perfomance_schema を使って測定する方法について解説します!

第1回 トランザクション分離レベル
第2回 ロックモニターの読み方
第3回 ロック読取りも SELECT は止められない
第4回 INSERT を止めるインテンションロック
第5回 WHERE 条件と違うロック読取り
第6回 performance_schema
第7回ギャップロックと消えるロック
第8回 ネクストキーロックと降順インデックス
第9回 共有ロックでデッドロック
第10回 ロック読み取りは READ COMMITTED
第11回 Aurora と MySQL の違い
第12回 TiDB と MySQL の違い

第6回 performance_schema 解説動画

performance_schema 有効化確認

多くの MySQL の配布パッケージではデフォルトで有効化されていますが、念の為確認します。

[1]> SELECT @@performance_schema;
+----------------------+
| @@performance_schema |
+----------------------+
|                    1 |
+----------------------+

もし 0 で無効化なときはコンフィグ my.cnf[mysqld] の設定で performance_schema を有効化してください。

perfomance_schema の data_locks テーブル

ロック情報は perfomance_schemadata_locks テーブルに格納されています。data_locks テーブルはシステム全体が対象で、行だけではなくテーブルに関してのインテンションロックなども記載されています。今回使うデータベースは learning_mysql でテーブルは t1 ですが、この場合に見たい例に絞り込んで必要な情報を取得するクエリは以下になります。

select engine_transaction_id as tx_id, index_name, lock_mode, lock_data 
from performance_schema.data_locks 
where lock_type = 'RECORD' and object_schema = 'learning_mysql' and object_name = 't1';

長いクエリを毎回打つのは大変なのでビュー t1locks を作っておきます。select クエリの前に CREATE VIEW t1locks AS を付けて作成します。

create view t1locks as select engine_transaction_id as tx_id, index_name, lock_mode, lock_data 
from performance_schema.data_locks 
where lock_type = 'RECORD' and object_schema = 'learning_mysql' and object_name = 't1';

いくつかロックして試す

トランザクションを開始し、k = 1, k = 15, k > 100 の三箇所で FOR UPDATE のロックをかけます。

[1]> SELECT * FROM t1 WHERE k = 1 FOR UPDATE;
+---+------+
| k | v    |
+---+------+
| 1 |    0 |
+---+------+
1 row in set (0.00 sec)

[1]> SELECT * FROM t1 WHERE k = 15 FOR UPDATE;
Empty set (0.00 sec)

[1]> SELECT * FROM t1 WHERE k > 100 FOR UPDATE;
Empty set (0.00 sec)

次に t1locks ビューでロックを確認します。

[1]> SELECT * FROM t1locks;
+-------+------------+---------------+------------------------+
| tx_id | index_name | lock_mode     | lock_data              |
+-------+------------+---------------+------------------------+
| 12049 | PRIMARY    | X,REC_NOT_GAP | 1                      | <---- k= 1
| 12049 | PRIMARY    | X,GAP         | 100                    | <---- k = 15
| 12049 | PRIMARY    | X             | supremum pseudo-record | <---- k > 100
+-------+------------+---------------+------------------------+

tx_id が実行中のトランザクションの ID
index_name がロックに使われているインデックス名で今は PRIMARY キー
lock_mode がロックの種類で
X, S が排他か共有で、それだけの場合は前方ギャップ+行のみがセットのネクストキーロック
GAP は前方のギャップ
REC_NOT_GAP はその行のみのロック
lock_data がロックのかかっている行のキーの値です。

FOR UPDATE を行った WHERE 条件別に
k = 1 —-> X,REC_NOT_GAP で k=1 行のみ排他ロック
k = 15 —-> X,GAP で k=100 行の前方排他ギャップロック
k > 100 —-> X で supremum 行の排他ネクストキーロック

このように performance_schema の data_locks テーブルを使うと見たいロック情報のみを表示出来ます。

トランザクション中のロックが共有ロックだけの場合

ロックモニターの場合は共有ロックだけの場合表示されず、排他ロックを追加すると出現する、という現象がありました。perfomance_schema の場合はどうなのか確認してみましょう。トランザクションを開始し、k = 5LOCK IN SHARE MODE の共有ロックだけをかけてみます。

[1]> SELECT * FROM t1 WHERE k = 5 LOCK IN SHARE MODE;
Empty set (0.00 sec)

[1]> SELECT * FROM t1locks;
+-----------------+------------+-----------+-----------+
| tx_id           | index_name | lock_mode | lock_data |
+-----------------+------------+-----------+-----------+
| 412670009101528 | PRIMARY    | S,GAP     | 10        |
+-----------------+------------+-----------+-----------+
1 row in set (0.00 sec)

表示されないロックモニターとはことなり、k = 10 の共有の前方ギャップロックが表示されました!

ただし、前が 12049 でしたので今回は 12050 が想定されるトランザクション ID が、 64 ビットの不思議な値になっていることがわかります。さらに同じ場所に FOR UPDATE の排他ロックを追加してみましょう。

[1]> SELECT * FROM t1 WHERE k = 5 FOR UPDATE;
Empty set (0.00 sec)

[1]> SELECT * FROM t1locks;
+-------+------------+-----------+-----------+
| tx_id | index_name | lock_mode | lock_data |
+-------+------------+-----------+-----------+
| 12050 | PRIMARY    | S,GAP     | 10        | <==== 412670009101528 から変化
| 12050 | PRIMARY    | X,GAP     | 10        |
+-------+------------+-----------+-----------+

トランザクション 12050 内で同じ k=10 の排他の前方ギャップロック X,GAP が追加されました。共有の前方ギャップロック S,GAP も存在したままですが、トランザクションIDに注目してください、正しく 12050 になっています!

まとめ

★ InnoDB ロックモニターに代わり perfomance_schema で現在のロックの状況を観測する方法を紹介
★トランザクション内のロックが共有ロックのみの場合 performance_schema では表示されるがトランザクションID は 64 ビットの値に変更される

ロックの分析データとしてキーの値で十分な場合は、今回紹介した performance_schema の調査が有効です。本シリーズでも今後の回では積極的に利用していきます。

次回、第7回は MySQL InnoDB の特徴といえるギャップロックの詳細についていよいよ解説していきます!ご期待ください!

ブログ記事検索

このブログについて

このブログは、札幌市・仙台市の「株式会社インフィニットループ」が運営する技術ブログです。 お仕事で使えるITネタを社員たちが発信します!