测试准备
1.环境准备
mysql版本为5.7.28
确保事务隔离级别为repeatable read,可以使用以下语句查看和设置
SELECT @@SESSION.transaction_isolation, @@SESSION.transaction_read_only;
SET SESSION TRANSACTION ISOLATION LEVEL repeatable read;
2.数据准备
CREATE TABLE `t` (
`i` int(11) NOT NULL,
`j` int(11) NOT NULL,
`k` int(11) NOT NULL,j
KEY `j` (`j`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
BEGIN;
INSERT INTO `t` VALUES (-10, -10, -10);
INSERT INTO `t` VALUES (-10, -10, -10);
INSERT INTO `t` VALUES (-10, -10, -10);
INSERT INTO `t` VALUES (-10, -10, -10);
INSERT INTO `t` VALUES (-10, -10, -10);
INSERT INTO `t` VALUES (-10, -10, -10);
INSERT INTO `t` VALUES (-10, -10, -10);
INSERT INTO `t` VALUES (0, 0, 0);
INSERT INTO `t` VALUES (10, 10, 10);
INSERT INTO `t` VALUES (20, 20, 20);
INSERT INTO `t` VALUES (30, 30, 30);
INSERT INTO `t` VALUES (40, 40, 40);
INSERT INTO `t` VALUES (50, 50, 50);
INSERT INTO `t` VALUES (60, 60, 60);
COMMIT;
3.测试须知
确保查询在normal index(结构为B树)上进行,使用explain来查看将要执行的select的执行计划,确保查询使用索引。我特意在表中插入了多条键值为-10的记录,原因就是在测试时发现即使在select语句的where条件中使用索引列,mysql有一部分select最终使用(隐含的)聚集索引进行全表扫描而不是使用索引查询,这样就对最终的结果产生了干扰。 以上问题的原因是mysql根据表的统计信息对查询进行了优化。
测试
测试语句
事务1:
start transaction;
select * from t where ...for update;
rollback;
事务2:
start transaction;
insert into t values(...);
rollback;
事务1和事务2并发执行,每次测试完成后执行rollback,避免误插入导致对后续测试造成干扰,可以使用以下语句查看锁和事务信息。
查看事务
select * from information_schema.innodb_trx;
查看正在锁的事务
select * from information_schema.innodb_locks;
查看等待锁的事务
select * from information_schema.innodb_lock_waits;
杀死指定事务对应的线程
kill <information_schema.innodb_trx表中trx_mysql_thread_id>
测试结果
# | 事务1查询条件 | 事务2被阻塞而得出的锁定范围 | 备注 |
---|---|---|---|
1 | j=10 | [0, 20) | |
2 | j=13 | [10, 20) | |
3 | j in(15, 40) | [10, 20) union [30, 50) | 相当于等值查询时锁产生的锁定范围的并集 |
4 | j in(15, 45) | [10, 20) union [40, 50) | 相当于等值查询时锁产生的锁定范围的并集 |
5 | j in(10, 40) | [0, 20) union [30, 50) | 相当于等值查询时锁产生的锁定范围的并集 |
6 | j in(10, 45) | [0, 20) union [40, 50) | 相当于等值查询时锁产生的锁定范围的并集 |
7 | j<10 | (-infinity, 10) | |
8 | j<13 | (-infinity, 20) | |
9 | j>10 | [10, +infinity) | |
10 | j>13 | [10, +infinity) | |
11 | j<=10 | (-infinity, 20) | |
12 | j<=13 | (-infinity, 20) | |
13 | j>=10 | [0, +infinity) | |
14 | j>=13 | [10, +infinity) | |
15 | j between 7 and 30 | [0, 40) | |
16 | j between 7 and 33 | [0, 40) | |
17 | j between 10 and 40 | [0, 50) | |
18 | j between 10 and 43 | [0, 50) |
测试总结(计算锁定范围)
1.将查询条件分解为区间
# | 查询条件 | 分解区间 | 备注 |
---|---|---|---|
1 | j=10 | [10, 10] | |
2 | j<10 | (-infinity, 10) | |
3 | j<=10 | (-infinity, 10] | |
4 | j>10 | (10, +infinity) | |
5 | j>=10 | [10, +infinity) | |
6 | j between a and b | [a, b] | |
7 | j in(a, b) | [a, a] union [b, b] | 相当于等值条件产生范围的并集 |
2.计算单个分解区间对应的最终锁定区间
1.如果分解区间存在-infinity、+infinity的端点,则无需计算。
2.计算最终锁定区间的左边端点:根据索引的键值查找,找到分解区间的最大范围的左侧区间,找到该区间中最大的索引键值,如果找不到就是-infinity,假设其值为LEFT;
3.计算最终锁定区间左边端点:根据索引的键值查找,找到分解区间的最大范围的右侧区间,找到该区间中最小的索引键值,如果找不到就是+infinity,假设其值为RIGHT;
4.最终的锁定区间为:[LEFT, RIGHT)