タイトルの通り。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
— tkyk04 (@taka_yuki_04) 2024年4月14日
Bug #29508068
多分、この話、これの対応じゃないですかね?
教えていただき解決しました!!ありがとうございます!8.0.18での対応
上記で対応された話ということでした。