MySQL のロックについて解説する入門シリーズの第6回です。今まで使ってきた InnoDB のロックモニターに代わり、perfomance_schema を使って測定する方法について解説します!
★ 第1回 トランザクション分離レベル
★ 第2回 ロックモニターの読み方
★ 第3回 ロック読取りも SELECT は止められない
★ 第4回 INSERT を止めるインテンションロック
★ 第5回 WHERE 条件と違うロック読取り
★ 第6回 performance_schema
★ 第7回ギャップロックと消えるロック
★ 第8回 ネクストキーロックと降順インデックス
★ 第9回 共有ロックでデッドロック
★ 第10回 ロック読み取りは READ COMMITTED
★ 第11回 AWS Aurora と MySQL の違い
★ 第12回 PingCAP TiDB と MySQL の違い
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 になっています!
まとめ
★ InnoDB ロックモニターに代わり perfomance_schema で現在のロックの状況を観測する方法を紹介
★トランザクション内のロックが共有ロックのみの場合 performance_schema では表示されるがトランザクションID は 64 ビットの値に変更される
ロックの分析データとしてキーの値で十分な場合は、今回紹介した performance_schema の調査が有効です。本シリーズでも今後の回では積極的に利用していきます。
次回、第7回は MySQL InnoDB の特徴といえるギャップロックの詳細についていよいよ解説していきます!ご期待ください!