目的
本文系对MySQL InnoDB中的行锁 Next-Key Lock消除幻读 的补充。文中有些细节没有涉及或者描述的不是太清楚。
InnoDB引擎的锁技术
锁的类型
1、共享锁(S)
2、排他锁(X)
3、意向共享锁(IS)
4、意向排他(IX)
锁的算法
假设有一个表:
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`age` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index1` (`age`),
KEY `index2` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
INSERT INTO `test`.`student`(`id`, `age`, `name`) VALUES (1, 10, '10');
INSERT INTO `test`.`student`(`id`, `age`, `name`) VALUES (2, 20, '20');
INSERT INTO `test`.`student`(`id`, `age`, `name`) VALUES (3, 30, '30');
InnoDB引擎有以下几种锁的方法:
1、Record Lock
Record Lock是作用于索引记录的锁。例如
select * from student where age = 10 for update;
其他会话执行以下sql插不进去(阻塞):
INSERT INTO `test`.`student`( `age`, `name`) VALUES (10, '10');
Record Lock作用于索引记录,如果一个表连主键索引都没有,MySQL会增加一个隐藏索引。
2、Gap Lock
Gap Lock是间隙锁,作用于索引区间范围。例如:
select * from student where age between 10 and 20 for update;
其他会话执行以下sql插不进去(阻塞):
INSERT INTO `test`.`student`( `age`, `name`) VALUES (15, '15');
3、Next-Key Lock
Next-Key Lock相当于Record Lock + Gap Lock
对于student表的初始化数据而言,Next-Key lock有几下几个:
(负无穷,10]
(10,20]
(20,30]
(30,正无穷)
下面的sql除了会锁住(10,20]还会锁住(负无穷,10] (20,30]
select * from student where age between 10 and 20 for update;
其他会话执行以下sql均插不进去(阻塞):
INSERT INTO `test`.`student`( `age`, `name`) VALUES (1, '1');
INSERT INTO `test`.`student`( `age`, `name`) VALUES (9, '9');
INSERT INTO `test`.`student`( `age`, `name`) VALUES (10, '10');
INSERT INTO `test`.`student`( `age`, `name`) VALUES (20, '20');
INSERT INTO `test`.`student`( `age`, `name`) VALUES (29, '29');
但下面的sql不会阻塞:
INSERT INTO `test`.`student`( `age`, `name`) VALUES (30, '30');
测试2
select * from student where age between 15 and 16 for update;
锁定的是(10,20]范围。
其他会话执行以下sql均插不进去(阻塞):
INSERT INTO `test`.`student`( `age`, `name`) VALUES (10, '10');
INSERT INTO `test`.`student`( `age`, `name`) VALUES (19, '19');
其他会话执行以下sql可以插入成功:
INSERT INTO `test`.`student`( `age`, `name`) VALUES (1, '1');
INSERT INTO `test`.`student`( `age`, `name`) VALUES (9, '9');
INSERT INTO `test`.`student`( `age`, `name`) VALUES (20, '20');
测试3
select * from student where age = 20 for update;
锁定的是:(10,20] (20,30]
其他会话执行以下sql均插不进去(阻塞):
INSERT INTO `test`.`student`( `age`, `name`) VALUES (10, '10');
INSERT INTO `test`.`student`( `age`, `name`) VALUES (20, '20');
其他会话执行以下sql可以插入成功:
INSERT INTO `test`.`student`( `age`, `name`) VALUES (30, '30');
总结
Gap Lock和Next-Key Lock存在的意义是能够防止幻读的发生。
Gap Lock和Next-key Lock发生的条件是:InnoDB引擎 & RR事务隔离级别 && 共享锁/独占锁。