https://ryuichi1208.hateblo.jp/entry/2024/04/14/090721

タイトルの通り。MySQL 5.7の時に「あ、そこもロック取るんだ」って思っていたやつが8.0から挙動が変わったのか見てみる。8.0.36を使ってます。リピータブルリードで検証。

mysql> SHOW VARIABLES LIKE '%isolation%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.02 sec)

こんなテーブルでこんなレコードが入っているとする。

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `col_pk` int NOT NULL AUTO_INCREMENT,
  `col2` int DEFAULT NULL,
  `col3` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`col_pk`),
  KEY `col2` (`col2`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> select * from t1;
+--------+------+------+
| col_pk | col2 | col3 |
+--------+------+------+
|      1 |   10 | AAA  |
|      6 |   60 | ZZZ  |
+--------+------+------+
2 rows in set (0.00 sec)

この状態でギャップロックをTxAでかけていく

mysql> begin; select * from t1 where col_pk > 3 and col_pk < 5 for update;
Query OK, 0 rows affected (0.00 sec)

Empty set (0.00 sec)

ロックの状態はこう。PK=6とGapロックがかかっている。

mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 281472607349976:1068:281472493736880
ENGINE_TRANSACTION_ID: 1824
            THREAD_ID: 48
             EVENT_ID: 33
        OBJECT_SCHEMA: mydb
          OBJECT_NAME: t1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 281472493736880
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 281472607349976:2:4:3:281472493733888
ENGINE_TRANSACTION_ID: 1824
            THREAD_ID: 48
             EVENT_ID: 33
        OBJECT_SCHEMA: mydb
          OBJECT_NAME: t1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 281472493733888
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 6
2 rows in set (0.00 sec)

この状態では 1<PK<5へのINSERTはできない

mysql> insert into t1(col_pk,col2,col3) values (2,20,'AAA');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

PK=6もロックを取るのがMySQLのリピータブルリードの仕様(ファジーリードを防ぐための仕組み)だと思っていたのだが普通にアップデートができてしまった。

mysql> begin; UPDATE t1 SET col3='DDD' WHERE col_pk=6;
Query OK, 0 rows affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+--------+------+------+
| col_pk | col2 | col3 |
+--------+------+------+
|      1 |   10 | AAA  |
|      6 |   60 | DDD  |
+--------+------+------+
2 rows in set (0.00 sec)

PK=6をDELETEしてもギャップが広がるわけではない

mysql> delete from t1 where col_pk = 6;
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1(col_pk,col2,col3) values (7,20,'AAA');
Query OK, 1 row affected (0.01 sec)

追記

https://t.co/ULEDZ5BIQv
Bug #29508068
多分、この話、これの対応じゃないですかね？
— tkyk04 (@taka_yuki_04) 2024年4月14日

教えていただき解決しました!!ありがとうございます！8.0.18での対応

bugs.mysql.com

上記で対応された話ということでした。