MySQL 8.0.20 (InnoDB): INDEX 有無によって、実在しない行に対する SELECT ... FOR UPDATE とそれに続く INSERT の挙動が異なる

今回の検証で仕組みの理解には至りませんでした。
個人用に挙動をメモ。

検証したい事

INDEX 有無で下記の挙動の違い
- 行ロック
- 行ロック後のINSERT

バージョン

MySQL 8.0.20

前提

InnoDB は行ロックの際に、テーブルロック(インテンションロック)も取得する
参考
- MySQL 5.6 リファレンスマニュアル - 14.2.3 InnoDB のロックモード
流れ
1. インテンションロック取得 (IX) または (IS)
2. 行ロック取得 (X) または (S)
インテンションロック
- what
  - テーブルレベルのロック
  - トランザクションがテーブル内の行でどんなタイプのロックを獲得しようとしているかを示す
- 目的
  - 他のトランザクションが行をロックしていることや、テーブル内の行をロックしようとしていることを示すこと
- 完全なテーブルリクエスト (LOCK TABLES ... WRITE など) 以外は何もブロックしない
備考
- インテンションロック同士は競合しないので、1. は必ず取得できる
- 行ロックとインテションロックは競合するので、2. の取得待ちが発生する

クラスタインデックス

INDEX が貼られていない場合は、内部で クラスタインデックス が生成されるらしい

MySQL 5.6 リファレンスマニュアル - 14.2.13.2 クラスタインデックスとセカンダリインデックスより

テーブルに PRIMARY KEY も適切な UNIQUE インデックスも存在しない場合には、InnoDB の内部で、行 ID 値を含む合成カラム上に非表示のクラスタ化されたインデックスが生成されます

挿入インテンションギャップロック

MySQL 5.6 リファレンスマニュアル - 14.2.6 InnoDB のレコード、ギャップ、およびネクストキーロックより

INSERT 操作では行の挿入前に、挿入インテンションギャップロックと呼ばれる一種のギャップロックが設定されます。

このロックは、同じインデックスギャップに挿入する複数のトランザクションは、そのギャップ内の同じ場所に挿入しなければ相互に待機する必要がないように、意図的に挿入することを示しています。

値が 4 と 7 のインデックスレコードが存在すると仮定します。それぞれ値 5 と 6 の挿入を試みる別々のトランザクションは、挿入される行の排他ロックを取得する前に挿入インテンションロックを使用して、4 と 7 の間にあるギャップをロックしますが、行の競合が発生しないため相互にブロックされません。

検証結果

存在しない行への SELECT ... FOR UPDATE
- INDEX なし
  - T1 が先にロックを取得していた場合は、T2 はロックを取得できなかった
- INDEX あり
  - T1 が先にロックをしていても、T2 がロックを取得できた
(存在しない行への SELECT ... FOR UPDATE に続く) INSERT
- INDEX なし
  - (SELECT ... FOR UPDATE 時に T2 が T1 のCOMMIT|ROLLBACK を待つ為) 検証対象外
- INDEX あり
  - 挿入インテンションギャップロック の取得でデッドロックが発生するケースとしないケースがあった

検証: INDEX 有無による `SELECT ... FOR UPDATE` とそれに続く `INSERT` の挙動

INDEX なし

T1> START TRANSACTION;
T2> START TRANSACTION;

// T1: 実在しない行を行排他ロック
//   インテンション排他ロック(IX) => 取得
//   行排他ロック(X) => 全レコードの行排他ロックを取得
T1> SELECT * from test WHERE id = 4 FOR UPDATE;
Empty set (0.00 sec)

// T2: 実在しない行を行排他ロック
//   インテンション排他ロック(IX) => インテンションロック同士は競合しない為、T1 が (IX) を取得していても T2 が (IX) を取得できる
//   行排他ロック(X) => T1 の (X) と競合する為、T1 の COMMIT|ROLLBACK を待つ
T2> SELECT * from test WHERE id = 4 FOR UPDATE;
-- 待たされる

T1> INSERT INTO test values (4, 1);
T1> COMMIT;

// T1 が COMMIT したので、競合が解消し、T2 が (X) を取得
//   SELECT 結果に T1 の COMMIT 内容が反映されている
T2 > 待たされていたSELECT
+------+-------+
| id   | count |
+------+-------+
|    4 |     1 |
+------+-------+
1 row in set (25.53 sec)

INDEX あり (デッドロックが発生したケース)

T1> START TRANSACTION;
T2> START TRANSACTION;

// T1: 実在しないを行排他ロック
//  インテンション排他ロック(IX) => 取得
//  行排他ロック(X) => supremum pseudo-record(=最小上限レコード(=末尾レコードの後にある疑似レコード)) に対してロックを取得
T1> SELECT * from test WHERE id = 5 FOR UPDATE;
Empty set (0.00 sec)

// T2: 実在しない行排を排他ロック
//  インテンション排他ロック(IX) => 取得
//  行排他ロック(X) => T1の(X)とは競合せずに、T2 も supremum pseudo-record のロックを取得できる (※なぜ取得できるのか、仕組みが理解できていません。。)
T2> SELECT * from test WHERE id = 5 FOR UPDATE;
Empty set (0.00 sec)

// T1: INSERT
//   挿入インテンションギャップロック(X,INSERT_INTENTION) => T2 のロックによって supremum pseudo-record の INSERT_INTENTION が WAITING になる  (※なぜWAITING になるのか、仕組みが理解できていません。)
T1> INSERT INTO test values (5, 1);
-- 待たされる

// T2: INSERT
//   挿入インテンションギャップロック(X,INSERT_INTENTION) => T1 のロックによって supremum pseudo-record の INSERT_INTENTION  WAITING になる => デッドロック
T2> INSERT INTO test values (5, 1);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
-- デッドロックで終了

// T2 が ROLLBACK したので、T1 が挿入インテンションギャップロックを取得できるようになり、INSERTが実行された
T1> 待たされていたINSERT
Query OK, 1 row affected (27.41 sec)

デッドロックが発生したケース、しなかったケース

 SELECT * FROM test ORDER BY id;
+------+-------+
| id   | count |
+------+-------+
|    1 |     0 |
|    2 |     0 |
|    3 |     0 |
|    4 |     1 |
|    5 |     1 |
|  100 |     1 |
|  101 |     1 |
|  103 |     1 |
|  104 |     1 |
|  200 |     1 |
|  200 |     1 |
|  201 |     1 |
|  201 |     1 |
|  203 |     1 |
|  204 |     1 |
| 1000 |     1 |
+------+-------+
16 rows in set (0.00 sec)

上記のようなデータの場合に、下記の挙動になった。この辺は今回、なぜそのような挙動になるのか理解できなかったので、次回の課題。

パターン1 (デッドロック発生)

T1> SELECT * from test WHERE id = 998 FOR UPDATE;
T2> SELECT * from test WHERE id = 999 FOR UPDATE;

T1> INSERT INTO test values (998, 1);
-- 待たされる

T2> INSERT INTO test values (999, 1);
-- デッドロック発生

T1: id=205〜999 をギャプロック
T2: id=205〜999 をギャプロック

という状態となり、ロックの範囲が重複して WAITING とデッドロックが発生?

パターン2 (デッドロック未発生)

T1> SELECT * from test WHERE id = 999 FOR UPDATE;
T2> SELECT * from test WHERE id = 1001 FOR UPDATE;

// 待たされない
T1> INSERT INTO test values (999, 1);
Empty set (0.01 sec)

// 待たされない
T2> INSERT INTO test values (1001, 1);
Empty set (0.01 sec)

T1: id=205〜999 をギャプロック
T2: id=1001以降の supremum pseudo-record をレコードロック

という状態となり、ロックの範囲が重複しないので、WAITING とデッドロックが発生しなかった?

パターン3 (デッドロック発生)

T1> SELECT * from test WHERE id = 1001 FOR UPDATE;
T2> SELECT * from test WHERE id = 1002 FOR UPDATE;


T1> INSERT INTO test values (1001, 1);
-- 待たされる

T2> INSERT INTO test values (1002, 1);
-- デッドロック発生

T1: id=1001以降の supremum pseudo-record をレコードロック
T2: id=1001以降の supremum pseudo-record をレコードロック

という状態となり、ロックの範囲が重複して WAITING とデッドロックが発生?

作業ログ

INDEX なし

// データセット作成
mysql> CREATE TABLE test(id INT, count INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test values (1, 0), (2, 0), (3, 0);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

// 検証前
mysql> SET autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)


mysql> SET global innodb_status_output_locks = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@innodb_status_output_locks;
+------------------------------+
| @@innodb_status_output_locks |
+------------------------------+
|                            1 |
+------------------------------+
1 row in set (0.00 sec)

// 検証前の状態
mysql> SHOW INDEX FROM test;
Empty set (0.01 sec)

mysql> SELECT * FROM test;
+------+-------+
| id   | count |
+------+-------+
|    1 |     0 |
|    2 |     0 |
|    3 |     0 |
+------+-------+
3 rows in set (0.00 sec)

// 検証
T1> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

T2> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)


// data_locks は空
mysql> SELECT * FROM performance_schema.data_locks\G
Empty set (0.00 sec)

mysql> SELECT * FROM performance_schema.data_locks\G
Empty set (0.00 sec)

mysql> SHOW engine innodb status\G

Trx id counter 3913

///////////////////////////////////////
// T1: 存在しない行を行排他ロック
///////////////////////////////////////
T1> SELECT * from test WHERE id = 4 FOR UPDATE;
Empty set (0.00 sec)

// T1 が、インテンション排他ロック(IX) と 行排他ロック(X) を取得している
//  ・(X) の INDEX_NAME は  GEN_CLUST_INDEX となっている
//  ・Record lock を 3 件取得していることから、存在するすべてのレコードを行排他ロックしたと推定
mysql> SHOW engine innodb status\G

---TRANSACTION 3913, ACTIVE 5 sec
2 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 13, OS thread handle 139656706246400, query id 108 localhost root starting
SHOW engine innodb status
TABLE LOCK table `webapp`.`test` trx id 3913 lock mode IX
RECORD LOCKS space id 11 page no 4 n bits 72 index GEN_CLUST_INDEX of table `webapp`.`test` trx id 3913 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000000308; asc       ;;
 1: len 6; hex 000000000f44; asc      D;;
 2: len 7; hex 810000008b0110; asc        ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 80000000; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000000309; asc       ;;
 1: len 6; hex 000000000f44; asc      D;;
 2: len 7; hex 810000008b011f; asc        ;;
 3: len 4; hex 80000002; asc     ;;
 4: len 4; hex 80000000; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 00000000030a; asc       ;;
 1: len 6; hex 000000000f44; asc      D;;
 2: len 7; hex 810000008b012e; asc       .;;
 3: len 4; hex 80000003; asc     ;;
 4: len 4; hex 80000000; asc     ;;


// T1 が インテンション排他ロック(IX) と クラスターインデックスとして行排他ロック(X) を取得している
mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734756216:1072:139656633071312
ENGINE_TRANSACTION_ID: 3913
            THREAD_ID: 54
             EVENT_ID: 96
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139656633071312
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734756216:11:4:1:139656633068320
ENGINE_TRANSACTION_ID: 3913
            THREAD_ID: 54
             EVENT_ID: 96
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 139656633068320
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734756216:11:4:2:139656633068320
ENGINE_TRANSACTION_ID: 3913
            THREAD_ID: 54
             EVENT_ID: 96
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 139656633068320
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 0x000000000308
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734756216:11:4:3:139656633068320
ENGINE_TRANSACTION_ID: 3913
            THREAD_ID: 54
             EVENT_ID: 96
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 139656633068320
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 0x000000000309
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734756216:11:4:4:139656633068320
ENGINE_TRANSACTION_ID: 3913
            THREAD_ID: 54
             EVENT_ID: 96
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 139656633068320
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 0x00000000030A
5 rows in set (0.00 sec)

// ロック待ちは未発生
mysql> SELECT * FROM performance_schema.data_lock_waits\G
Empty set (0.00 sec)




///////////////////////////////////////
// T2: 存在しない行を行排他ロック
///////////////////////////////////////
T2> SELECT * from test WHERE id = 4 FOR UPDATE;
-- 待たされる


// T2(TRANSACTION 3914) が lock_mode X waiting になっている
mysql> SHOW engine innodb status\G

---TRANSACTION 3914, ACTIVE 7 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 11, OS thread handle 139656706541312, query id 109 localhost root executing
SELECT * from test WHERE id = 4 FOR UPDATE
------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11 page no 4 n bits 72 index GEN_CLUST_INDEX of table `webapp`.`test` trx id 3914 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000000308; asc       ;;
 1: len 6; hex 000000000f44; asc      D;;
 2: len 7; hex 810000008b0110; asc        ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 80000000; asc     ;;

------------------
TABLE LOCK table `webapp`.`test` trx id 3914 lock mode IX
RECORD LOCKS space id 11 page no 4 n bits 72 index GEN_CLUST_INDEX of table `webapp`.`test` trx id 3914 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000000308; asc       ;;
 1: len 6; hex 000000000f44; asc      D;;
 2: len 7; hex 810000008b0110; asc        ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 80000000; asc     ;;

---TRANSACTION 3913, ACTIVE 26 sec
2 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 13, OS thread handle 139656706246400, query id 112 localhost root starting
SHOW engine innodb status
TABLE LOCK table `webapp`.`test` trx id 3913 lock mode IX
RECORD LOCKS space id 11 page no 4 n bits 72 index GEN_CLUST_INDEX of table `webapp`.`test` trx id 3913 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000000308; asc       ;;
 1: len 6; hex 000000000f44; asc      D;;
 2: len 7; hex 810000008b0110; asc        ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 80000000; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000000309; asc       ;;
 1: len 6; hex 000000000f44; asc      D;;
 2: len 7; hex 810000008b011f; asc        ;;
 3: len 4; hex 80000002; asc     ;;
 4: len 4; hex 80000000; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 00000000030a; asc       ;;
 1: len 6; hex 000000000f44; asc      D;;
 2: len 7; hex 810000008b012e; asc       .;;
 3: len 4; hex 80000003; asc     ;;
 4: len 4; hex 80000000; asc     ;;


// T2 の (X) が WAITING になっている
mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734757064:1072:139656633077456
ENGINE_TRANSACTION_ID: 3914
            THREAD_ID: 52
             EVENT_ID: 35
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139656633077456
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734757064:11:4:2:139656633074544
ENGINE_TRANSACTION_ID: 3914
            THREAD_ID: 52
             EVENT_ID: 35
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 139656633074544
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: WAITING
            LOCK_DATA: 0x000000000308
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734756216:1072:139656633071312
ENGINE_TRANSACTION_ID: 3913
            THREAD_ID: 54
             EVENT_ID: 96
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139656633071312
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734756216:11:4:1:139656633068320
ENGINE_TRANSACTION_ID: 3913
            THREAD_ID: 54
             EVENT_ID: 96
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 139656633068320
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734756216:11:4:2:139656633068320
ENGINE_TRANSACTION_ID: 3913
            THREAD_ID: 54
             EVENT_ID: 96
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 139656633068320
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 0x000000000308
*************************** 6. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734756216:11:4:3:139656633068320
ENGINE_TRANSACTION_ID: 3913
            THREAD_ID: 54
             EVENT_ID: 96
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 139656633068320
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 0x000000000309
*************************** 7. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734756216:11:4:4:139656633068320
ENGINE_TRANSACTION_ID: 3913
            THREAD_ID: 54
             EVENT_ID: 96
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 139656633068320
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 0x00000000030A
7 rows in set (0.00 sec)

// T2(3914) が T1(3913) の COMMIT|ROLLBACK を待っている
mysql> SELECT * FROM performance_schema.data_lock_waits\G
*************************** 1. row ***************************
                          ENGINE: INNODB
       REQUESTING_ENGINE_LOCK_ID: 139656734757064:11:4:2:139656633074544
REQUESTING_ENGINE_TRANSACTION_ID: 3914
            REQUESTING_THREAD_ID: 52
             REQUESTING_EVENT_ID: 35
REQUESTING_OBJECT_INSTANCE_BEGIN: 139656633074544
         BLOCKING_ENGINE_LOCK_ID: 139656734756216:11:4:2:139656633068320
  BLOCKING_ENGINE_TRANSACTION_ID: 3913
              BLOCKING_THREAD_ID: 54
               BLOCKING_EVENT_ID: 96
  BLOCKING_OBJECT_INSTANCE_BEGIN: 139656633068320
1 row in set (0.00 sec)



///////////////////////////////////////
// T1: COMMIT
///////////////////////////////////////

T1> INSERT INTO test values (4, 1);
Query OK, 1 row affected (0.00 sec)

T1> COMMIT;
Query OK, 0 rows affected (0.01 sec)




///////////////////////////////////////
// T2: 待たされていた SELECT が実行される
///////////////////////////////////////

// SELECT 結果に T1 の COMMIT 内容が反映されている
T2> 待たされていた SELECT
+------+-------+
| id   | count |
+------+-------+
|    4 |     1 |
+------+-------+
1 row in set (25.53 sec)

// T2 が (IX) を (X) を取得している
mysql> SHOW engine innodb status\G

---TRANSACTION 3914, ACTIVE 38 sec
2 lock struct(s), heap size 1136, 5 row lock(s)
MySQL thread id 11, OS thread handle 139656706541312, query id 109 localhost root
TABLE LOCK table `webapp`.`test` trx id 3914 lock mode IX
RECORD LOCKS space id 11 page no 4 n bits 72 index GEN_CLUST_INDEX of table `webapp`.`test` trx id 3914 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000000308; asc       ;;
 1: len 6; hex 000000000f44; asc      D;;
 2: len 7; hex 810000008b0110; asc        ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 80000000; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000000309; asc       ;;
 1: len 6; hex 000000000f44; asc      D;;
 2: len 7; hex 810000008b011f; asc        ;;
 3: len 4; hex 80000002; asc     ;;
 4: len 4; hex 80000000; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 00000000030a; asc       ;;
 1: len 6; hex 000000000f44; asc      D;;
 2: len 7; hex 810000008b012e; asc       .;;
 3: len 4; hex 80000003; asc     ;;
 4: len 4; hex 80000000; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 00000000030b; asc       ;;
 1: len 6; hex 000000000f49; asc      I;;
 2: len 7; hex 820000008d0110; asc        ;;
 3: len 4; hex 80000004; asc     ;;
 4: len 4; hex 80000001; asc     ;;

mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734757064:1072:139656633077456
ENGINE_TRANSACTION_ID: 3914
            THREAD_ID: 52
             EVENT_ID: 35
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139656633077456
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734757064:11:4:1:139656633074544
ENGINE_TRANSACTION_ID: 3914
            THREAD_ID: 52
             EVENT_ID: 35
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 139656633074544
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734757064:11:4:2:139656633074544
ENGINE_TRANSACTION_ID: 3914
            THREAD_ID: 52
             EVENT_ID: 35
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 139656633074544
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 0x000000000308
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734757064:11:4:3:139656633074544
ENGINE_TRANSACTION_ID: 3914
            THREAD_ID: 52
             EVENT_ID: 35
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 139656633074544
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 0x000000000309
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734757064:11:4:4:139656633074544
ENGINE_TRANSACTION_ID: 3914
            THREAD_ID: 52
             EVENT_ID: 35
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 139656633074544
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 0x00000000030A
*************************** 6. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734757064:11:4:5:139656633074544
ENGINE_TRANSACTION_ID: 3914
            THREAD_ID: 52
             EVENT_ID: 35
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 139656633074544
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 0x00000000030B
6 rows in set (0.00 sec)

// ロック待ちはなし
mysql> SELECT * FROM performance_schema.data_lock_waits\G
Empty set (0.00 sec)

INDEX あり

// INDEX を貼る
mysql> CREATE INDEX id_idx ON test (id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

// 検証前
mysql> SHOW INDEX FROM test;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test  |          1 | id_idx   |            1 | id          | A         |           4 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

mysql> SELECT * FROM test;
+------+-------+
| id   | count |
+------+-------+
|    1 |     0 |
|    2 |     0 |
|    3 |     0 |
|    4 |     1 |
+------+-------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM performance_schema.data_locks\G
Empty set (0.00 sec)

// 検証

T1> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

T2> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)



///////////////////////////////////////
// T1: 存在しない行を行排他ロック
///////////////////////////////////////

mysql> SELECT * from test WHERE id = 5 FOR UPDATE;
Empty set (0.00 sec)

// T1 が (IX) と (X) を取得している
mysql> SHOW engine innodb status\G
---TRANSACTION 3924, ACTIVE 38 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 13, OS thread handle 139656706246400, query id 129 localhost root starting
SHOW engine innodb status
TABLE LOCK table `webapp`.`test` trx id 3924 lock mode IX
RECORD LOCKS space id 11 page no 5 n bits 72 index id_idx of table `webapp`.`test` trx id 3924 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

// T1 が (IX) と (X) を取得している
//  - (X) は supremum pseudo-record(=最小上限レコード(=末尾レコードの後にある疑似レコード)) に対して取得
mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734756216:1072:139656633071312
ENGINE_TRANSACTION_ID: 3924
            THREAD_ID: 54
             EVENT_ID: 119
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139656633071312
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734756216:11:5:1:139656633068320
ENGINE_TRANSACTION_ID: 3924
            THREAD_ID: 54
             EVENT_ID: 119
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: id_idx
OBJECT_INSTANCE_BEGIN: 139656633068320
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
2 rows in set (0.00 sec)

// ロック待ちは未発生
mysql> SELECT * FROM performance_schema.data_lock_waits\G
Empty set (0.00 sec)



///////////////////////////////////////
// T2: 存在しない行を行排他ロック
///////////////////////////////////////

// T1 のロックと競合せずに、排他ロックを取得できる
mysql> SELECT * from test WHERE id = 5 FOR UPDATE;
Empty set (0.00 sec)

// T2(3925) が (IX) と (X) を取得している
mysql> SHOW engine innodb status\G
---TRANSACTION 3925, ACTIVE 6 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 11, OS thread handle 139656706541312, query id 133 localhost root starting
SHOW engine innodb status
TABLE LOCK table `webapp`.`test` trx id 3925 lock mode IX
RECORD LOCKS space id 11 page no 5 n bits 72 index id_idx of table `webapp`.`test` trx id 3925 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

---TRANSACTION 3924, ACTIVE 62 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 13, OS thread handle 139656706246400, query id 131 localhost root
TABLE LOCK table `webapp`.`test` trx id 3924 lock mode IX
RECORD LOCKS space id 11 page no 5 n bits 72 index id_idx of table `webapp`.`test` trx id 3924 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

// T2(3925) が (IX) と (X) を取得している
mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734757064:1072:139656633077456
ENGINE_TRANSACTION_ID: 3925
            THREAD_ID: 52
             EVENT_ID: 39
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139656633077456
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734757064:11:5:1:139656633074544
ENGINE_TRANSACTION_ID: 3925
            THREAD_ID: 52
             EVENT_ID: 39
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: id_idx
OBJECT_INSTANCE_BEGIN: 139656633074544
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734756216:1072:139656633071312
ENGINE_TRANSACTION_ID: 3924
            THREAD_ID: 54
             EVENT_ID: 119
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139656633071312
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734756216:11:5:1:139656633068320
ENGINE_TRANSACTION_ID: 3924
            THREAD_ID: 54
             EVENT_ID: 119
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: id_idx
OBJECT_INSTANCE_BEGIN: 139656633068320
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
4 rows in set (0.00 sec)

// ロック待ちは未発生
mysql> SELECT * FROM performance_schema.data_lock_waits\G
Empty set (0.00 sec)



///////////////////////////////////////
// T1: INSERT
///////////////////////////////////////

T1> INSERT INTO test values (5, 1);
-- 待たされる

// T1(3924) の 挿入インテンションギャップロックが WAITING になっている
mysql> SHOW engine innodb status\G
---TRANSACTION 3925, ACTIVE 24 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 11, OS thread handle 139656706541312, query id 137 localhost root starting
SHOW engine innodb status
TABLE LOCK table `webapp`.`test` trx id 3925 lock mode IX
RECORD LOCKS space id 11 page no 5 n bits 72 index id_idx of table `webapp`.`test` trx id 3925 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

---TRANSACTION 3924, ACTIVE 80 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 13, OS thread handle 139656706246400, query id 136 localhost root update
INSERT INTO test values (5, 1)
------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11 page no 5 n bits 72 index id_idx of table `webapp`.`test` trx id 3924 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

------------------
TABLE LOCK table `webapp`.`test` trx id 3924 lock mode IX
RECORD LOCKS space id 11 page no 5 n bits 72 index id_idx of table `webapp`.`test` trx id 3924 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

RECORD LOCKS space id 11 page no 5 n bits 72 index id_idx of table `webapp`.`test` trx id 3924 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

// T1 は X,INSERT_INTENTION が WAITING になっている
mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734757064:1072:139656633077456
ENGINE_TRANSACTION_ID: 3925
            THREAD_ID: 52
             EVENT_ID: 39
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139656633077456
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734757064:11:5:1:139656633074544
ENGINE_TRANSACTION_ID: 3925
            THREAD_ID: 52
             EVENT_ID: 39
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: id_idx
OBJECT_INSTANCE_BEGIN: 139656633074544
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734756216:1072:139656633071312
ENGINE_TRANSACTION_ID: 3924
            THREAD_ID: 54
             EVENT_ID: 119
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139656633071312
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734756216:11:5:1:139656633068320
ENGINE_TRANSACTION_ID: 3924
            THREAD_ID: 54
             EVENT_ID: 119
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: id_idx
OBJECT_INSTANCE_BEGIN: 139656633068320
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734756216:11:5:1:139656633068664
ENGINE_TRANSACTION_ID: 3924
            THREAD_ID: 54
             EVENT_ID: 123
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: id_idx
OBJECT_INSTANCE_BEGIN: 139656633068664
            LOCK_TYPE: RECORD
            LOCK_MODE: X,INSERT_INTENTION
          LOCK_STATUS: WAITING
            LOCK_DATA: supremum pseudo-record
5 rows in set (0.00 sec)

// T1(3924) が T2(3925) の COMMIT|ROLLBACK を待っている
mysql> SELECT * FROM performance_schema.data_lock_waits\G
*************************** 1. row ***************************
                          ENGINE: INNODB
       REQUESTING_ENGINE_LOCK_ID: 139656734756216:11:5:1:139656633068664
REQUESTING_ENGINE_TRANSACTION_ID: 3924
            REQUESTING_THREAD_ID: 54
             REQUESTING_EVENT_ID: 123
REQUESTING_OBJECT_INSTANCE_BEGIN: 139656633068664
         BLOCKING_ENGINE_LOCK_ID: 139656734757064:11:5:1:139656633074544
  BLOCKING_ENGINE_TRANSACTION_ID: 3925
              BLOCKING_THREAD_ID: 52
               BLOCKING_EVENT_ID: 39
  BLOCKING_OBJECT_INSTANCE_BEGIN: 139656633074544
1 row in set (0.00 sec)


///////////////////////////////////////
// T2: INSERT
///////////////////////////////////////

// デッドロック発生
mysql> INSERT INTO test values (5, 1);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction


///////////////////////////////////////
// T1: T2 が ROLLBACK したので、待たされていた INSERT が実行される
///////////////////////////////////////

T1> 待たされていた INSERT
Query OK, 1 row affected (27.41 sec)

// T2(3925) の デッドロックのログが出ている
//  insert intention がデッドロックした模様
mysql> SHOW engine innodb status\G

------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-10-18 07:56:26 0x7f0444ceb700
*** (1) TRANSACTION:
TRANSACTION 3924, ACTIVE 101 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 13, OS thread handle 139656706246400, query id 136 localhost root update
INSERT INTO test values (5, 1)

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 11 page no 5 n bits 72 index id_idx of table `webapp`.`test` trx id 3924 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11 page no 5 n bits 72 index id_idx of table `webapp`.`test` trx id 3924 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (2) TRANSACTION:
TRANSACTION 3925, ACTIVE 45 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 139656706541312, query id 141 localhost root update
INSERT INTO test values (5, 1)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 11 page no 5 n bits 72 index id_idx of table `webapp`.`test` trx id 3925 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11 page no 5 n bits 72 index id_idx of table `webapp`.`test` trx id 3925 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 3926
Purge done for trx's n:o < 3922 undo n:o < 0 state: running but idle
History list length 57
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421131711467720, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421131711466024, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421131711465176, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 3924, ACTIVE 121 sec
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 13, OS thread handle 139656706246400, query id 142 localhost root starting
SHOW engine innodb status
TABLE LOCK table `webapp`.`test` trx id 3924 lock mode IX
RECORD LOCKS space id 11 page no 5 n bits 72 index id_idx of table `webapp`.`test` trx id 3924 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

RECORD LOCKS space id 11 page no 5 n bits 72 index id_idx of table `webapp`.`test` trx id 3924 lock_mode X insert intention
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

RECORD LOCKS space id 11 page no 5 n bits 72 index id_idx of table `webapp`.`test` trx id 3924 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000030c; asc       ;;

// T1 が下記を取得している
//  ・supremum pseudo-record の X
//  ・supremum pseudo-record の X,INSERT_INTENTION
//  ・id=5 の X,GAP
mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734756216:1072:139656633071312
ENGINE_TRANSACTION_ID: 3924
            THREAD_ID: 54
             EVENT_ID: 119
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139656633071312
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734756216:11:5:1:139656633068320
ENGINE_TRANSACTION_ID: 3924
            THREAD_ID: 54
             EVENT_ID: 119
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: id_idx
OBJECT_INSTANCE_BEGIN: 139656633068320
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734756216:11:5:1:139656633068664
ENGINE_TRANSACTION_ID: 3924
            THREAD_ID: 54
             EVENT_ID: 123
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: id_idx
OBJECT_INSTANCE_BEGIN: 139656633068664
            LOCK_TYPE: RECORD
            LOCK_MODE: X,INSERT_INTENTION
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139656734756216:11:5:6:139656633069008
ENGINE_TRANSACTION_ID: 3924
            THREAD_ID: 54
             EVENT_ID: 123
        OBJECT_SCHEMA: webapp
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: id_idx
OBJECT_INSTANCE_BEGIN: 139656633069008
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 5, 0x00000000030C
4 rows in set (0.00 sec)

// ロック待ちは未発生
mysql> SELECT * FROM performance_schema.data_lock_waits\G
Empty set (0.00 sec)



///////////////////////////////////////
// T1: COMMIT
///////////////////////////////////////

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

// data_lock が空になる
mysql> SELECT * FROM performance_schema.data_locks\G
Empty set (0.00 sec)

検証したい事

バージョン

前提

クラスタインデックス

挿入インテンションギャップロック

検証結果

検証: INDEX 有無による SELECT ... FOR UPDATE とそれに続く INSERT の挙動

INDEX なし

INDEX あり (デッドロックが発生したケース)

デッドロックが発生したケース、しなかったケース

パターン1 (デッドロック発生)

パターン2 (デッドロック未発生)

パターン3 (デッドロック発生)

作業ログ

INDEX なし

INDEX あり

検証: INDEX 有無による `SELECT ... FOR UPDATE` とそれに続く `INSERT` の挙動