业务系统大事务导致mysql死锁问题的排查与解决

场景描述：用户平台有一各操作步骤（用户应用绑定，用户角色绑定）比较多，为提升用户体验，统一批量操作，后来就加了一个用户组的概念（用户绑定用户组和角色，用户组绑定多个角色，角色绑定多个应用，这里涉及到一些交并集操作以及笛卡尔积，比如用户数据有5000，这时候给每个用户加两个角色，就需要插入5000*2=1w的数据量），但是随之而来带来了一个问题，因为该系统其实主要是面对B端用户，并发其实不高，很难遇到一些并发导致的问题，但是由于这个批量操作导致的大事务，事务时间过长导致锁冲突的概率极大增加，所以就出现了死锁问题。

然后我就查mysql死锁日志，show engine innodb status，该命令显示的内容长度有限制，可以设置SET GLOBAL innodb_status_output=ON;SET GLOBAL innodb_status_output_locks=ON;，默认输出到log-err，可以通过show variables like 'log_err%';查看设置的输出路径，如果是stderr，则表示标准错误输出

然后我就看日志，发现了两个冲突的事务产生了死锁，死锁产生的四个条件之一就是循环等待，主要原因是加锁顺序不一致，在这个场景中是并发修改数据导致的，主要就是两张表，一张用户表open_app_user，主键id，执行的sql是update open_app_user set xx=xx where id = 14310 and version=5，另一张表是open_role_user，是用户和角色的关联表，就两个字段同时也是联合主键，role_id和user_id,执行的sql是delete from open_role_user where user_id=654321;

这里的delete from open_role_user where user_id = xx的业务逻辑是用户角色关系更新（可能是绑定或解绑），所以每个地方都需要根据user_id删除原来的记录再去新增，而这条sql是有问题的，这里需要说明一下mysql的删除以及删除的加锁机制：

InnoDB上删除一条记录，并不是真正意义上的物理删除，而是将记录标识为删除状态（删除状态的记录会在索引中存放一段时间）。所以在RR隔离级别下，
1、在非唯一索引的情况下，删除一条存在的记录是有gap锁，锁住记录本身和记录之前的gap，InnoDB会在记录上加next key锁(对记录本身加X锁，同时锁住记录前的GAP，防止新的满足条件的记录插入。
2、在唯一索引和主键的情况下删除一条存在的记录，因为都是唯一值，进行删除的时候，是不会有gap存在

以下是一段演示代码

//先执行删除的操作
begin;
delete from `open_role_user` where `user_id`=654321;

//再执行以下语句查看mysql的加锁情况
select * from performance_schema.data_locks;

我们看到删除使用非主键或唯一索引，mysql会加supremum pseudo-record一个gap锁和目前表里所有的记录都加一个record行锁

然后我换一条使用主键删除的sql：delete from open_role_user where role_id = 654321 and user_id=654321;

这时候就只加了一个record行锁了

对于使用非唯一索引或主键删除会相当于给整个表上锁，这时候冲突的概率是非常大的，而换成使用主键删除的话就只会锁住一行记录，这样是能有效减少冲突情况的。

另外还有一种办法，就是把mysql默认的隔离级别RR可重复读改为RC读已提交，这次我执行

//先执行删除的操作
begin;
set session transaction isolation level read committed;
delete from `open_role_user` where `user_id`=654321;

然后这边加锁就只会加上符号where条件的记录的锁了，实际上许多大公司也都是用的RC级别，包括oracle默认的就是RC隔离级别，相比于RR，对于UPDATE or DELETE语句，RC只为它更新或删除的行（符合where条件的记录）持有锁，大大降低了死锁的可能性，对高并发场景比较友好。对于UPDATE，如果行已被锁定，InnoDB 还会执行一个“半一致读”，会多进行一次判断，当 where 条件匹配到的记录与当前持有锁的事务中的记录不冲突时，就会提前释放 InnoDB 锁，虽然这样做违背了二阶段加锁协议，但却可以减少锁冲突，提高事务并发能力，是一种很好的优化行为。