JZTXT

mysql死锁

发布时间 2023-06-19 00:49:36作者: MarkLeeBYR

一、问题背景

8.19号下午对中视频会员体系-免费发放观影卡RPC服务进行压测，监控上出现一些领取失败的请求，如下

登录服务所在机器发现报错日志

由上面的报错日志可以看到，在执行insert语句的时候，系统检测到出现了死锁，并对当前事务进行了回滚操作

那么为什么出现死锁呢？由于服务层面的报错日志较为简单，因此我们需要基于Mysql的死锁日志进一步分析

二、Mysql死锁日志分析

2.1 死锁日志查询方式

  • 方式1：通过SQL查询
show engine innodb status;
  • 方式2：通过KDB平台下载死锁日志，这种方式更为便捷

2.2 死锁日志简要分析

为方便大家阅读，这里简化一下，只贴出部分核心的死锁日志

*** (1) TRANSACTION:
TRANSACTION 956890, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 1046639, OS thread handle 139920757466880, query id 7346955 public-bjdy-kcs-node1039.idczw.hb1.kwaidc.com 10.100.92.146 gifshow_4295_v1_rw update
insert into vip_user_right_time(user_id, latest_order_no, right_expire_time, create_time, update_time, ext_params) values(1598259800, 'OPAYsufOJOwamBjTDwEh', 1661529600000, 1660913922339, 1660913922339, '{}')

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 33 page no 320 n bits 824 index uniq_user_id of table `gifshow`.`vip_user_right_time` trx id 956890 lock_mode X locks gap before rec insert intention waiting

*** (2) TRANSACTION:
TRANSACTION 956891, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 1044689, OS thread handle 139920757200640, query id 7346956 bjrz-rs5763.lf 10.44.114.168 gifshow_4295_v1_rw update
insert into vip_user_right_time(user_id, latest_order_no, right_expire_time, create_time, update_time, ext_params) values(1598259801, 'OPAfNSIEYDyBEPNPSxFU', 1661529600000, 1660913922349, 1660913922349, '{}')

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 33 page no 320 n bits 824 index uniq_user_id of table `gifshow`.`vip_user_right_time` trx id 956891 lock_mode X locks gap before rec

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 33 page no 320 n bits 824 index uniq_user_id of table `gifshow`.`vip_user_right_time` trx id 956891 lock_mode X locks gap before rec insert intention waiting

*** WE ROLL BACK TRANSACTION (2)

从上述日志中可以看出，发生死锁的情形如下：

  • 【事务1】执行的sql语句为
insert into vip_user_right_time(user_id, latest_order_no, right_expire_time, create_time, update_time, ext_params) values(1598259800, 'OPAYsufOJOwamBjTDwEh', 1661529600000, 1660913922339, 1660913922339, '{}')
  • 【事务1】在执行上述insert 语句前需等待获取插入意向锁
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 33 page no 320 n bits 824 index uniq_user_id of table `gifshow`.`vip_user_right_time` trx id 956890 
lock_mode X locks gap before rec insert intention waiting

执行insert操作，为防止其他事务向该索引项上插入数据，会在插入之前先申请插入意向锁，而【事务1】想要获取的插入意向锁和【事务2】持有的间隙锁存在冲突，因此【事务1】被阻塞等待

  • 【事务2】执行的sql语句为
insert into vip_user_right_time(user_id, latest_order_no, right_expire_time, create_time, update_time, ext_params) values(1598259801, 'OPAfNSIEYDyBEPNPSxFU', 1661529600000, 1660913922349, 1660913922349, '{}')
  • 【事务2】持有排他的间隙锁
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 33 page no 320 n bits 824 index uniq_user_id of table `gifshow`.`vip_user_right_time` trx id 956891 lock_mode X locks gap before rec
  • 【事务2】在执行上述insert 语句前需等待获取插入意向锁
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 33 page no 320 n bits 824 index uniq_user_id of table `gifshow`.`vip_user_right_time` trx id 956891 lock_mode X locks gap before rec insert intention waiting

同【事务1】一样，【事务2】执行insert 操作前也需要获取插入意向锁，而【事务2】想要获取的插入意向锁和【事务1】持有的间隙锁存在冲突，因此【事务2】被阻塞等待

  • 数据库检测到出现死锁，回滚【事务2】，让【事务1】成功执行
*** WE ROLL BACK TRANSACTION (2)

【事务1】和【事务2】都处于阻塞等待状态，数据库检测出死锁，最后回滚【事务2】释放间隙锁，【事务1】获得成功执行

三、知识准备-InnoDB不得不说的”锁“事

我们工作中使用的存储引擎基本都是InnoDB，因此以下关于锁的介绍也是基于InnoDB的

3.1 锁的分类

Mysql中往往是按照否含有某一特性或者锁定范围来定义锁，可将锁进行如下归类分组

  • 共享锁：又称为读锁、S锁。共享锁之间兼容，可以同时加锁
  • 排它锁：又称为独占锁、X锁、写锁。排它锁不能和其他锁兼容
  • 全局锁：在DB级别对整个数据库实例加锁，加锁后，数据库处于只读状态，阻塞对数据的增删改以及DDL，一般在做全库逻辑备份场景下才会使用
  • 表级锁：表级别加锁，对操作的整张表加锁，锁定粒度大，不会出现死锁，但是并发性能低
  • 行级锁：InnoDB存储引擎支持行级别锁，锁粒度小，并发度高，但可能造成死锁问题。锁是加在索引上的而非数据行上，当回表时，主键的聚簇索引也会被加锁

因全局锁和表级锁的粒度太粗，并发性能差，一般场景下很少使用，因此下文仅对行级锁进行展开介绍

3.2 行级锁

记录锁（Record Lock）

  • 锁是加在索引上而非数据行上，会阻塞其他事务对该行索引的插入、更新、删除操作
  • 仅仅锁住索引记录的一行，在单条索引记录上加锁
  • 可以加在聚簇索引或者二级索引上

间隙锁（Gap Lock）

  • 间隙锁是对索引记录之间的间隙的锁，或者是对第一个索引记录之前或最后一个索引记录之后的间隙加锁，目标就为了防止其他事务在这个区间插入数据
  • 间隙锁是前开后开区间，如：（-∞, a)，(a, b)，(b, +∞)
  • 间隙锁之间是兼容的，即多个事务可以在同一个间隙上获得间隙锁
  • 间隙锁在RR（Repeatable Read）级别及以上才会使用，在RC（Read Commited）级别不会使用

临键锁（Next-Key Lock）

  • 临键锁是Record 锁 + Gap锁的组合，对索引项以及索引项之间的间隙加锁，前开后闭区间(a,b]
  • 默认情况下，innodb使用Next-key lock来锁定记录

插入意向锁（Insert Intention Lock）

  • 插入意向锁是一种特殊的间隙锁，实际是在Gap锁上加了一个LOCK_INSERT_INTENTION的标记
  • 执行insert语句之前会向插入的间隙加上Insert Intention Lock
  • 插入意向锁之间不冲突，因此允许同时向同一个间隙插入不同主键的数据

记录锁&间隙锁&临健锁之间的范围关系

行锁兼容矩阵

对于某一个索引项，其行级锁兼容性关系如下表

四、回归问题-系统死锁分析

4.1 信息确认

  • 确认事务隔离级别

事务隔离级别为RR，可重复读

 

 

 

 

 

 

 

  • 出现死锁的问题代码

由代码截图可以看出，事务里对同一个表先执行select for update，然后再执行insert into ...。具体sql语句如下

select vip_user_right_time from %s where user_id = :user_id for update

insert into vip_user_right_time(user_id, latest_order_no, right_expire_time, create_time, update_time, 
ext_params) values(:user_id, :latest_order_no, :right_expire_time, :create_time, :update_time, :ext_params)
  • 数据表索引结构

select语句中where user_id = :userId，其中user_id是唯一索引

4.2 select ... for update加锁情况分析

以出现问题的select语句为例

select vip_user_right_time from %s where user_id = :user_id for update

在数据库事务隔离级别为RR的情况下对sql加锁情况进行分析

4.3 事务执行过程分析

在压测前，vip_user_right_time表中只有内测的几十条数据，表中已有的user_id记录为”694750208，1650059897, 2171059320.....“

  •  user_id是唯一索引，1598259800是压测账号，之前在DB没有记录，查询不到数据，因此【事务1】在T2时刻会加上间隙锁（694750208，1650059897）
  • 【事务2】在T3时刻执行select for update语句也会获取到间隙锁（694750208，1650059897），因为间隙锁之间是兼容的，不会导致阻塞
  • 【事务1】在T4时刻执行insert语句时会申请插入意向锁，但【事务2】已经持有了（694750208，1650059897）范围的间隙锁，由上文可知间隙锁和插入意向锁是冲突的，【事务1】需要等待【事务2】释放间隙锁，所以【事务1】被阻塞了
  • 【事务2】在T5时刻执行insert语句时会申请插入意向锁，因为【事务1】已经持有了（694750208，1650059897）范围的间隙锁，所以【事务2】也被阻塞了
  • 此时【事务1】和【事务2】都持有间隙锁，并且都在等待对方释放间隙锁，因此发生死锁

4.4 压测号段导致死锁概率增加

  • 压测账号在DB中查询不到数据，因此会加间隙锁
  • 压测账号是挖的一段连续userId，由上面的分析可知，连读userId持有间隙锁范围是一样的，因此产生冲突的概率会增加

五、 解决方案

  • 【方案1】（不建议）：使用表锁，并发性能严重下降
  • 【方案2】（不建议）：调整事务隔离级别为Read Commited，select ... for update不会加间隙锁，但可能会造成数据不一致问题
  • 【方案3】去掉select for update加锁方式，使用乐观锁+唯一索引组合方式，这样在并发插入时，保证只有一个事务能够插入成功（DB唯一键约束）
  • 优点：能避免每次加锁开销，改造成本较低
  • 缺点：并不能彻底避免死锁出现，insert语句如果出现唯一键冲突，会在冲突的唯一值上加上Next-key锁，还是可能出现死锁
  • 【方案4】去掉select for update加锁方式，改用redis分布式锁来保证同一个用户的操作有序性
  • 优点：redis分布式锁的方案比较成熟，性能比较好，抗Qps能力更强
  • 缺点：在Mysql事务的最外层套一层Redis分布式锁，锁粒度比较粗
  • 【方案5】：将insert语句剥离出事务，在事务之前先插入数据，这样可确保事务在执行select ... for update时一定能查询到数据，因此加上的是记录锁而不是间隙锁。但事务原子性被破坏，可能造成数据不一致

因为想彻底避免死锁，经过综合考虑，我们最终选择了【方案4】，重新压测后，结果符合预期。附使用redis分布式锁代码改造CR