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行を追加します。
ロックするSQLを実行
ここで、SELECT .. FOR UPDATE
にて、id2=1000に行をロックします。
ここでdata_locks
テーブルを確認してみます。各行が1つのロックを表します。また、説明しやすくするために出力結果の各行先頭にRowXの連番を付け足しています。
それぞれのカラムについて説明します。
THREAD_ID
ロックしたスレッドID。1つのセッションに1つのスレッドIDが発行されます。9481は、今回SQLを実行してロックを保持しているスレッドのIDになります。
自身のスレッドIDを確認するには以下SQLを実行します。
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でロック待ちのクエリを確認する方法などを紹介したいと思います。