第145回　InnoDBの行ロック状態を確認する［その1］

MySQL（InnoDB）をデフォルトのトランザクション分離レベルのRepeatable-Readで運用していると、ワークロードによってはデッドロックが頻繁に発生して頭を悩ますことがあると思います。解決策としては、デッドロックを引き起こしている原因のSQLがどのような行ロックを取得したか確認して、デッドロックが起こらないようにSQLを修正します。

このほか、行ロックの待機が大量に発生して高負荷になり、サービスに影響が出てしまうこともあると思います。

今回は、そういったときに便利な「InnoDBの行ロック状態を確認する方法」を紹介したいと思います。実行環境にはMySQL 8.0.23を使用しています。

MySQL 8.0での調査方法

まずは、MySQL 8.0から追加されたperformance_schemaのテーブルを使って、あるSQLがどのような行ロックを取得しているか確認する方法を紹介します。

data_locksテーブル: data_locksテーブルは行ロックを保持している、または行ロックを取得しようと待機している状態を確認できます。
data_lock_waitsテーブル: data_lock_waitsテーブルは行ロックを待機している状態を確認できます。スレッド間の行ロックの依存関係を把握することができます。

ロックの状態確認方法

それでは、data_locksテーブルからどのように行ロックの状態が確認できるか、例を挙げて見てみましょう。

テーブル準備

テーブルを用意して1行を追加します。

mysql> CREATE TABLE `i` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `id2` int DEFAULT NULL,
  `t` varchar(255)  DEFAULT NULL,
  PRIMARY KEY `id` (`id`),
  KEY `id2` (`id2`)
) ENGINE=InnoDB;

 mysql> INSERT INTO i (id2,t) VALUES (1000,'test');

ロックするSQLを実行

ここで、SELECT .. FOR UPDATEにて、id2=1000に行をロックします。

mysql> BEGIN;
mysql> SELECT * FROM i WHERE id2=1000 FOR UPDATE;
 
+----+------+------+
| id | id2  | t    |
+----+------+------+
|  1 | 1000 | test |
+----+------+------+
1 row in set (0.00 sec)

ここでdata_locksテーブルを確認してみます。各行が1つのロックを表します。また、説明しやすくするために出力結果の各行先頭にRowXの連番を付け足しています。

mysql> SELECT THREAD_ID,EVENT_ID,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from data_locks;
       +-----------+----------+-------------+------------+-----------+---------------+-------------+------------------------+
       | THREAD_ID | EVENT_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA              |
       +-----------+----------+-------------+------------+-----------+---------------+-------------+------------------------+
 Row1  |      9481 |       48 | i           | NULL       | TABLE     | IX            | GRANTED     | NULL                   |
 Row2  |      9481 |       48 | i           | id2        | RECORD    | X             | GRANTED     | supremum pseudo-record |
 Row3  |      9481 |       48 | i           | id2        | RECORD    | X             | GRANTED     | 1000, 1                |
 Row4  |      9481 |       48 | i           | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 1                      |
       +-----------+----------+-------------+------------+-----------+---------------+-------------+------------------------+

それぞれのカラムについて説明します。

THREAD_ID

ロックしたスレッドID。1つのセッションに1つのスレッドIDが発行されます。9481は、今回SQLを実行してロックを保持しているスレッドのIDになります。

自身のスレッドIDを確認するには以下SQLを実行します。

SELECT THREAD_ID 
FROM performance_schema.threads
WHERE PROCESSLIST_ID=connection_id();

EVENT_ID

ロックしたイベントID。スレッドのイベント番号です。

OBJECT_NAME

ロックされたテーブル名。

INDEX_NAME

ロックされたインデックス名。

LOCK_TYPE

ロックのタイプ。TABLEはテーブルロック、RECORDは行ロックです。

LOCK_MODE

ロックのモード。InnoDBの行ロックでは排他（X）または共有（S）のギャップロック（GAP⁠）⁠、ネクストキーロック（なし）やレコードロック（REC_NOT_GAP）を表します。たとえば、以下のような表示になります。

Xは排他ネクストキーロック
S,REC_NOT_GAPは共有レコードロック
X,GAPは排他ギャップロック

今回は行ロックの確認方法なのでギャップロックやネクストキーロックの詳細については説明しません。くわしくは 15.7.1 InnoDB Lockingをご確認ください。

LOCK_STATUS

ロックの状態。GRANTEDはロック保持、WAITINGはロックを取得できず待機している状態です。

LOCK_DATA

ロックを取得した行の値が表示されます。プライマリキーであれば、プライマリキーの値。セカンダリキーであればセカンダリーキーの値とプライマリキーの値となります。

各行の詳細

今回は行が4つであることから4つの種類のロックを取得しています。また、LOCK_STATUS=GRANTEDからリクエストしたロックが待機中ではなくすべて取得できています。

Row1

LOCK_TYPE=TABLEとなっていることからテーブルレベルのロックです。また、LOCK_MODE=IXであることから、排他インテンションロックです。インテンションロックについては詳しくは15.7.1 InnoDB LockingのIntention Locksをご確認ください。

Row2

id2インデックス（INDEX_NAME=id2）の行ロック（LOCK_TYPE=RECORD）で、排他ネクストキーロック（LOCK_MODE=X）です。行の値を見てみると、LOCK_DATA=supremum pseudo-recordとなっており、これはインデックスの最大値を超える疑似値とその範囲に対してギャップロックを取得しています。他のトランザクションからのid2=1000よりも大きい値を挿入することを防ぐためのロックです。

Row3

Row2と同じくid2インデックス（INDEX_NAME=id2）の行ロック（LOCK_TYPE=RECORD）で、排他ネクストキーロック（LOCK_MODE=X）です。行の値を見てみると、LOCK_DATA=1000,1となっており、id2:1000とid:1を表します。id2:1000のロックとid2=1000以下に対してのギャップロックを取得しています。これは他のトランザクションからのid2=1000と、それ以下の値を挿入することを防ぐためのロックです。

Row4

id2インデックス（INDEX_NAME=PRIMARY）の行ロック（LOCK_TYPE=RECORD）で、排他レコードロック（X,REC_NOT_GAP）です。行の値を見てみると、LOCK_DATA=1となっているので、id:1の行のみロックを表します。

まとめ

MySQL 8.0でInnoDBの行ロック状態を確認する方法を紹介しました。もし、リリース予定のSQLでデッドロックや行ロック待機が懸念されるときは、そのSQLがどのようなロックを取得するかを、今回の方法で事前に把握できます。

次回は、今回紹介できなかったdata_lock_waitsテーブルについてや、SQLでロック待ちのクエリを確認する方法などを紹介したいと思います。