mysql 锁范围测试
测试过得版本有
8.0.18
根据唯一索引、非唯一索引和无索引，以及数据库中是否存在查询边界，进行测试。结果如下，其中边界5存在索引中，边界6不在。 数据量新增到7万 测试结果还是一样。
表数据如下图：
id 为主键
userid 为二级索引，非唯一
锁范围矩阵（间隙锁是针对insert来说的）
|
|
唯一
|
索引(产生间隙锁)
|
非唯一
|
索引
|
无 |
索引
|
|
存在边界与否
|
存在边界（5）
|
不存在边界（6）
|
存在边界（5）
|
不存在边界（6）
|
存在边界（5）
|
不存在边界（6)
|
|
小于
|
锁<5(产生间隙锁
|
锁<9（产生间隙锁）
|
锁<=5(产生间隙锁)
|
锁<=9(找到边界9，产生间隙锁)
|
全表行锁
|
全表行锁
|
|
大于
|
锁>5(产生间隙锁
|
锁>=6 (产生间隙锁)
|
锁>5(产生间隙锁)
|
锁>=6( 产生间隙锁)
|
全表行锁
|
全表行锁
|
|
等于
|
锁=5
|
锁=[6,7,8] 间隙锁
|
锁=5（4678产生间隙锁）
|
锁=【6,7,8】 间隙锁
|
全表行锁
|
全表行锁
|
疑问： 非唯一索引 userid=5 时候 为什么 6,7,8 会产生间隙锁，不好理解?
唯一索引测试
小于（存在边界5）：
事务1： UPDATE `t1` SET orderid=20160601161 WHERE id<5
事务2： UPDATE `t1` SET orderid=20160601161 WHERE id=5 ; 事务执行通过
UPDATE `t1` SET orderid=20160601161 WHERE id=2 ; 事务执行通过
UPDATE `t1` SET orderid=20160601161 WHERE id=6 ; 事务执行通过
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (4,2,2,2,2); -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (8,2,2,2,2); -- 事务执行通过
范围结论 ： 锁<5（只锁住已存在的数据，不允许 insert，产生间隙锁）
小于（不存在边界6）：
事务1： UPDATE `t1` SET orderid=20160601161 WHERE id<6
事务2：UPDATE `t1` SET orderid=20160601161 WHERE id=9 事务执行通过
UPDATE `t1` SET orderid=20160601161 WHERE id=6 事务执行通过
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (10,2,2,2,2) -- 事务执行通过
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (7,2,2,2,2) -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (6,2,2,2,2) -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (2,2,2,2,2) -- 事务阻塞
范围结论 ： 锁<9 （范围查询，以找到满足条件的第一个边界值为区间）
大于（存在边界5）：
事务1： UPDATE `t1` SET orderid=20160601161 WHERE id>5
事务2： UPDATE `t1` SET orderid=20160601161 WHERE id=5; -- 事务执行通过
UPDATE `t1` SET orderid=20160601161 WHERE id=9; -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (10,2,2,2,2); -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (6,2,2,2,2); -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (2,2,2,2,2); -- 事务执行通过
范围结论 ： 锁>5（存在边界值，则范围正常）
大于（不存在边界6）：
事务1： UPDATE `t1` SET orderid=20160601161 WHERE id>6
事务2： UPDATE `t1` SET orderid=20160601161 WHERE id=6; -- 事务执行通过
UPDATE `t1` SET orderid=20160601161 WHERE id=5; -- 事务执行通过
UPDATE `t1` SET orderid=20160601161 WHERE id=9; -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (10,2,2,2,2); -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (7,2,2,2,2); -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (6,2,2,2,2); -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (4,2,2,2,2); -- 事务执行通过
范围结论 ： 锁>=6（6间隙锁锁住）
等于（存在边界5）：
事务1： UPDATE `t1` SET orderid=20160601161 WHERE id=5
事务2： UPDATE `t1` SET orderid=20160601161 WHERE id=3; -- 事务执行通过
UPDATE `t1` SET orderid=20160601161 WHERE id=4; -- 事务执行通过
UPDATE `t1` SET orderid=20160601161 WHERE id=6; -- 事务执行通过
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (4,2,2,2,2); -- 事务执行通过
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (6,2,2,2,2); -- 事务执行通过
UPDATE `t1` SET orderid=20160601161 WHERE id=9; 事务执行通过
范围结论 ： 锁=5
等于（不存在边界6）：
事务1： UPDATE `t1` SET orderid=20160601161 WHERE id=6；
事务2： UPDATE `t1` SET orderid=20160601161 WHERE id=5; 事务执行通过
UPDATE `t1` SET orderid=20160601161 WHERE id=7; 事务执行通过
UPDATE `t1` SET orderid=20160601161 WHERE id=9; 事务执行通过
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (6,2,2,2,2); -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (7,2,2,2,2); -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (8,2,2,2,2); -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (10,2,2,2,2); -- 事务阻塞
范围结论 ： 锁=[6,7,8] 间隙锁
非唯一索引测试
小于（存在边界5）：
事务1： UPDATE `t1` SET orderid=20160601161 WHERE userid<5;
事务2： UPDATE `t1` SET orderid=20160601161 WHERE userid=5; -- 事务阻塞
UPDATE `t1` SET orderid=20160601161 WHERE userid=3; -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (2,2,2,2,2) -- 事务阻塞，间隙锁
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (6,6,2,2,2) -- 事务执行通过
UPDATE `t1` SET orderid=20160601161 WHERE userid=9; -- 事务执行通过
UPDATE `t1` SET orderid=20160601161 WHERE userid=11; -- 事务执行通过
范围结论 ：锁<=5(不存在的数据，间隙锁，不允许插入)
小于（不存在边界6）：
事务1： UPDATE `t1` SET orderid=20160601161 WHERE userid<6;
事务2： UPDATE `t1` SET orderid=20160601161 WHERE userid=6; -- 事务执行通过
UPDATE `t1` SET orderid=20160601161 WHERE userid=9; -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (6,2,2,2,2) -- 事务阻塞，间隙锁
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (8,2,2,2,2) -- 事务阻塞，间隙锁
UPDATE `t1` SET orderid=20160601161 WHERE userid=11; -- 事务执行通过
UPDATE `t1` SET orderid=20160601161 WHERE userid=2; -- 事务执行通过
范围结论 ： 锁<=9(找到边界9，6.7.8 间隙锁)
大于（存在边界5）：
事务1： UPDATE `t1` SET orderid=20160601161 WHERE userid>5;
事务2： UPDATE `t1` SET orderid=20160601161 WHERE userid=5; -- 事务执行通过
UPDATE `t1` SET orderid=20160601161 WHERE userid=9; -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (4,4,2,2,2) -- 事务执行通过
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (6,6,2,2,2) -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (7,7,2,2,2) -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (10,10,2,2,2) -- 事务阻塞
范围结论 ： 锁>5(产生间隙锁)
大于（不存在边界6）：
事务1： UPDATE `t1` SET orderid=20160601161 WHERE userid>6;
事务2： UPDATE `t1` SET orderid=20160601161 WHERE userid=6; -- 事务执行通过
UPDATE `t1` SET orderid=20160601161 WHERE userid=9; -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (6,6,2,2,2) -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (7,7,2,2,2) -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (10,10,2,2,2) -- 事务阻塞
范围结论 ： 锁>=6( 产生间隙锁)
等于（存在边界5）：
事务1： UPDATE `t1` SET orderid=20160601161 WHERE userid=5
事务2： UPDATE `t1` SET orderid=20160601161 WHERE userid=9; -- 事务执行通过
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (2,2,2,2,2) -- 事务执行通过
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (4,4,2,2,2) -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (6,6,2,2,2) -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (7,7,2,2,2) -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (8,8,2,2,2) -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (10,10,2,2,2) -- 事务执行通过
范围结论 ： 锁=5；【产生间隙锁】
等于（不存在边界6）：
事务1： UPDATE `t1` SET orderid=20160601161 WHERE userid=6；
事务2：
UPDATE `t1` SET orderid=20160601161 WHERE userid=9; -- 事务执行通过
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (6,6,2,2,2) -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (7,7,2,2,2) -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (8,8,2,2,2) -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (10,10,2,2,2) -- 事务执行通过
范围结论 ： 锁=【6,7,8】 产生间隙锁
无索引测试
小于（存在边界5）：
事务1： UPDATE `t1` SET orderid=20160601161 WHERE orderid<5;
事务2： UPDATE `t1` SET orderid=20160601161 WHERE orderid=5; -- 事务阻塞
UPDATE `t1` SET orderid=20160601161 WHERE orderid=9; -- 事务阻塞
UPDATE `t1` SET orderid=20160601161 WHERE orderid=11; -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (6,6,6,2,2) -- 事务阻塞
范围结论 ：全表行锁
小于（不存在边界6）：
事务1： UPDATE `t1` SET orderid=20160601161 WHERE orderid<6;
事务2： INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (7,7,7,2,2) -- 事务阻塞
UPDATE `t1` SET orderid=20160601161 WHERE orderid=9; -- 事务阻塞
UPDATE `t1` SET orderid=20160601161 WHERE orderid=11; -- 事务阻塞
范围结论 ：全表行锁
大于（存在边界5）：
事务1： UPDATE `t1` SET orderid=20160601161 WHERE orderid>5;
事务2： UPDATE `t1` SET orderid=20160601161 WHERE orderid=5; -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (4,4,4,2,2) -- 事务阻塞
UPDATE `t1` SET orderid=20160601161 WHERE orderid=9; -- 事务阻塞
UPDATE `t1` SET orderid=20160601161 WHERE orderid=11; -- 事务阻塞
范围结论 ：全表行锁
大于（不存在边界6）：
事务1： UPDATE `t1` SET orderid=20160601161 WHERE orderid>6;
事务2： UPDATE `t1` SET orderid=20160601161 WHERE orderid=1; -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (4,4,4,2,2) -- 事务阻塞
UPDATE `t1` SET orderid=20160601161 WHERE orderid=9; -- 事务阻塞
UPDATE `t1` SET orderid=20160601161 WHERE orderid=11; -- 事务阻塞
范围结论 ：全表行锁
等于（存在边界5）：
事务1： UPDATE `t1` SET orderid=20160601161 WHERE orderid=5;
事务2： UPDATE `t1` SET orderid=20160601161 WHERE orderid=5; -- 事务阻塞
UPDATE `t1` SET orderid=20160601161 WHERE orderid=1; -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (4,4,4,2,2) -- 事务阻塞
UPDATE `t1` SET orderid=20160601161 WHERE orderid=9; -- 事务阻塞
UPDATE `t1` SET orderid=20160601161 WHERE orderid=11; -- 事务阻塞
范围结论 ：全表行锁
等于（不存在边界6）：
事务1： UPDATE `t1` SET orderid=20160601161 WHERE orderid=6;
事务2： UPDATE `t1` SET orderid=20160601161 WHERE orderid=1; -- 事务阻塞
INSERT INTO `t1`(`id`,`userId`,`orderId`,`channerId`,`amount`) VALUES (4,4,4,2,2) -- 事务阻塞
UPDATE `t1` SET orderid=20160601161 WHERE orderid=9; -- 事务阻塞
UPDATE `t1` SET orderid=20160601161 WHERE orderid=11; -- 事务阻塞
范围结论 ：全表行锁