加锁规则案例分析

# 加锁规则案例分析

# 加锁规则

✨原则1：加锁的基本单位是next-key lock

✨原则2：查询过程中访问到的对象才会加锁，不论是否等值。

🔥优化1：索引上的等值查询，给唯一索引(包括主键）加锁的时候，next-key lock退化为行锁(仅特指右区间满足主键等值情况)。

🔥优化2：索引上的等值查询，若区间右边界不满足等值条件，则next-key lock退化间隙锁，右区间变为开区间。

💡特殊1：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

💡特殊2：所有等值查询或者范围查询，访问到第一个不满足条件的值(所在的区间)才会停止。如果是降序则向左遍历找第一个不满足条件的区间(右边界需要向右边多锁上一个区间，见例八)。

💡特殊3：范围查询第一步按照等值查询定位左边界时(A=10这条记录存在)，分为以下几种情况：

A>10这种情况，则第一个区间从(10,15]开始
A>=10这种情况，需要根据A字段是否是唯一索引进一步区分：
- A为主键索引，则(5,10]退化为行锁A=10
- A为普通索引，则直接添加next-key(5,10]

注意：所有规则都是在索引上加锁，而具体在哪个索引上是根据MySQL优化器选择，并不是根据where字段。

# 建表语句

建表语句与初始化语句如下：

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);

1
2
3
4
5
6
7
8
9
10

# 案例一、等值查询间隙锁

根据id=7落在的区间范围，在(5,10]添加next-key lock
根据优化2，退化为(5,10)间隙锁。
B插入语句，会被间隙锁阻塞；而对于C更新语句，因为id=10没有添加锁，因此执行成功。

# 案例二、非唯一索引等值锁

对于非唯一索引的等值查询，因为可能存在多个相同索引值的行记录，所以每个c=a的查询都要查到c不等于a的行记录才停止。

根据c=5落在的间隙范围，A会在C索引上的(0,5]范围加上next-key lock
c=5满足条件，因此继续向右遍历搜索区间(5,10]，而c=10不满足遍历结束。根据特殊2，next-key lock的范围是(0,5]+(5,10]
根据优化2,区间(0,5]不需要退化；而c=10不满足等值判断，因此退化成间隙锁(5,10)
根据原则2，因为SQL语句只需要查询id，而c字段的索引树可以满足并返回结果集(覆盖索引)。这里采用lock in share mode，故只有普通索引c加了锁，主键上没有加锁；如果采用for update则主键索引也会加上行锁。
由于id主键字段没有上锁，故B操作查询id=3时成功；而对于C时插入语句，因此会被(5,10)间隙锁阻塞。

🌟注意：要想采用lock in share mode锁数据的话，需要向查询字段添加索引中不存在的字段，多进行一次回表。否则很可能被”覆盖索引“拦截住，没有锁到主键上。

# 案例三、主键索引范围锁

定位到id=10这行数据，找到第一个行记录位置需要按照等值查询进行判断，同时id又是主键。因此根据优化1，(5,10]会退化成id=10的行锁。
根据特殊规则，继续向右查询直到id=15不满足范围要求，因此添加next-key lock(10,15]，因为是范围查询，所以这里不需要进行优化。
综上id主键字段上锁的情况：id=10行锁+next-key lock(10,15]。可以发现sessionC也被阻塞了。

# 案例四、非唯一索引范围锁

c是非唯一索引，因此在c字段上next-key lock为(5,10],(10,15]，因为这里是范围查询所以不存在任何优化；同时使用for update故主键也会加锁。
显然查询8和15都被会被阻塞。

# 案例五、唯一索引范围锁特殊

首先next-key lock锁上(10,15]，而这里id=15依旧满足范围条件，因此还需要往后查询id=20，发现不满足条件后next-key lock(15,20]。
综上next-key lock会加两端锁(10,15]和(15,20]。可以发现在会话B中id=20查询被阻塞了。

# 案例六、非唯一索引存在”等值“

根据优化2+特殊2，索引C字段上加锁情况：next-key lock在(5,10]+间隙锁(10,15)

这里如果插入一条记录(30,10,30)，那么在c字段的索引树上，c=10节点对应会存在两个id值的叶子节点，它们之间也是存在间隙的，并不会重合。具体来说(id=5,c=5)——>(id=10,c=10)——>(id=10,c=30)——>(id=15,c=15)

# 案例七、limit语句加锁

第一步delete生成的间隙锁和上面案例六相同、
limit语句会根据数量进一步缩小间隙锁的范围，使整个范围更加精确。具体来说，从左边界开始搜索，遍历到(c=10,id=30)这一行之后，已经满足limit条件只包含锁住了两条数据，循环结束。
因此最终锁的范围就缩小为从(c=5,id=5)到(c=10,id=30)的左开右闭区间。

🌟注意：删除数据时尽量添加limit，这样可以减小锁的范围，提高并发度。

# 案例八、order排序时加锁

由于SQL语句采用了order by c desc，根据特殊2需要向左遍历找到第一个不满足条件的行记录，因此又右向左一次定位的间隙如下：
- 首先找到第一个行记录需要当成等值查询进行，首先找到c=20行记录，因此next-key lock (15,20]，要想确认20是右边界需要向右再找一个位置c=25，因此(20,25]也会被上锁，而此时整个过程都当作等值查询，因此根据优化2后一个区间退化成(20,25)间隙锁。
- 接着开始向左遍历，next-key lock(10,15]。再往左因为c=10这条记录落在(5,10]这个区间上，10不满足条件因此也会锁上，也就是next-key lock(5,10]。
- 整个SQL语句上锁的情况：c索引上(5,25)，主键上c=20，15，10
因此插入c=6的记录会被间隙锁阻塞住。

🌟注意：由于B+树索引默认升序排列，因此如果SQL语句采用desc降序时，需要考虑会不会多锁上了一个间隙。而会出现这种情况的原因，主要是间隙锁是左开右闭区间，每个行记录位于区间的右边界，因此向左找到的第一个不满足条件的行记录所在的区间实际上没有必要锁上。

# 案例九、间隙锁合并

首先按照等值查询找到左边界，同时id为主键，根据特殊3，id=10不满足条件因此不会退化为行锁或者是间隙锁(5,10)，然后往后依次添加(10,15],(15,20]
因此删除id=10这一行并不会被阻塞；然后删除id=10这行记录后，左右两边两个间隙(5,10)和(10,15]进行合并变为(5,15]，因此插入id=10这行记录会被阻塞。

# 案例十、死锁例子

根据优化规则，next-key lock(5,10]和间隙锁(10,15)
session B要更新id=10，因此进入锁等待；同时session B也会针对c=10添加响应的间隙锁(间隙锁之间不会冲突，每个线程单独持有)。
session A最后执行的插入语句，也会被sessionB的更新语句阻塞。循环等待从而导致死锁
由于出现死锁，因此InnoDB会尝试让sessionB进行回滚。

# 如何查看分析死锁

利用上述加锁规则分析以下两条查询语句：

select id from t where c in(5,20,10) lock in share mode;
select id from t where c in(5,20,10) order by c desc for update;

1
2

第一条SQL查询语句，虽然in语句内的字段值是无序的，但因为加锁时是在索引B+树上进行，所以加锁时是有序的，依次会在5，10，20记录上锁。

第二条SQL查询语句涉及到desc，因此加锁顺序是20，10，5。

并发场景下这两条语句也会造成死锁。排查过程如下：

执行show命令查看部分的输出，并查看LATESTDETECTED DEADLOCK记录的最后一次死锁信息的那一节。

show engine innodb status;

可以发现InnoDB在回滚事务时，会选择持有锁资源少的事务(回滚成本更小)进行回滚。因此最终回滚事务一。

编辑

#数据库

上次更新: 2023/12/15, 15:49:57

← 幻读与间隙锁 binlog和redolog如何写入磁盘→