mysql间隙锁引发的死锁 mysql间歇锁

转载

数据大侠客 2023-08-21 14:09:50

文章标签 mysql间隙锁引发的死锁 mysql 加锁主键 MySQL 文章分类 MySQL 数据库

首先MySQL引入间隙锁是为了解决幻读的问题。间隙锁之间并不产生冲突，但间隙锁与插入数据之间会产生冲突。间隙锁的引入，可能会导致同样的语句锁住更大的范围，影响并发度。MySQL加锁的规则十分复杂并且随着版本更新进行变动，整体的加锁规则如下，包含了两个“原则”、两个“优化”和一个“bug”（所有都是在可重复读的隔离级别）
原则1：加锁的基本单位是next-key lock，next-key lock是前开后闭区间。
原则2：查找过程中访问到的对象才会加锁。
优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
下面以这个表为例：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

mysql间隙锁引发的死锁 mysql间歇锁_mysql间隙锁引发的死锁

案例一：

事务A	事务B	事务C
`begin;update t set d = d+1 where id = 7`
	`insert into t values(8,8,8);`（blocked）
		`update t set d = d+1 where id = 10;`

分析：

首先事务A,进行update，where使用的是主键索引，加锁应该加(5,10],然后发现是唯一索引，且是等值查询，所以根据优化2向右遍历，到第一个不满足的10，所以加锁为(5,10);
事务B插入数据在间隙锁之间，所以blocked;
事务C成功执行；

案例二：

事务A	事务B	事务C
`begin;select id from t where c=5 lock in share mode;`
	`update t set d = d+1 where id = 5;`（blocked）
		`insert into t values(7,7,7);`（blocked)

分析：

首先事务A使用普通索引c，且为覆盖索引，应该锁(0,5],(5,10],根据优化2，变成(0,5],(5,10)，但是由于是lock in share mode只锁覆盖索引，所以主键索引并没有用到，所以也没有加锁。但是如果是执行 for update时系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。
事务B使用主键索引，所以正常更新
事务C插入数据时收到间隙锁的限制，被锁。

案例三

事务A	事务B	事务C
`begin;select * from t where id>=10 and id<11 for update;`
	`insert into t values(8,8,8);`（ok）`insert into t values(13,13,13);`（blocked）
		`update t set d = d+1 where id = 15;`（blocked)

分析：

事务A采用主键索引，应该加上间隙锁(5,10],(10,15],由于存在10这一行，根据优化1，等值查询，所以变为10这一行行锁+(10,15]。
事务B第一条语句成功，第二条语句被间隙锁锁住
事务C被间隙锁锁住。

案例四

事务A	事务B	事务C
`begin;select * from t where c>=10 and c<11 for update;`
	`insert into t values(8,8,8);`（blocked）
		`update t set d = d+1 where c=15;`（blocked)