タイトルの通り。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)
追記
教えていただき解決しました!!ありがとうございます!8.0.18での対応
bugs.mysql.com
上記で対応された話ということでした。