乐趣区

关于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 锁,尽管这样做违反了二阶段加锁协定,但却能够缩小锁抵触,进步事务并发能力,是一种很好的优化行为。

退出移动版