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

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

★ 第１回トランザクション分離レベル
★ 第２回ロックモニターの読み方
★ 第３回ロック読取りも SELECT は止められない
★ 第４回 INSERT を止めるインテンションロック
★ 第５回 WHERE 条件と違うロック読取り
★ 第６回 performance_schema
★ 第７回ギャップロックと消えるロック
★ 第８回ネクストキーロックと降順インデックス
★ 第９回共有ロックでデッドロック
★ 第１０回ロック読み取りは READ COMMITTED
★ 第１１回 AWS Aurora と MySQL の違い
★ 第１２回 PingCAP TiDB と MySQL の違い

第６回 performance_schema 解説動画

performance_schema 有効化確認

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

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

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

perfomance_schema の data_locks テーブル

ロック情報は perfomance_schema の data_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 = 5 で LOCK 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 になっています！