【MySQL（锁篇）】深入MySQL锁机制：从全局到行级，解锁数据库性能瓶颈（下：行锁分析实战、死锁原理）

文章目录

MySQL（锁篇）- 全局锁、表锁、行锁（记录锁、间隙锁、临键锁、插入意向锁）、意向锁、SQL加锁分析、死锁产生原因与排查

MySQL（锁篇）- 全局锁、表锁、行锁（记录锁、间隙锁、临键锁、插入意向锁）、意向锁、SQL加锁分析、死锁产生原因与排查

行锁分析实战

# 分析下下面两条简单的SQL，判断他们加的什么锁？
# SQL1
select * from t1 where id = 10;
# SQL2
delete from t1 where id = 10;

针对这个问题，我们通常能想到的答案是：

SQL1: 不加锁，因为Mysql是多版本并发处理的，读不加锁。
SQL2: 对ID=10的记录加写锁（走主键索引）

这个答案对吗？不一定，因为已知条件不足，这个问题没有答案。
判断这个问题，需要一些前提，前提不同，答案也不相同。

前提一：id列是不是主键？
前提二：当前系统的隔离级别是什么？
前提三：ID列如果不是主键，那么ID列上有索引吗？
前提四：ID列上如果有索引，那么这个索引是唯一索引吗？
前提五：两个SQL的执行计划是什么？索引扫描？全表扫描？
读已提交【RC】隔离级别
- 组合一：id列是主键
- 组合二：id列是二级唯一索引
- 组合三：id列是二级非唯一索引
- 组合四：id列上没有索引
可重复读【RR】隔离级别
- 组合五：id列是主键
- 组合六：id列是二级唯一索引
- 组合七：id列是二级非唯一索引
- 组合八：id列上没有索引
组合九：Serializable隔离级别

1 读已提交RC

前面8种组合下，也就是RC、RR的隔离级别下：SQL1 select操作都是不加锁的，采用的是快照读。
因此下面讨论的主要是SQL2 delete操作的加锁

1.1 组合一：ID是主键

这个组合最简单：ID是主键，RC隔离级别；给定SQL：delete from t1 where id = 10; 只需要将主键上ID = 10 的数据加上写锁就行了。
在这里插入图片描述

结论：RC隔离级别，ID是主键，此SQL只需要在id=10的记录上加写锁即可；

1.2 组合二：ID唯一索引

这个组合，ID不是主键，而是一个Unique的二级索键值。那么在RC的隔离级别下，delete from t1 where id = 10;需要加什么锁呢？
在这里插入图片描述
此组合中，id是unique索引，而主键是name列。此时，加锁的情况由于组合一有所不同。由于id是unique索引，因此delete语句会选择走id列的索引进行where条件的过滤，在找到id=10的记录后，首先会将unique索引上的id=10索引记录加上写锁，同时，会根据读取到的name列，回主键索引(聚簇索引)，然后将聚簇索引上的name = ‘d’ 对应的主键索引项加写锁。

为什么聚簇索引上的记录也要加锁？
试想一下，如果并发的一个SQL，是通过主键索引来更新：update t1 set id = 100 where name = ‘a’; 此时，如果delete语句没有将主键索引上的记录加锁，那么并发的update就会感知不到delete语句的存在，违背了同一记录上的更新/删除需要串行执行的约束。

结论：若id列是unique列，其上有unique索引。那么SQL需要加两个写锁，一个对应于id unique索引上的id = 10的记录，另一把锁对应于聚簇索引上的【name=’d’,id=10】的记录。

1.3 组合三：ID非唯一索引

相对于组合一、二，组合三又发生了变化，隔离级别仍旧是RC不变，但是id列上的约束又降低了，id列不再唯一，只有一个普通的索引。假设delete from t1 where id = 10; 语句，仍旧选择id列上的索引进行过滤where条件，那么此时会持有哪些锁？
在这里插入图片描述
根据此图，可以看到，首先，id列索引上，满足id = 10查询条件的记录，均已加锁。同时，这些记录对应的主键索引上的记录也都加上了锁。与组合二唯一的区别在于，组合二最多只有一个满足等值查询的记录，而组合三会将所有满足查询条件的记录都加锁。

结论：若id列上有非唯一索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。

1.4 组合四：ID无索引

相对于前面三个组合，这是一个比较特殊的情况。id列上没有索引，where id = 10;这个过滤条件，没法通过索引进行过滤，那么只能走全表扫描做过滤

对应于这个组合，SQL会加什么锁？或者是换句话说，全表扫描时，会加什么锁？
在这里插入图片描述
由于id列上没有索引，因此只能走聚簇索引，进行全部扫描。从图中可以看到，满足删除条件的记录有两条，但是，聚簇索引上所有的记录，都被加上了写锁。无论记录是否满足条件，全部被加上写锁。既不是加表锁，也不是在满足条件的记录上加行锁。

有人可能会问？为什么不是只在满足条件的记录上加锁呢？
这是由于MySQL的实现决定的。如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回，然后由MySQL Server层进行过滤。 因此也就把所有的记录，都锁上了。

注：在实际的实现中，MySQL有一些改进，在MySQL Server过滤条件，发现不满足后，会调用
unlock_row方法，把不满足条件的记录放锁。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。

结论：若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上写锁。 但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。

2 可重复读RR

2.1 组合五：ID主键

结论：与组合一是一致的。ID=10的数据加写锁

2.2 组合六：ID唯一索引

结论：与组合二是一致的。ID=10的数据加写锁，聚簇索引上ID=10的数据也加写锁，两把锁。

2.3 组合七：ID非唯一索引

RC隔离级别允许幻读，而RR隔离级别，不允许存在幻读
那么RR隔离级别下，如何防止幻读呢？
- 间隙锁

在这里插入图片描述
相对于组合三最大的区别在于，组合七中多了一个间隙锁。其实这个多出来的间隙锁，就是RR隔离级别，相对于RC隔离级别，不会出现幻读的关键。

所谓幻读，就是同一个事务，连续做两次当前读 (例如：select * from t1 where id = 10 for update;)，那么这两次当前读返回的是完全相同的记录 (记录数量一致，记录本身也一致)，第二次的当前读，不会比第一次返回更多的记录 (幻象)。记录本身的一致性是可重复性，使用MVCC来解决。

如何保证两次当前读返回一致的记录？ 那就需要在第一次当前读与第二次当前读之间，其他的事务不会插入新的满足条件的记录并提交。为了实现这个功能，间隙锁应运而生。

结论：RR隔离级别下，ID列上有一个非唯一索引，对应SQL：delete from t1 where id = 10;

首先：通过ID索引定位到第一条满足查询条件的记录，对该记录上写锁，加GAP的间隙锁，然后加主键索引上记录的写锁，然后返回；然后读取吓一跳，重复进行。直到进行到第一条不满足条件的记录[11,f]，此时，不需要加记录写锁，但是仍旧需要加间隙锁，最后返回结束。

2.4 组合八：ID无索引

RR情况下，ID无索引：delete from t1 where id = 10; 只能走全表扫描
在这里插入图片描述

如图，聚簇索引上的所有记录都被加上了写锁，其次，聚簇索引每条记录的间隙，也同时被加上了间隙锁。 这个实例表，总共6条数据，一共需要6个记录锁，7个间隙锁。那么如果是1000万条记录呢？

在这种情况下，这个表上，除了不加锁的快照读，其他任何加锁的并发SQL都不能被执行，不能更新，不能删除，不能插入，全表被锁死。

当前，跟组合四类似，这种情况下，Mysql也做了优化，就是所谓的semi-consistent read。semi-consistent read开启的情况下，对于不满足查询条件的记录，Mysql会提前放锁。针对上面的这个用例，就是除了记录[d,10],[g,10]之外，其他的记录锁都会被释放，同时不加间隙锁。

semi-consistent read如何触发？ 要么是RC隔离级别；要么是RR隔离级别，同时设置了**innodb_locks_unsafe_for_binlog 参数**。

结论：在RR隔离级别下，如果进行全表扫描的当前读，会锁上表中所有的记录，同时锁上聚簇索引内的所有间隙，杜绝所有的并发更新、删除、插入操作。当前，也可以通过触发semi-consistent read，来缓解加锁开销与并发影响。但是semi-consistent read本身也会带来其他问题，不建议使用。（可能会造成主从数据库数据的不一致）

2.3 组合九：串形化Serializable

对于SQL2来说，Serializable隔离级别与RR隔离级别组合八情况完全一致，因此不做介绍。
delete from t1 where id = 10;

Serializable隔离级别，影响的是SQL1这条SQL：
select * from t1 where id = 10;

在RC，RR隔离级别下，都是快照读，不加锁。但是在Serializable隔离级别，SQL1会加读锁，也就是说快照读不复存在，MVCC并发控制降级为LBCC。

结论： 在MySQL/InnoDB中，所谓的读不加锁，并不适用于所有的情况，而是隔离级别相关的。Serializable隔离级别，读不加锁就不再成立，所有的读操作，都是当前读。

4 复杂SQL加锁分析

delete from t1 where pubtime > 1 and pubtime < 20 and userid='hero' and commit is not null;

索引如图所示：
在这里插入图片描述
假设在RR的隔离级别下，同时假设SQL走的idx_t1_pu索引

在详细分析这条SQL的加锁情况前，还需要一个知识储备，那就是一个SQL中的where条件如何拆分？

index key: pubtime > 1 and pubtime < 20。此条件，用于确定SQL在idx_t1_pu索引上的查询范围。
index Filter: userid = 'hero'. 此条件，可以在idx_t1_pu索引上进行过滤，但不属于index key。
Table Filter：comment is not null。此条件，在idx_t1_pu索引上无法过滤，只能在SQL- Layer上过滤。

在这里插入图片描述
从图中可以看出，在RR隔离级别下，有index key所确定的范围被加上了间隙锁；Index Filter锁给定的条件视Mysql版本而定【图中红色箭头标出的写锁是否要加，跟ICP有关】

不支持ICP：Index Filter在Mysql Server层过滤，不满足Index Filter的记录，也要加上记录写锁
支持ICP：则在Index上过滤，则不满足Index Filter的记录，无需加记录写锁。

而Table Filter对应的过滤条件，则在聚簇索引中读取后，在Mysql Server层过滤，因此聚簇索引上也需要写锁。最后，选取出了一条满足条件的记录[8, hero, d, 5, handsome]，但是加锁的数量，要远远大于满足条件的记录数量。

结论：
在RR隔离级别下，针对一个复杂SQL，首先需要提取其where条件

index key确定的范围，需要加上间隙锁
index Filter过滤条件，视是否支持ICP，若支持ICP，则不满足Index Filter的记录，不加写锁，否则需要写锁
Table Filter过滤条件：无论是否满足，都需要加写锁。

死锁原理

深入理解Mysql如何加锁，有几个比较重要的作用：

可以根据Mysql的加锁规则，写出不会发生死锁的SQL
可以根据Mysql的加锁规则，定位出线上产生死锁的原因
可以根据Mysql的加锁规则，透过现象看本质，理解数据库层面阻塞执行的根本原因

1 什么是死锁？

情况1

CREATE TABLE `t1_deadlock` (
`id` int(11) NOT NULL,
`name` varchar(100) DEFAULT NULL,
`age` int(11) NOT NULL,
`address` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_age` (`age`) USING BTREE,
KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Insert into t1_deadlock(id,name,age,address) values (1,'刘备',18,'蜀国');
Insert into t1_deadlock(id,name,age,address) values (2,'关羽',17,'蜀国');
Insert into t1_deadlock(id,name,age,address) values (3,'张飞',16,'蜀国');
Insert into t1_deadlock(id,name,age,address) values (4,'关羽',16,'蜀国');
Insert into t1_deadlock(id,name,age,address) values (5,'诸葛亮',35,'蜀国');
Insert into t1_deadlock(id,name,age,address) values (6,'曹孟德',32,'魏国');

产生死锁的两个例子：

一个是两个session的两条SQL产生死锁
一个是两个session的一条SQL产生死锁

时间	session01	session02
T1	begin;	begin;
T2	select * from t1_deadlock where id=1 for update;
T3		delete from t1_deadlock where id = 5;
T4	update t1_deadlock set name=‘qqq’ where id =5
T5	死锁	delete from t1_deadlock where id = 1;
T6	commit;	commit;

在这里插入图片描述

-- Session01
begin;
select * from t1_deadlock where id=1 for update;	
update t1_deadlock set name='qqq' where id=5;
commit;

-- Session02
begin;
delete from t1_deadlock where id=5;
delete from t1_deadlock where id=1;
-- 死锁
commit;

在这里插入图片描述

情况02

在这里插入图片描述

CREATE TABLE `t1_deadlock03` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`cnt` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE index `idx_cnt` (`cnt`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into t1_deadlock03(id,cnt) values (1,'abc-130-sz');

-- Session01
begin;
delete from t1_deadlock03 where cnt='abc-130-sz';
insert into t1_deadlock03(cnt) values ('abc-130-sz'); -- 在加写锁之前会先加读锁
commit;

-- Session01
begin;
delete from t1_deadlock03 where cnt='abc-130-sz';
commit;

在这里插入图片描述

结论

死锁的发生与否，并不在于事务中有多少条SQL，【死锁的关键在于】 ：两个（或以上）的session【加锁的顺序不一致】

查询最近一次死锁日志：show engine innodb status

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-05-14 10:49:51 0x7f8c2c06f700
*** (1) TRANSACTION:
TRANSACTION 2428, ACTIVE 4 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 207, OS thread handle 140239876904704, query id 721 localhost root updating
delete from t1_deadlock03 where cnt='abc-130-sz'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 44 page no 4 n bits 72 index idx_cnt of table `hello`.`t1_deadlock03` trx id 2428 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 10; hex 6162632d3133302d737a; asc abc-130-sz;;
 1: len 4; hex 80000004; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 2427, ACTIVE 8 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 208, OS thread handle 140240010802944, query id 722 localhost root update
insert into t1_deadlock03(cnt) values ('abc-130-sz')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 44 page no 4 n bits 72 index idx_cnt of table `hello`.`t1_deadlock03` trx id 2427 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 10; hex 6162632d3133302d737a; asc abc-130-sz;;
 1: len 4; hex 80000004; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 44 page no 4 n bits 72 index idx_cnt of table `hello`.`t1_deadlock03` trx id 2427 lock mode S waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 10; hex 6162632d3133302d737a; asc abc-130-sz;;
 1: len 4; hex 80000004; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

2 如何避免死锁？

Mysql默认会主动探知死锁，并回滚某一个影响最小的事务，等另一个事务执行完成之后，再重新执行该事务。

注意程序的逻辑：根本的原因是程序逻辑的顺序交叠，最常见的是交叉更新
保持事务的轻量：越是轻量的事务，占有越少的资源，这样发生死锁的几率越小
提高运行的速度：避免使用子查询，尽量使用主键等
尽量快提交事务，减少持有锁的时间：越早提交事务，锁就越早释放

标签：行级,加锁,记录,t1,索引,死锁,MySQL,ID,id
From： https://blog.csdn.net/weixin_68020300/article/details/140781379

【MySQL（锁篇）】深入MySQL锁机制：从全局到行级，解锁数据库性能瓶颈（下：行锁分析实战、死锁原理）

文章目录

MySQL（锁篇）- 全局锁、表锁、行锁（记录锁、间隙锁、临键锁、插入意向锁）、意向锁、SQL加锁分析、死锁产生原因与排查

行锁分析实战

1 读已提交RC

1.1 组合一：ID是主键

1.2 组合二：ID唯一索引

1.3 组合三：ID非唯一索引

1.4 组合四：ID无索引

2 可重复读RR

2.1 组合五：ID主键

2.2 组合六：ID唯一索引

2.3 组合七：ID非唯一索引

2.4 组合八：ID无索引

2.3 组合九：串形化Serializable

4 复杂SQL加锁分析

死锁原理

1 什么是死锁？

情况1

情况02

结论

2 如何避免死锁？

相关文章

赞助商

阅读排行