表user结构如下:
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT '',
`score` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `index_score` (`score`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
当前的user表数据如下:
id | name | score |
---|---|---|
1 | test1 | 5 |
2 | test2 | 10 |
3 | test3 | 15 |
4 | test4 | 22 |
我测试gap锁区间的sql如下:
事务隔离级别为RR
session 1 | session 2 |
---|---|
begin; | begin; |
update user set name = 'test' where score = 15; | - |
- | insert into user values(5, 'test5', 10); # 阻塞 |
- | insert into user values(5, 'test5', 21); # 阻塞 |
commit; | - |
- | commit; |
Suppose that an index contains the values 10, 11, 13, and 20. The possible next-key locks for this index cover the following intervals, where a round bracket denotes exclusion of the interval endpoint and a square bracket denotes inclusion of the endpoint:(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
按照上面的说明,gap锁的区间应该是(10, 15],(15, 22],也就是(10, 22],但是我实际测试发现gap锁区间为[10, 22)?
环境说明:
System: ubuntu 16.04
Mysql Version: 5.5.56
最近也在看这一块,虽然知道晚了,万一以后有小伙伴有需要呢。。。
