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

操作二

    1. client A 执行insert
insert into t_student (id,name,class,age)values(26,'student-26',26,26) ;
    1. client B执行insert
insert into t_student (id,name,class,age)values(27,'student-27',27,27) ;
    1. client A执行update
update t_student set name='student-26-2' where class=26 and age<>25 ;
    1. 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(写锁)。

如果有多个列违反了唯一性约束,就会按照索引的顺序,修改跟第一个索引冲突的行。