mysql行锁分析一
mysql锁的原则
- 原则 1：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。
- 原则 2：查找过程中访问到的对象才会加锁。
- 2.1： 若字段上无索引，因要根据id查询所有的数据，所以所有数据均会加上next-key lock
- 2.2： 若字段上有普通索引，则只有访问到的数据添加next-key lock，但是最后一条由于不满足，所以根据优化2，退化为间隙锁
- 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
- 3.1 原因为唯一索引不可再插入该值，所以等值查询不会引起问题
- 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
- 一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
准备工作
- 准备sql
CREATE TABLE `t_student` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
`age` int(10) DEFAULT NULL,
`class` int(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `class` (`class`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;
insert into t_student(id,`name`,`age`,`class`)values(1,"student-1",1,1);
insert into t_student(id,`name`,`age`,`class`)values(5,"student-5",5,5);
insert into t_student(id,`name`,`age`,`class`)values(10,"student-10",10,10);
insert into t_student(id,`name`,`age`,`class`)values(15,"student-15",15,15);
insert into t_student(id,`name`,`age`,`class`)values(20,"student-20",20,20);
- 确认当前隔离级别是可重复读
show variables like '%isolation';
- 打开两个mysql连接A/B,并且设置为不自动提交
set autocommit=0;
show variables like 'autocommit';
实验1-验证原则1
操作
- A client执行update语句
update t_student set `name`='student-10-1' where `class`=10;
- B client以下 语句
insert into t_student(`name`,`age`,`class`)values("student-3",3,3);-- 1
insert into t_student(`name`,`age`,`class`)values("student-5",5,5); -- 2
insert into t_student(`name`,`age`,`class`)values("student-8",8,8); -- 3
insert into t_student(`name`,`age`,`class`)values("student-10",10,10); -- 4
insert into t_student(`name`,`age`,`class`)values("student-13",13,13); -- 5
insert into t_student(`name`,`age`,`class`)values("student-15",15,15);-- 6
insert into t_student(`name`,`age`,`class`)values("student-18",18,18); -- 7
select * from t_student where class = 5 for update; -- 8
select * from t_student where class = 7 for update;-- 9
select * from t_student where class = 10 for update;-- 10
select * from t_student where class = 12 for update;-- 11
select * from t_student where class = 15 for update;-- 12
select * from t_student where id = 10 for update;-- 13
select * from t_student where age = 10 for update;-- 14
insert into t_student (id)values(9);-- 15
结果
2,3,4,5 插入阻塞；10修改阻塞
分析
- 9未锁定表明：间隙锁的作用只是用来阻止其他事务在间隙中插入数据，而不会锁定update，虽然update并不会有真实的数据被修改
- 3，4，5被锁定表明：class索引树上的锁定范围是(5,15),印证了上述的结论 原则1和原则2.2
- 2执行锁定，8未锁定表明：class==5这条记录并没有被锁定，但是当插入新的记录并且class=5时，这条数据会插入到class=(5,10]的间隙中（虽然class的值为5），所以插入失败
- 10,13,14被锁定，表明这条数据被锁定
- 15执行成功，表明主键只锁定这条记录，不会锁定间隙锁，印证了优化1
验证原则2.1
操作
- A client执行update语句
update t_student set `name`='student-10-1' where `age`=10;
- B client执行以下语句
insert into t_student(age) values(1); -- 1
insert into t_student(age) values(3); -- 2
insert into t_student(age) values(5); -- 3
insert into t_student(age) values(8);-- 4
insert into t_student(age) values(10); -- 5
insert into t_student(age) values(13);-- 6
insert into t_student(age) values(15);-- 7
insert into t_student(age) values(18);-- 8
insert into t_student(age) values(20);-- 9
select * from t_student where id = 1 for update; -- 10
select * from t_student where id = 5 for update; -- 11
select * from t_student where id = 10 for update; -- 12
select * from t_student where id = 13 for update; -- 13
select * from t_student where id = 15 for update; -- 14
select * from t_student where id = 20 for update; -- 15
insert into t_student (id) values(9); -- 16
insert into t_student (id) values(3); -- 17
结果
除 13外其它均阻塞
分析
- 当对无索引的字段进行修改时，需要进行全表扫描，故锁定所有的数据，印证原则2.1
对索引执行update发现锁住了整个表
操作
- A client
update t_student set `name` = 's' where `class`<=10
- B client
insert into t_student(class) values(1);-- 1
insert into t_student(class) values(10);-- 2
insert into t_student(class) values(13);-- 3
insert into t_student(class) values(15);-- 4
insert into t_student(class) values(18);-- 5
insert into t_student(class) values(22);-- 6
结果
1~6均无法执行成功
分析
通过explain select * from t_student where class<=10发现未使用索引class,可通过update t_student force index(class) set name = 's' where class<=10 ;进行update操作
范围查询
准备表
CREATE TABLE `t_student` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
`age` int(10) DEFAULT NULL,
`class` int(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `class_age` (`class`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8;
操作一
- clint A 执行语句
select * from t_student where class=15 for update
- client B执行语句
-- 执行成功
select * from t_student where class=10 for update;
-- 阻塞
insert into t_student (id,name,class,age)values(11,'student-11',11,11) ;
-- 阻塞
select * from t_student where class=15 for update ;
-- 阻塞
insert into t_student (id,name,class,age)values(19,'student-19',19,19) ;
-- 执行成功
select * from t_student where class=20 for update;
操作二
- client A执行语句
select * from t_student force index(class_age) where class=15 and age<>10 for update;
- client B执行语句
select * from t_student where class=10 for update;-- 执行成功
insert into t_student (id,name,class,age)values(11,'student-11',11,11) ; -- 阻塞
select * from t_student where class=15 for update -- 阻塞
insert into t_student (id,name,class,age)values(19,'student-19',19,19) ; -- 阻塞
select * from t_student where class=20 for update; -- 阻塞
insert into t_student (id,name,class,age)values(21,'student-21',21,21) ; -- 执行成功
结果分析
- 通过explain分析上述两个select语句，发现一个是ref一个是range
- 当索引等值查询时，如果最后一个不满足条件，则退化为间隙锁，即操作一锁的范围是(10,15],(15,20)
- 当索引范围查询时，最后一个不满足条件，不会退化为间隙锁，即操作二锁的范围是（10,15],(15,20]
- 注意确认使用索引class
insert及update产生的死锁
准备表
按如下重建上述表结构，然后插入上述数据
CREATE TABLE `t_student` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
`age` int(10) DEFAULT NULL,
`class` int(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `class_age` (`class`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8;
操作一
- client A 执行insert
insert into t_student (id,name,class,age)values(26,'student-26',26,26) ;
- client B执行insert
insert into t_student (id,name,class,age)values(27,'student-27',27,27) ;
- client A执行update
update t_student set name='student-26-2' where class=26 ;
- client B执行update
update t_student set name='student-27-2' where class=27;
- 发现正常执行
- 分析sql
explain select * from t_student where class=27
操作二
-
- client A 执行insert
insert into t_student (id,name,class,age)values(26,'student-26',26,26) ;
-
- client B执行insert
insert into t_student (id,name,class,age)values(27,'student-27',27,27) ;
-
- client A执行update
update t_student set name='student-26-2' where class=26 and age<>25 ;
-
- client B执行update
update t_student set name='student-27-2' where class=27 and age<>25;
-
发现出现死锁
-
分析sql
explain select * from t_student where class=27 and age<>25;
示例分析
- 间隙锁与间隙锁间不互斥，即同一个间隙可多条insert语句同时执行,即两个不同的客户端分别执行以下语句，虽然都锁定了(10,15)的间隙锁，但是并不会阻塞
select * from t_student where class=10 for update;
select * from t_student where class=15 for update;
- 间隙锁与记录锁互斥，即锁定的间隙无法执行插入操作
- 操作1的锁的范围分析
- 两个insert 分别锁定26，27的数据记录
- client A的update执行时，表中的数据为25，26,27而且是索引条件，故锁的范围为(25,27)
- client B的update执行时，表中的数据为25，26,27而且是索引条件，故锁的范围为(26,+∞)
- client A和client B重复的锁的范围为间隙锁(26,27),故均可正常执行
- 操作2的锁的范围分析
- 两个insert 分别锁定26，27的数据记录
- client A的update执行时，表中的数据为25，26,27，但是条件是索引范围查询，故锁的范围为(25,27],需要等待1释放锁
- client B的update执行时，表中的数据为25，26，27，但是条件是索引范围查询，故锁的范围为(26,+∞)，需要等待3释放锁
- 由于A依赖B,B依赖A,故出现死锁
- 如果将3和4的顺序对调，则正常执行不会出现死锁
mysql 其它insert语句锁
MySQL 对自增主键锁做了优化，尽量在申请到自增 id 以后，就释放自增锁。
因此，普通的insert 语句是一个很轻量的操作。但是还有些 insert 语句是属于“特殊情况”的，在执行过程中需要给其他资源加锁，或者无法在申请到自增 id 以后就立马释放自增锁。
insert … select 语句
在可重复读隔离级别下，这个语句会给 select 的表里扫描到的记录和间隙加读锁。
insert 循环写入
insert into t2(c,d) (select c+1, d from t force index(c) order by c desc limit 1);
这个语句的加锁范围，就是表 t 索引 c 上的 (3,4] 和 (4,supremum] 这两个 next-key lock，以及主键索引上 id=4 这一行。
insert into t(c,d) (select c+1, d from t force index(c) order by c desc limit 1);
而如果 insert 和 select 的对象是同一个表，则有可能会造成循环写入。这种情况下，我们需要引入用户临时表来做优化。
也就是说，这个语句会导致在表 t 上做全表扫描，并且会给索引 c 上的所有间隙都加上共享的 next-key lock。所以，这个语句执行期间，其他事务不能在这个表上插入数据
insert 唯一键冲突
insert 语句如果出现唯一键冲突，会在冲突的唯一值上加共享的 next-key lock(S 锁)。因此，碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长。
insert into ... on duplicate key update
该语句会给唯一索引加一个排他的 next-key lock（写锁）。
如果有多个列违反了唯一性约束，就会按照索引的顺序，修改跟第一个索引冲突的行。