博客

MySQL笔记（六）MySQL间隙锁的加锁规律

  • Post author:
  • Post category:mysql


MySQL笔记（六）MySQL间隙锁的加锁规律

以下只讨论在MySQL的InnoDB引擎默认隔离级别（可重复读级别）下的状况。



创建示例student表结构如下

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` bigint(20) NOT NULL,
  `student_no` bigint(20) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `stu_no`(`student_no`) USING BTREE,
  INDEX `age`(`age`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

在这里插入图片描述



next-key lock的加锁原则

  1. 加锁的基本单位是临键锁（next-key lock）,他是前开后闭原则
  2. 插叙过程中访问的对象会增加锁
  3. 索引上的等值查询–给唯一索引加锁的时候，next-key lock升级为行锁
  4. 索引上的等值查询–向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁
  5. 唯一索引上的范围查询会访问到不满足条件的第一个值为止


在测试的过程还需要注意的是，使用explain查看sql语句是否走了索引，可能会由于mysql优化的原因，导致sql没有走索引，从而导致测试锁的情况与预想的不一致

。



普通索引：



等值查询 next-key lock，（不论有没有查到）相邻的数据，产生左开右开的间隙锁

时间点 SessionA SessionB SessionC
1 begin;

select * from student where age=12 for update;
2 insert into student(id,student_no,name,age) value(5,201528,‘周’,13);

被阻塞 		update student set

name

=

name

where age=14;

成功执行
3 commit;
4 成功执行

普通索引的等值查询，一定会是左开右开的锁，因此这里锁住的是（10,14）的范围，所以会话2在范围内就被阻塞了，而会话3不在范围内，就能够成功执行。



范围查询 next-key lock ， （不论有没有查到）相邻的数据，产生左开右闭的锁

时间点 SessionA SessionB SessionC
1 begin;

select * from student where age>11 and age<13 for update;
2 insert into student(id,student_no,name,age) value(5,201528,‘周’,13);

被阻塞 		update student set

name

=

name

where age=14;

被阻塞
3 commit;
4 成功执行 成功执行

普通索引的范围查询，一定会产生左开右闭的锁，因此这里锁住的是（10,14】的范围，13和14都在范围内，所以会话1和会话2都会被阻塞。



唯一索引：



等值查询 （优化为行锁），有数据则是记录锁，没数据则是间隙锁

时间点 SessionA SessionB SessionC
1 begin;

select * from student where student_no=201523 for update;
2 insert into student(id,student_no,name,age) value(5,201523,‘周’,13);

被阻塞 		update student set

name

=

name

where student_no=201524;

成功执行
3 commit;
4 成功执行

唯一索引的等值查询，一定是产生的这一行的行锁，不论有没有数据，所以只有201523这一行被上锁了，所以会话2就会被阻塞，会话3则成功执行。



范围查询 Next-Key Lock ，（不论有没有查到）相邻的数据，产生左开右闭的锁

时间点 SessionA SessionB SessionC
1 begin;

select * from student where student_no>201523 and student_no<201525 for update;
2 update student set

name

=

name

where student_no=201522;

成功执行 		update student set

name

=

name

where student_no=201526;

被阻塞
3 commit
4 成功执行

根据左开右闭的原则，这里会锁住（201522,201526】的范围，因此会话2成功执行，会话3被阻塞。


版权声明：本文为weixin_44228698原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
Tags: