关于mysql:业务系统mysql死锁问题的排查与解决

场景形容：对立登录平台之前的操作步骤（用户利用绑定，用户角色绑定）比拟多，为晋升用户体验，对立批量操作，起初就加了一个用户组的概念（用户绑定用户组和角色，用户组绑定多个角色，角色绑定多个利用，这里波及到一些交并集操作），然而随之而来带来了一个问题，因为该零碎其实次要是面对 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 锁，尽管这样做违反了二阶段加锁协定，但却能够缩小锁抵触，进步事务并发能力，是一种很好的优化行为。