ラグザイアPublicationへの投稿
🔒

MySQLのロック状態を確認する

chima
に公開
MySQL
lock
transaction
InnoDB
tech

この記事では、MySQL(InnoDB)でのロック状態の確認方法と、結果の見方を紹介します。
実際、結果の見方の解説記事が少なく、案件のデッドロック問題の解析に難儀したので、同じような人を減らすことができれば嬉しいです。

トランザクションとロックの基礎知識については、以下の資料がとても参考になりますので、基本的な説明は割愛します。

ロック状態を確認する方法

ロック状態を確認する手順と結果の見方を、実際にデータ操作しながら見ていきます。

手順

  1. トランザクションを開始し、ロック状態を見たいところまで実行する
  2. SELECT * FROM performance_schema.data_locks\G を実行する
    ※MySQL 5.7系の場合は SELECT * FROM sys.innodb_lock_waits\G

結果の見方

今回の検証にあたり、ロック状態を確認するためだけの簡単なMySQL環境を作成しました。
記事と同じ検証ができますので、より詳しく見てみたい方は実際に手を動かしてみてください。
MySQLのバージョンは8.0.41です。

例：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from organization_users where id = 110;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139746888125656:110:1071:139746894467968
ENGINE_TRANSACTION_ID: 2910
            THREAD_ID: 50
             EVENT_ID: 41
        OBJECT_SCHEMA: mysql_demo
          OBJECT_NAME: organization_users
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139746894467968
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139746888125656:110:5:4:4:139746894464976
ENGINE_TRANSACTION_ID: 2910
            THREAD_ID: 50
             EVENT_ID: 41
        OBJECT_SCHEMA: mysql_demo
          OBJECT_NAME: organization_users
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139746894464976
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 110
2 rows in set (0.01 sec)

2. rowを例に、ロック状態を確認するのに必要な、主な項目を説明します。

項目 値(例) 説明
ENGINE INNODB ENGINEの種類
ENGINE_TRANSACTION_ID 2910 ロックを要求したトランザクションのID、
複数のトランザクションを並列して実行している場合(デッドロックの再現等)は、このIDでどのトランザクションでのロック状態なのかを判断できる
OBJECT_SCHEMA mysql_demo ロックされたテーブルを含むスキーマ
OBJECT_NAME organization_users ロックされたテーブルの名前
INDEX_NAME PRIMARY ロックされたインデックスの名前
LOCK_TYPE RECORD ロックのタイプ(TABLE or RECORD)
LOCK_MODE X,REC_NOT_GAP ロック状態　※詳細後記
LOCK_STATUS GRANTED ロックリクエストのステータス
GRANTED (ロックが保持されている) or WAITING (ロックが待機されている)
LOCK_DATA 110 ロックに関連付けられているデータ　※詳細後記

参考：MySQL :: MySQL 8.0 リファレンスマニュアル :: 27.12.13.1 data_locks テーブル

LOCK_MODE と LOCK_DATA

LOCK_MODEとLOCK_DATAでは、実際にどのデータ(の範囲)をロックしているのかを示しています。

ロックの種類と範囲

LOCK_MODEで表現されるロックの種類と範囲を表にすると以下のように12通りになります。

種類　＼　範囲 レコードロック ギャップロック ネクストキーロック
共有ロック (S) S,REC_NOT_GAP S,GAP S
インテンション共有ロック (IS) IS,REC_NOT_GAP IS,GAP IS
排他ロック (X) X,REC_NOT_GAP X,GAP X
インテンション排他ロック (IX) IX,REC_NOT_GAP IX,GAP IX

参考：MySQL :: MySQL 8.0 リファレンスマニュアル :: 15.7.1 InnoDB ロック

ロック例

以下のようなPRIMARYキーを持つorganization_usersというテーブルでの例を示します。

mysql> SELECT id from organization_users;
+-----+
| id  |
+-----+
| 100 |
| 110 |
| 120 |
+-----+

なお、例にはテーブルのIXロックもかかっていますが、記載は省略します。

S(X),REC_NOT_GAP　→　レコードロック

レコードロックでは、INDEX_NAMEのインデックスのLOCK_DATAの値のみがロックされます。
以下の例では、PRIMARYキーが110のデータがロックされます。

mysql> start transaction;
mysql> delete from organization_users where id = 110;

mysql> SELECT ENGINE_TRANSACTION_ID,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_DATA FROM performance_schema.data_locks\G
*************************** 2. row ***************************
ENGINE_TRANSACTION_ID: 1830
          OBJECT_NAME: organization_users
           INDEX_NAME: PRIMARY
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
            LOCK_DATA: 110


このロック状態のまま、別トランザクションでどのデータがロックにかかっているのかを見てみましょう(ターミナルの別タブでMySQLのコンソールを開き、そこで新たにstart transaction;します)。
id=110のデータに対しての操作のみがロックされ、待ち続けてタイムアウトし、id=109,111は操作が可能なことがわかります。

mysql> insert into organization_users (id, organization_id, user_id) values (109, 10, 5);
Query OK, 1 row affected (0.00 sec)

mysql> update organization_users set user_id = 1 where id = 110;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> insert into organization_users (id, organization_id, user_id) values (111, 20, 5);
Query OK, 1 row affected (0.00 sec)

S(X),GAP　→　ギャップロック

ギャップロックでは、INDEX_NAMEのインデックスのLOCK_DATAの手前の値までのレコードがない範囲がロックされます。LOCK_DATAの値自体はロックされないので注意が必要です。
以下の例では、PRIMARYキーが101〜109のデータがロックされます。

mysql> start transaction;
mysql> delete from organization_users where id = 107;

mysql> SELECT ENGINE_TRANSACTION_ID,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_DATA FROM performance_schema.data_locks\G
*************************** 2. row ***************************
ENGINE_TRANSACTION_ID: 1836
          OBJECT_NAME: organization_users
           INDEX_NAME: PRIMARY
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
            LOCK_DATA: 110

このロック状態のまま、別トランザクションでどのデータがロックにかかっているのかを見てみると、
id=101〜109のデータに対しての操作がロックされ、id=100,110は操作が可能なことがわかります。

mysql> update organization_users set user_id = 3 where id = 100;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> insert into organization_users (id, organization_id, user_id) values (101, 20, 5);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> insert into organization_users (id, organization_id, user_id) values (109, 20, 5);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> update organization_users set user_id =1  where id = 110;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings:

S（X）　→　ネクストキーロック

ネクストキーロックでは、INDEX_NAMEのインデックスのLOCK_DATAの値のレコードロック＋手前のギャップロックがかかります。
以下の例では、PRIMARYキーが、3. rowがネクストキーロックで106〜110のデータがロックされ、2. rowで111〜119のデータがギャップロックされます。

mysql> start transaction;
mysql> delete from organization_users where id > 107 and id < 112;

mysql> SELECT ENGINE_TRANSACTION_ID,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_DATA FROM performance_schema.data_locks\G
*************************** 2. row ***************************
ENGINE_TRANSACTION_ID: 2923
          OBJECT_NAME: organization_users
           INDEX_NAME: PRIMARY
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
            LOCK_DATA: 120
*************************** 3. row ***************************
ENGINE_TRANSACTION_ID: 2923
          OBJECT_NAME: organization_users
           INDEX_NAME: PRIMARY
            LOCK_TYPE: RECORD
            LOCK_MODE: X
            LOCK_DATA: 110


このロック状態のまま、別トランザクションでどのデータがロックにかかっているのかを見てみると、
id=101〜119のデータに対しての操作がロックされ、id=100,120は操作が可能なことがわかります。

mysql> update organization_users set user_id = 3 where id = 100;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> insert into organization_users (id, organization_id, user_id) values (101, 20, 5);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> insert into organization_users (id, organization_id, user_id) values (109, 20, 5);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> update organization_users set user_id =1  where id = 110;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> insert into organization_users (id, organization_id, user_id) values (111, 20, 5);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> insert into organization_users (id, organization_id, user_id) values (119, 20, 1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> update organization_users set user_id =3  where id = 120;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

まとめ

本記事では、MySQLのロック状態の見方と、PRIMARYキーでのロック状態の例を見ていきました。

ロックの範囲 LOCK_TYPE 説明
レコードロック S(X),REC_NOT_GAP INDEX_NAMEインデックスのLOCK_DATAの値のみがロックされる
ギャップロック S(X),GAP INDEX_NAMEインデックスのLOCK_DATAの手前の値までのレコードがない範囲がロックされる
LOCK_DATAの値自体はロックされないので注意が必要
ネクストキーロック S(X) INDEX_NAMEインデックスのLOCK_DATAの値のレコードロック＋手前のギャップロック
ラグザイア
ラグザイアPublication

株式会社ラグザイア（luxiar.com）の技術広報ブログです。 ラグザイアはRuby on RailsとC#に特化した町田の受託開発企業です。フルリモートでの開発を積極的に推進しており、全国からの参加を可能にしています。柔軟な働き方で最新のソフトウェアソリューションを提供します。

Discussion