mysql 版本 5.7.2
隔离级别 rr
mysql 间隙锁 !不是锁定行,也不是锁定某个列,是锁定对应的索引。测试表 tx_test ,age 字段加了索引了。
-- Table structure for tx_test
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 吗
1
movq 2023-02-10 10:48:33 +08:00
第一个事务后面有 SELECT * from tx_test;,所以要全锁住,不然不是 repeatable read
|
2
rqxiao OP @movq session1 改成 update tx_test set name='aaaaa' where age >1 and age <5;。session2 就可以插入
|
3
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; |
4
wps353 2023-02-10 11:54:03 +08:00 2
@rqxiao
楼主,试试 update tx_test force index(age) set name='aaaaa' where age >1 and age <20; 看看 session2 能不能插入成功? |
6
Chaox 2023-02-10 14:13:51 +08:00
我猜测是你这个数据量太少了,mysql 优化器就没走索引,直接锁表了。所以 4 楼加上 force index(age)走了索引就可以了
网上的资料: 即使完全符合索引生效的场景,考虑到实际数据量等原因,最终是否使用索引还要看 MySQL 优化器的判断。当然你也可以在 sql 语句中写明强制走某个索引。 |
7
lookStupiToForce 2023-02-10 14:19:26 +08:00 1
|
8
wangxin3 2023-02-10 16:40:30 +08:00
8.0.29 亲测无这个问题
|
9
ivanMeng 2023-02-10 17:06:36 +08:00
经过我不断实验和分析 。。。。
explain 可以看到 age<4 和 age <20 走了不通的命中 key 二级索引太少了 还没有 All 快 索引走了 Primarykey 所以锁全表了。。。。 让我也想了多半天 都怀疑间隙锁了。。。。 而且 8 的版本 没毛病。。。 |