东莞网站建设 食品厂,空间设计装修公司,杭州推广公司排名,免费建站长平台网站10个行锁、死锁案例⭐️24张加锁分析图#x1f680;彻底搞懂Innodb行锁加锁规则#xff01;
上篇文章 我们描述原子性与隔离性的实现#xff0c;其中描述读操作解决隔离性问题的方案时还遗留了一个问题#xff1a;写操作是如何解决不同的隔离性问题#xff1f;
本篇文章…10个行锁、死锁案例⭐️24张加锁分析图彻底搞懂Innodb行锁加锁规则
上篇文章 我们描述原子性与隔离性的实现其中描述读操作解决隔离性问题的方案时还遗留了一个问题写操作是如何解决不同的隔离性问题
本篇文章将会解决这个问题并描述MySQL中的锁、总结Innodb中行锁加锁规则、列举行锁、死锁案例分析等
再阅读本篇文章前至少要理解查询使用索引的流程、mvcc等知识不理解的同学可以根据专栏顺序进行阅读 MySQL锁的分类 从锁的作用域上划分:全局锁、表锁、页锁、行锁 全局锁锁整个数据库实例常用数据备份禁止全局写只允许读表锁锁表对表进行加锁 元数据锁表结构修改时表X锁表独占锁表S锁表共享锁 页锁位于表锁与行锁中间作用域的锁行锁(innodb特有)锁记录其中包含独占锁写锁X锁和共享锁读锁S锁 从功能上划分意向锁、插入意向锁、自增长锁、悲观锁、乐观锁… 意向锁表锁表示有意向往表中加锁获取行锁前会加意向锁当需要加表锁时要通过意向锁判断表中是否有行锁 独占意向锁 IX意向往表中加X锁兼容IX、IS不兼容X、S表级别共享意向锁 IS意向往表中加S锁兼容IX、IS、S不兼容X表级别 插入意向锁隐式锁意向往表中插入记录自增长锁隐式锁在并发场景下不确定插入数量会使用自增长锁加锁生成自增值如果确定则使用互斥锁连续模式悲观锁悲观锁可以用加行锁实现乐观锁乐观锁可以用版本号判断实现
这些锁有些是MySQL提供的有些是存储引擎提供的比如Innodb支持的行锁粒度小并发性能高不易冲突
但在某些场景下行锁还是会发生冲突阻塞因此我们需要深入掌握行锁的加锁规则才能在遇到这种场景时分析出问题
Innodb的行锁加锁规则
前面说到行锁分为独占锁 X锁和共享锁 S锁行锁除了会使用这种模型外还会使用到一些其他的模型
锁模型
record记录锁用于锁定某条记录 模型使用S/X 也就是独占锁、共享锁
gap间隙锁用于锁定某两条记录之间禁止插入用于防止幻读 模型使用GAP
next key临键锁相当于记录锁 间隙锁 模型使用X/SGAP
在lock mode中常用X或S代表独占/共享的recordGAP则不分X或S
如果是独占的临键锁则是X、GAP共享的临键锁则是S、GAP
这个lock mode后面案例时会用到表示当前SQL被哪种锁模型阻塞
不同隔离级别下的加锁
加锁的目的就是为了能够满足事务隔离性从而达到数据的一致性
在不同隔离级别、使用不同类型SQL增删改查、走不同索引主键和非主键、唯一和非唯一、查询条件等值查询、范围查询、MySQL版本 等诸多因素都会导致加锁的细节发生变化
因此只需要大致掌握加锁规则并结合发生阻塞的记录排查出发生阻塞的问题再进行优化、解决即可本文基于5.7.X
在RURead Uncommitted下读不加锁写使用X record由于写使用X record 则不会产生脏写会产生脏读、不可重复读、幻读
在RCRead Committed下读使用mvcc每次读生成read view写使用X record不会产生脏写、脏读但会有不可重复读和幻读
在RRRepeatable Read下读使用mvcc第一次读生成read view写使用X next key不会产生脏写、脏读、不可重复读和大部分幻读极端场景的幻读会产生
在SSerializable下自动提交情况下读使用mvcc手动提交下读使用S next key写使用X next key不会产生脏写、脏读、不可重复读、幻读
在常用的隔离级别RC、RR中读都是使用mvcc机制不加锁来提高并发性能的
锁定读的加锁
在S串行化下读会加S锁那select如何加锁呢?
S锁select ... lock in share mode
X锁select ... for update
通常把加锁的select称为锁定读而在普通的update和delete时需要先进行读找到记录再操作在这种情况下加锁规则也可以归为锁定读
update与delete是写操作肯定是加X锁的
以下锁定读和新增的加锁规则是总结搭配案例查看一开始看不懂不要紧~ 锁定读加锁规则 在RC及以下隔离级别锁定读使用record锁在RR及以上隔离级别锁定读使用next key锁 间隙锁的范围是前开后闭案例详细描述 具体S、X锁则看SQL如果是 select ... lock in share mode 则是S锁如果是 select ... for update、update ...、delete ... 则是X锁 等值查询如果找不到记录该查询条件所在区间加GAP锁如果找到记录唯一索引临键锁退化为记录锁非唯一索引需要扫描到第一条不满足条件的记录最后临键锁退化为间隙锁不在最后一条不满足条件的记录上加记录锁 范围查询非唯一索引需要扫描到第一条不满足条件的记录5.7中唯一索引也会扫描第一条不满足条件的记录8.0修复后文描述 在查找的过程中使用到什么索引就在那个索引上加锁遍历到哪条记录就给哪条先加锁 查找时走二级索引如果要回表查聚簇索引则还会在聚簇索引上加锁 修改时如果二级索引上也存在要修改的值则还要去二级索引中查找加锁并修改 在RC及以下隔离级别下查找过程中如果记录不满足当前查询条件则会释放锁在RR及以上无论是否满足查询条件只要遍历过记录就会加锁直到事务提交才释放RR及以上获取锁的时间会更长
新增的加锁
前面说到update、delete这种先查再写的操作可以看成加X锁的锁定读而select的锁定读分为S、X还剩insert的规则没有说明 新增加锁规则 新增加锁规则分为三种情况正常情况、遇到重复冲突的情况、外键情况
新增时加的锁叫插入意向锁它是隐式锁
当别的事务想要获取该记录的X/S锁时查看该记录的事务id是不是活跃事务如果活跃事务未提交则会帮新增记录的事务生成锁结构此时插入意向锁变成显示锁可以看成X锁
正常情况下加锁
一般情况下插入使用隐式锁插入意向锁不生成锁结构当插入意向锁隐式锁被其他事务生成锁结构时变为显示锁X record
重复冲突加锁 当insert遇到重复主键冲突时RC及以下加S recordRR及以上加S next key 当insert遇到重复唯一二级索引时加S next key 如果使用 ON DUPLICATE KEY update 那么S锁会换成X锁
外键加锁一般不做物理外键略…
行锁案例分析
搭建环境
先建立一张测试表其中id为主键以s_name建立索引
CREATE TABLE s (id int(11) NOT NULL,s_name varchar(255) DEFAULT NULL,s_age varchar(255) DEFAULT NULL,PRIMARY KEY (id),KEY name_idx (s_name)
) ENGINEInnoDB DEFAULT CHARSETutf8;再插入一些记录
INSERT INTO s (id, s_name, s_age) VALUES (1, juejin, 1);
INSERT INTO s (id, s_name, s_age) VALUES (10, nb, 10);
INSERT INTO s (id, s_name, s_age) VALUES (20, caicai菜菜, 20);
INSERT INTO s (id, s_name, s_age) VALUES (25, ai, 25);聚簇索引和s_name索引的存储图像简化成如下 前面说过GAP需要加在记录之间如果是第一条记录或者最后一条记录要防止插入该如何加GAP锁呢
Infimum和Supremum的出现就能够解决这种问题它们用于标识每页的最小值和最大值
注意由于RC、RR是常用的隔离级别案例也是使用这两种隔离级别进行说明
分析方法
可以通过系统库查看行锁阻塞的相关信息
5.7 阻塞的锁、事务等信息在information_schema库中的innodb_locks、innodb_lock_waits、innodb_trx等表
8.0 的相关信息则是在performance_schema库中 lock记录信息简介 lock_id 锁id 由事务id、存储信息组成 会变动
lock_trx_id 事务ID 42388为先开启的事务 事务ID全局自增
lock_mode 阻塞的锁为X锁还有其他模式S、X、IS、IX、GAP、AUTO_INC等
lock_type 锁类型为行锁record 还有表锁table
lock_table 锁的表 ; lock_index 锁的索引 二级索引
lock_space 、page 、rec 锁的表空间id、页、堆号等存储信息
lock_data 表示锁的数据一般是行记录 ‘caicai菜菜’,20 (s_name,id) 还可以通过联表查询获取行锁阻塞的信息 SELECTr.trx_id waiting_trx_id,r.trx_mysql_thread_id waiting_thread,r.trx_query waiting_query,rl.lock_mode waiting_lock_mode,rl.lock_type waiting_lock_type,b.trx_id blocking_trx_id,b.trx_mysql_thread_id blocking_thread,b.trx_query blocking_query,bl.lock_mode blocking_lock_mode,bl.lock_type blocking_lock_type
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx bON b.trx_id w.blocking_trx_id
INNER JOIN information_schema.innodb_trx rON r.trx_id w.requesting_trx_id
inner join information_schema.innodb_locks rlon r.trx_id rl.lock_trx_id
inner join information_schema.innodb_locks bl on b.trx_id bl.lock_trx_id;又或者通过 innodb的日志 show engine innodb status查看阻塞信息…
后文分析再说
案例RC、RR下的加锁
T1T21begin;select * from s where id10 and id20 for update;2insert into s values (12,‘caicaiJava’,12);(阻塞)3commit;
T1事务在1020之间会加GAP锁因此T2新增时会被阻塞 设置为RC后不再阻塞因为RC下不加GAP锁不防止插入
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT tx_isolation;但如果是要获取记录锁则还是会被阻塞 修改id为10的记录 update s set s_name 666 where id 10 根据该案例可以说明规则一RC及以下使用记录锁、RR及以上使用临键锁
案例等值查询 等值查询匹配不到满足条件的记录 T1T2T31begin;select * from s where id15 for update;2insert into s values (11,‘caicaiJava11’,11);(阻塞)3insert into s values (19,‘caicaiJava11’,19);(阻塞)4commit;
通过阻塞记录可以看到T2,T3事务被主键索引上数据为20的临键锁的GAP阻塞 等值查询如果匹配不到值会在该区间加GAP锁
图中向下黑箭头为GAP锁 例如T1等值查询id15没有id15的记录则会加锁在15这个区间加GAP锁 等值查询匹配到满足条件的记录 T1T2T31begin;select * from s where id20 for update;2insert into s values (15,‘菜菜的后端私房菜’,15);(不阻塞)3update s set s_name ‘菜菜的后端私房菜’ where id 20;(阻塞)4commit;
因为唯一索引上相同的记录只有一条当等值查询匹配时临键锁会退化成记录锁因此T2不被阻塞 T3被阻塞
图中为T3被数据为20上的X锁阻塞 唯一索引等值查询间隙锁退化为记录锁
(图中蓝色为记录锁) 非唯一索引等值查询 T1T2T31begin;select s_name,id from s where s_name‘caicai菜菜’ for update;2insert into s values (15,‘bilibili’,15);(阻塞)3insert into s values (18,‘da’,18);(阻塞)4commit;
为了确保 select s_name,id from s where s_namecaicai菜菜 for update 使用s_name索引我将查询列换成s_name上存在的列避免回表确保使用s_name
先定位到 s_namecaicai菜菜 的记录加锁(ai,caicai菜菜]由于不确定满足 s_namecaicai菜菜 的记录是否有重复于是继续后查询加锁(caicai菜菜juejin]由于juejin不满足查询条件于是退化为间隙锁加锁:(caicai菜菜juejin) 最终加锁范围 (ai,caicai菜菜] (caicai菜菜juejin) (aijuejin)
(注意我这里的加锁范围是简化的没有带上主键信息完整信息如下图lock_data中的juejin,1)
然后再来分析T2,T3的插入语句首先它们需要在聚簇索引和name_idx索引上新增数据由于聚簇索引未加锁因此不影响插入
但是name_idx索引上存在锁T2事务 bilibili 会插入到ai和caicai菜菜记录之间T3事务会插入到caicai菜菜和juejin这两条记录间因此被GAP锁阻塞
通过阻塞记录也可以看出T2,T3均被临键锁阻塞 至此等值查询的案例分析完毕小结如下
等值查询找不到记录该区间加GAP锁等值查询找到记录 唯一索引临键锁会退化为记录锁非唯一索引一直扫描到第一条不满足条件的记录并将临键锁退化为间隙锁
案例范围查询
在上面等值查询 非唯一索引的场景下由于无法判断该值数量因此会一直扫描可以把这种场景理解成范围查询
T1T21begin;select * from s where id10 and id20 for update;2insert into s values (21,‘caicaiJava’,21);(阻塞)3commit;
按照正常思路来说我的查询条件在10-20那么就不能往这个范围外再加锁了
但是新增该范围外的记录是会阻塞的我明明查询条件在10~20结果超过20你也给我加锁是吧 我们来分析下T1加锁过程 id10 and id20
定位第一条记录(id10)按道理加间隙锁(前开后闭)应该是(1,10]但是有等值查询的优化间隙锁退化为记录锁因此只对10加锁 [10]继续向后范围扫描定位到记录id20加锁范围(10,20]按照正常思路主键是唯一的我已经找到一条20了那我应该退出才对呀但是它还是会继续扫描直到第一条不满足查询条件的值(id25)并将临键锁锁退化成间隙锁也就是不在25加记录锁因此加锁范围(20,25) 最终加锁范围 [10] (10,20] (20,25) [10,25)因此插入主键为21时会被阻塞
思考按照正常的思路当在非唯一索引上时这么扫描没问题因为不知道满足结果的20有多少条只能往后扫描找到第一条不满足条件的记录而在唯一索引上找到最后一个满足条件的记录20后还继续往后加锁是不是有点奇怪呢
我在8.0的版本中重现这个操作插入id21不再被阻塞应该是在唯一索引上扫描到最终满足条件的记录id20就结束加锁范围如下图在5.7中这应该算bug 范围查询时无论是否唯一索引都会扫描到第一条不满足条件的记录然后临键锁退化为间隙锁 8.0修复唯一索引范围查询时的bug
案例查找过程中怎么加锁
T1T21begin;update s set s_name ‘caicai菜菜’ where id 20;2select s_name,id from s where s_name like ‘cai%’ for update; (阻塞)3commit;
T1 事务在修改时先使用聚簇索引定位到id20的记录修改后通过主键id20找到二级索引上的记录进行修改因此聚簇索引、二级索引上都会获取锁 T2 事务锁定读二级索引时由于查询条件满足二级索引的值因此不需要回表但由于T1事务锁住二级索引上的记录因此发生阻塞 在该案例中说明加锁时使用什么索引就要在那个索引上加锁遍历到哪些记录就要在哪些记录上加锁
delete与主键相关的二级索引肯定也要删除因此二级索引上对应主键值的记录也会被加锁
update如果在二级索引上修改那么一定回去聚簇索引上修改因此聚簇索引也会被加锁如果在聚簇索引上修改二级索引可能会需要被加锁如上案例如果修改的是s_age那么二级索引就不需要加锁
select使用什么索引就在什么索引上加锁比如使用聚簇索引就要在聚簇索引上加锁使用二级索引就在二级索引上加锁如果要回表也要在聚簇索引上加锁
案例RC、RR什么时候释放锁
RC及以下RR及以上在获取完锁后释放锁的时机也不同 RR下 T1T2T31begin;update s force index (name_idx) set s_age 20 where s_name ‘c’ and s_age 18;2select * from s where id 1 for update;(阻塞)insert into s values (33,‘zz’,33);(阻塞)3commit;
T3插入的记录满足 s_name c and s_age 18 的记录被阻塞情有可原
那为啥T2 id1不满足 s_name c and s_age 18 也被阻塞了呢
T1事务是一条修改语句我使用force index 让它强制使用name_idx索引查询条件为 s_name c and s_age 18
由于name_idx上不存在s_age需要判断s_age就要去聚簇索引因此聚簇索引上也会被加锁
T1在name_idx上根据查询条件s_name c’进行加锁
定位第一条s_name大于c的记录加锁(ai,caicai菜菜]根据主键值id20去聚簇索引中找到该记录加锁[20,20]查看是否满足s_age18的条件如果满足则进行修改不满足不会释放锁继续循环回到name_idx上寻找下一条记录直到不满足查询条件的记录或遍历完记录则退出
根据1-3的步骤会在索引上这样加锁 最终加锁状态name_id中的∞则指的是supremum 其中只有id20的记录满足 s_name c and s_age 18即使这些记录不满足条件也不会释放锁
因此T2要获取聚簇索引id1的记录时被阻塞而T3则是被supremum阻塞 在RR下使用的索引遍历到哪就把锁加到哪即使不满足查询条件也不会释放锁直到事务提交才释放 RC 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT tx_isolation;T1T2T31begin;update s force index (name_idx) set s_age 20 where s_name ‘c’ and s_age 18;2select * from s where id 1 for update;(不阻塞)select * from s where id 20 for update;(阻塞)3commit;
遍历流程与RR情况相似不同的是RC只加记录锁并且不满足条件的记录会立即释放锁因此T2不被阻塞满足条件的T3被阻塞 加锁如下图 遍历到哪条记录就先加锁但是RC对于不满足查询条件的记录会释放锁
死锁案例分析
死锁案例分析的是insert加的锁配合上面新增加锁规则查看
案例新增死锁
先将name_idx改为唯一索引
T1T21begin;insert into s values (5,‘bilibili’,5);2insert into s values (7,‘bilibili’,7); (阻塞)3insert into s values (6,‘balibali’,6);4死锁 回滚
T1插入bilibiliT2也插入bilibili按照道理应该报错唯一键重复呀T2怎么阻塞了呢
T1后续再插入balibali竟然发生死锁了啥情况呀同学们可以先根据前面说到的insert加锁规则大胆猜测喔~ 查看最近的死锁日志 需要注意的是innodb lock表中锁相关信息记录只有正在发生时才存在像这种发生死锁回滚事务后是看不到的因此我们来看看死锁日志
show engine innodb status 查看innodb状态其中有一段最近检测到的死锁 latest detected deadlock
红色框表示事务和持有/等待的锁
绿色框表示锁的信息都是同一把X锁 如果日志还是看不太懂的话来看看下面这段分析吧主要说name_idx索引上的流程哈
1、T1插入bilibili隐式锁
2、T2插入bilibili发生冲突T2帮T1生成锁结构隐式锁转化为显示锁T1获得X recordT2要加S临键锁先获取GAP锁成功再获取S锁被T1的X record阻塞
T1事务id为42861T2事务id为42867根据锁信息可以看到T2想加的S锁被T1的X锁阻塞 3、T1插入balibali插入意向锁被T2的GAP锁阻塞死锁成环T1等待T2的GAPT2等待T1的X
图中蓝色与T1有关黑色与T2有关
T1持有[bilibilibilibili] X锁要插入balabala被T2的间隙锁ai,bilibili阻塞
T2持有间隙锁ai,bilibili要插入[bilibilibilibili]S锁被T1的[bilibilibilibili]X锁阻塞 那么如何解决死锁呢
先来看看死锁产生的四个条件互斥、占有资源不放、占有资源继续申请资源、等待资源成环
MySQL通过回滚事务的方式解决死锁也就是解决占有资源不放
但MySQL死锁检测是非常耗费CPU的为了避免死锁检测我们应该在业务层面防止死锁产生
首先互斥、占有资源不放两个条件是无法破坏的因为加锁由MySQL来实现
而破坏占有资源继续申请资源的代价可能会很大比如让业务层加锁处理
性价比最高的应该是破坏等待资源成环当发生死锁时通过分析日志、加锁规则调整业务代码获取资源的顺序避免发生死锁
案例相同的新增发生死锁
T1T2T31insert into s values (15,‘bili’,15);2insert into s values (15,‘bili’,15);(阻塞)insert into s values (15,‘bili’,15);(阻塞)3rollback4死锁
T1、T2、T3新增相同的记录
T1新增后T2、T3 会帮T1生成锁结构X锁从而被阻塞
当T1回滚时T2T3竟然发生死锁 分析流程 T1 插入 加隐式锁 T2 插入相同唯一记录帮T1生成X锁自己获取S next key先获取gap成功再获取S record此时被T1的X锁阻塞T3 与 T2 相似获取到gap 再获取S record 时被T1的X阻塞 T1 回滚T2、T3获取S record成功此时它们都还要获取X record插入意向锁转化为显示锁X导致死锁成环T2要加X锁被T3的GAP阻塞T3要加X锁被T2的GAP阻塞
图中T2、T3都对bili加S next key锁橙色记录和前面的黑色间隙当它们都想加插入意向X锁蓝色记录同时也被各自的GAP锁阻塞 查看死锁日志 总结
本篇文章通过大量案例、图例分析不同情况下的行锁加锁规则
update、delete 先查再改可以看成锁定读insert则是有单独一套加锁规则 锁定读加锁规则 在RC及以下隔离级别锁定读使用record锁在RR及以上隔离级别锁定读使用next key锁
等值查询如果找不到记录该查询条件所在区间加GAP锁如果找到记录唯一索引临键锁退化为记录锁非唯一索引需要扫描到第一条不满足条件的记录最后临键锁退化为间隙锁不在最后一条不满足条件的记录上加记录锁
范围查询非唯一索引需要扫描到第一条不满足条件的记录5.7中唯一索引也会扫描第一条不满足条件的记录8.0修复后文描述
在查找的过程中使用到什么索引就在那个索引上加锁遍历到哪条记录就给哪条先加锁
在RC及以下隔离级别下查找过程中如果记录不满足当前查询条件则会释放锁在RR及以上无论是否满足查询条件只要遍历过记录就会加锁直到事务提交才释放 insert加锁规则 正常情况下加锁
一般情况下插入使用隐式锁插入意向锁不生成锁结构当插入意向锁隐式锁被其他事务生成锁结构时变为显示锁X record
重复冲突加锁 当insert遇到重复主键冲突时RC及以下加S recordRR及以上加S next key 当insert遇到重复唯一二级索引时加S next key
如果使用ON DUPLICATE KEY update那么S锁会换成X锁
外键加锁一般不做物理外键略…
最后不要白嫖一键三连求求拉~
本篇文章被收入专栏 MySQL进阶之路感兴趣的同学可以持续关注喔
本篇文章笔记以及案例被收入 gitee-StudyJava、 github-StudyJava 感兴趣的同学可以stat下持续关注喔~
有什么问题可以在评论区交流如果觉得菜菜写的不错可以点赞、关注、收藏支持一下~
关注菜菜分享更多干货公众号菜菜的后端私房菜 本文由博客一文多发平台 OpenWrite 发布
