mysql 间隙锁

DROP TABLE IF EXISTS `tx_test`; CREATE TABLE `tx_test` ( `id` int(11) NOT NULL, `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL, `age` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `age` (`age`), KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- Records of tx_test

BEGIN; INSERT INTO `tx_test` (`id`, `name`, `age`) VALUES (1, '123', 3); INSERT INTO `tx_test` (`id`, `name`, `age`) VALUES (2, '44', 4); INSERT INTO `tx_test` (`id`, `name`, `age`) VALUES (3, '55', 5); INSERT INTO `tx_test` (`id`, `name`, `age`) VALUES (4, '41', 50); COMMIT;

#session1

begin;

SELECT * from tx_test;

update tx_test set name='aaaaa' where age >1 and age <20;

SELECT * from tx_test;

COMMIT;

#session2

begin;

SELECT * from tx_test;

INSERT INTO tx_test (id, name, age) VALUES (88, '41', 77);

SELECT * from tx_test;

COMMIT;

为什么 session2 的 INSERT INTO tx_test (id, name, age) VALUES (88, '41', 77); 会阻塞呢。session1 的查询不是应该锁定了 -无穷到 50 吗

10 条回复 2023-02-10 17:17:01 +08:00

movq

2023-02-10 10:48:33 +08:00

第一个事务后面有 SELECT * from tx_test;，所以要全锁住，不然不是 repeatable read

rqxiao

2023-02-10 11:00:47 +08:00

@movq session1 改成 update tx_test set name='aaaaa' where age >1 and age <5;。session2 就可以插入

Inf1nity

2023-02-10 11:08:35 +08:00

我这边在 MySQL 8.0 下面测试的 session1 的查询执行（未 commit ）后，给 age = 3, age = 4, age = 5 的辅助索引加了 next key lock ，同时给对应的主键 id = 1, id = 2, id = 3 加了 record lock ，之后执行 session2 并没有被阻塞。
楼主可以试试用下面这个查询查看加锁情况：

SELECT
ENGINE,
EVENT_ID,
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME,
LOCK_TYPE,
LOCK_MODE,
LOCK_STATUS,
LOCK_DATA
FROM performance_schema.data_locks;

wps353

2023-02-10 11:54:03 +08:00

@rqxiao
楼主，试试 update tx_test force index(age) set name='aaaaa' where age >1 and age <20;
看看 session2 能不能插入成功？

rqxiao

2023-02-10 13:48:10 +08:00

@wps353 可以插入成功，这个 update 操作还会区分数据分部情况来判断锁不锁索引吗。。

Chaox

2023-02-10 14:13:51 +08:00

我猜测是你这个数据量太少了，mysql 优化器就没走索引，直接锁表了。所以 4 楼加上 force index(age)走了索引就可以了
网上的资料：
即使完全符合索引生效的场景，考虑到实际数据量等原因，最终是否使用索引还要看 MySQL 优化器的判断。当然你也可以在 sql 语句中写明强制走某个索引。

lookStupiToForce

2023-02-10 14:19:26 +08:00

顺道提一嘴
mysql 从 5.6 开始就支持 explain update 了
https://dev.mysql.com/doc/refman/5.6/en/explain.html

ponder09

2023-02-10 16:40:30 +08:00

8.0.29 亲测无这个问题

ivanMeng

2023-02-10 17:06:36 +08:00

经过我不断实验和分析。。。。
explain 可以看到 age<4 和 age <20 走了不通的命中 key
二级索引太少了还没有 All 快索引走了 Primarykey 所以锁全表了。。。。
让我也想了多半天都怀疑间隙锁了。。。。
而且 8 的版本没毛病。。。

koloonps

2023-02-10 17:17:01 +08:00

@ivaneng @wangxin3 为什么我的 8.0.29 有这个问题........