如何排查MySQL死锁警告

本篇内容介绍了“如何排查MySQL死锁警告”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!

清河门网站制作公司哪家好,找创新互联建站!从网页设计、网站建设、微信开发、APP开发、自适应网站建设等网站项目制作,到程序开发,运营维护。创新互联建站从2013年成立到现在10年的时间,我们拥有了丰富的建站经验和运维经验,来保证我们的工作的顺利进行。专注于网站建设就选创新互联建站

 故障背景

国庆期间,收到一条从未见过的报警,后面间歇性地又报出类似的偶现报警,便忽然来了兴致,摘了其中一条,探究一下其中的故事。

*** (1) TRANSACTION:  TRANSACTION 6286508066, ACTIVE 0 sec updating or deleting  mysql tables in use 1, locked 1  LOCK WAIT 9 lock struct(s), heap size 1136, 14 row lock(s), undo log entries 1  MySQL thread id 189619143, OS thread handle 140619931252480, query id 1148803196 10.200.18.103 ke_information updating  update `user_feed_26` set `notification` = 1, `mtime` = '2020-10-03 09:11:11' where `user_id` = 2000000126212250 and `action` in ('resblock_weekly', 'bizcircle_weekly', 'district_weekly') and `notification` = 0 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:  RECORD LOCKS space id 2229 page no 263938 n bits 264 index idx_user_id of table `lianjia_user_feed`.`user_feed_26` trx id 6286508066 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 93 PHYSICAL RECORD: n_fields 5; compact format; info bits 0   0: len 8; hex 80071afd5112d89a; asc     Q   ;;   1: len 14; hex 6f6e5f7368656c665f616761696e; asc on_shelf_again;;   2: len 1; hex 81; asc  ;;   3: len 12; hex 313034313033373433363737; asc 104103743677;;   4: len 4; hex 95f12ab5; asc   * ;;

从日志的字面意思来看,显然,是MySQL数据库在执行事务时,发现了死锁的情况,那么这种死锁是如何产生的,背后又潜藏着怎样的隐患,又该如何去解决呢,我们一起来排查一下~

排查过程

雾里看花

刚开始收到这个报警,第一反应,是有不同事务互相锁,结果产生了死锁。那么坏了,十有八九是某个代码片段里写的逻辑出了问题。但是排查了一整圈,涉及到这个sql的代码,既没有开启事务,更没有多个事务,那么代码的bug基本上就可以排除了。

那么这些个事务是怎么来的呢?众所周知,MySQL的事务支持与存储引擎有关,MyISAM不支持事务,INNODB支持事务,更新时采用的是行级锁。由于我们的数据库采用的是INNODB引擎,意味着,会将update语句当做一个事务来处理。那难道是更新同一条数据,出现的冲突吗?于是找DBA同学要来了死锁日志(数据库版本:5.7.24 事务隔离级别为RR)。

事务一日志:

*** (1) TRANSACTION:  TRANSACTION 6286508066, ACTIVE 0 sec updating or deleting  mysql tables in use 1, locked 1  LOCK WAIT 9 lock struct(s), heap size 1136, 14 row lock(s), undo log entries 1  MySQL thread id 189619143, OS thread handle 140619931252480, query id 1148803196 10.200.18.103 ke_information updating  update `user_feed_26` set `notification` = 1, `mtime` = '2020-10-03 09:11:11' where `user_id` = 2000000126212250 and `action` in ('resblock_weekly', 'bizcircle_weekly', 'district_weekly') and `notification` = 0 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:  RECORD LOCKS space id 2229 page no 263938 n bits 264 index idx_user_id of table `lianjia_user_feed`.`user_feed_26` trx id 6286508066 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 93 PHYSICAL RECORD: n_fields 5; compact format; info bits 0   0: len 8; hex 80071afd5112d89a; asc     Q   ;;  1: len 14; hex 6f6e5f7368656c665f616761696e; asc on_shelf_again;;  2: len 1; hex 81; asc  ;;   3: len 12; hex 313034313033373433363737; asc 104103743677;;   4: len 4; hex 95f12ab5; asc   * ;;

由日志可以看出,事务一执行的sql语句是:

update `user_feed_26` set `notification` = 1, `mtime` = '2020-10-03 09:11:11' where `user_id` = 2000000126212250 and `action` in ('resblock_weekly', 'bizcircle_weekly', 'district_weekly') and `notification` = 0

在等待的锁是:

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:  ECORD LOCKS space id 2229 page no 263938 n bits 264 index idx_user_id of table `lianjia_user_feed`.`user_feed_26` trx id 6286508066 lock_mode X locks gap before rec insert intention waiting

这里显示的是事务在等待什么锁。RECORD LOCKS 表示记录锁,并且可以看出要加锁的索引为idx_user_id,space id为2229,page no为263938,lock_mode X 标识该记录锁为排它锁,insert intention waiting 表示要加的锁为插入意向锁,并处于锁等待状态。

Record lock, heap no 93 PHYSICAL RECORD: n_fields 5; compact format; info bits 0  0: len 8; hex 80071afd5112d89a; asc     Q   ;;  1: len 14; hex 6f6e5f7368656c665f616761696e; asc on_shelf_again;;  2: len 1; hex 81; asc  ;;  3: len 12; hex 313034313033373433363737; asc 104103743677;;  4: len 4; hex 95f12ab5; asc   * ;;

结合索引信息第二行 on_shelf_again 可以知道,这行锁的 action 字段是 on_shelf_again ;

事务二日志:

 *** (2) TRANSACTION:  TRANSACTION 6286508067, ACTIVE 0 sec updating or deleting, thread declared inside InnoDB 4980  mysql tables in use 1, locked 1  12 lock struct(s), heap size 1136, 22 row lock(s), undo log entries 3  MySQL thread id 189619144, OS thread handle 140620050204416, query id 1148803197 10.200.17.37 pt_user updating  UPDATE `user_feed_26` SET  `notification` = '1' , `mtime` = '2020-10-03 09:11:11'  WHERE `user_id` = '2000000126212250'  AND `action` in ( 'deal','price_changed','ting_shou','house_new_picture','house_new_vr','price_changed_rise','on_shelf_again')  AND `notification` = '0' *** (2) HOLDS THE LOCK(S):  RECORD LOCKS space id 2229 page no 263938 n bits 264 index idx_user_id of table `lianjia_user_feed`.`user_feed_26` trx id 6286508067 lock_mode X locks gap before rec Record lock, heap no 83 PHYSICAL RECORD: n_fields 5; compact format; info bits 0   0: len 8; hex 80071afd5112d89a; asc     Q   ;;   1: len 4; hex 6465616c; asc deal;;  2: len 1; hex 81; asc  ;;   3: len 12; hex 313034313032363731333238; asc 104102671328;;   4: len 4; hex 95e14632; asc   F2;;  Record lock, heap no 93 PHYSICAL RECORD: n_fields 5; compact format; info bits 0   0: len 8; hex 80071afd5112d89a; asc     Q   ;;   1: len 14; hex 6f6e5f7368656c665f616761696e; asc on_shelf_again;;   2: len 1; hex 81; asc  ;;   3: len 12; hex 313034313033373433363737; asc 104103743677;;   4: len 4; hex 95f12ab5; asc   * ;;  *** 省略……  *** (2) WAITING FOR THIS LOCK TO BE GRANTED:  RECORD LOCKS space id 2229 page no 263938 n bits 264 index idx_user_id of table `lianjia_user_feed`.`user_feed_26` trx id 6286508067 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 87 PHYSICAL RECORD: n_fields 5; compact format; info bits 32   0: len 8; hex 80071afd5112d89a; asc     Q   ;;   1: len 15; hex 64697374726963745f7765656b6c79; asc district_weekly;;   2: len 1; hex 80; asc  ;;   3: len 8; hex 3233303038373831; asc 23008781;;   4: len 4; hex 95f63035; asc   05;;

事务二的日志,相比于事务一多了持有锁的信息:

 *** (2) HOLDS THE LOCK(S):  RECORD LOCKS space id 2229 page no 263938 n bits 264 index idx_user_id of table `lianjia_user_feed`.`user_feed_26` trx id 6286508067 lock_mode X locks gap before rec Record lock, heap no 83 PHYSICAL RECORD: n_fields 5; compact format; info bits 0  0: len 8; hex 80071afd5112d89a; asc     Q   ;;   1: len 4; hex 6465616c; asc deal;;   2: len 1; hex 81; asc  ;;   3: len 12; hex 313034313032363731333238; asc 104102671328;;   4: len 4; hex 95e14632; asc   F2;;  Record lock, heap no 93 PHYSICAL RECORD: n_fields 5; compact format; info bits 0   0: len 8; hex 80071afd5112d89a; asc     Q   ;;   1: len 14; hex 6f6e5f7368656c665f616761696e; asc on_shelf_again;;   2: len 1; hex 81; asc  ;;   3: len 12; hex 313034313033373433363737; asc 104103743677;;   4: len 4; hex 95f12ab5; asc   * ;;  *** 省略……

从日志看,事务二持有一个记录锁,RECORD LOCKS这是个记录锁,space id为2229,page no为263938 并且通过索引信息可以看出,事务二恰好持有事务一需要的那行记录锁,即:

Record lock, heap no 93 PHYSICAL RECORD: n_fields 5; compact format; info bits 0   0: len 8; hex 80071afd5112d89a; asc     Q   ;;   1: len 14; hex 6f6e5f7368656c665f616761696e; asc on_shelf_again;;   2: len 1; hex 81; asc  ;;   3: len 12; hex 313034313033373433363737; asc 104103743677;;   4: len 4; hex 95f12ab5; asc   * ;;  lock_mode X locks gap before rec 表示这是一个排他锁,并且是一个间隙锁  *** (2) WAITING FOR THIS LOCK TO BE GRANTED:  RECORD LOCKS space id 2229 page no 263938 n bits 264 index idx_user_id of table `lianjia_user_feed`.`user_feed_26` trx id 6286508067 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 87 PHYSICAL RECORD: n_fields 5; compact format; info bits 32   0: len 8; hex 80071afd5112d89a; asc     Q   ;;   1: len 15; hex 64697374726963745f7765656b6c79; asc district_weekly;;   2: len 1; hex 80; asc  ;;   3: len 8; hex 3233303038373831; asc 23008781;;   4: len 4; hex 95f63035; asc   05;;

同样,这里显示的是事务二在等待什么锁。RECORD LOCKS 表示记录锁,并且可以看出要加锁的索引为idx_user_id,space id为2229,page no为263938 lock_mode X 标识该记录锁为排它锁,insert intention waiting 表示要加的锁为插入意向锁,并处于锁等待状态。虽然,事务一的日志中没有标明它持有了哪些锁,但是结合事务二等待的锁结构中 district_weekly 字段来看,事务一是持有该锁的,因此,两个事务形成了互相等待锁释放的场景,从而形成了死锁。

那么疑问来了,两个sql:

# sql1:  update `user_feed_26` set `notification` = 1, `mtime` = '2020-10-03 09:11:11' where `user_id` = 2000000126212250 and `action` in ('resblock_weekly', 'bizcircle_weekly', 'district_weekly') and `notification` = 0 # sql2:  UPDATE `user_feed_26` SET  `notification` = '1' , `mtime` = '2020-10-03 09:11:11'  WHERE `user_id` = '2000000126212250'  AND `action` in ( 'deal','price_changed','ting_shou','house_new_picture','house_new_vr','price_changed_rise','on_shelf_again')  AND `notification` = '0'

明明两个语句的where条件不一样,也不交叉,为什么会占用彼此的锁呢?

山穷水复

为了验证这种case,我们在线下尝试进行复现。表结构如下:

#CREATE TABLE `user_feed_26` (    `feed_id` int(10) NOT NULL AUTO_INCREMENT,    `user_id` bigint(20) NOT NULL,  ……    PRIMARY KEY (`feed_id`),    KEY `idx_user_id` (`user_id`,`action`,`notification`,`feed_target`),  …… ) ENGINE=InnoDB AUTO_INCREMENT=371826027 DEFAULT CHARSET=utf8 COMMENT='用户推送表';

但是无论如何,都是锁等待,而不会形成死锁。这是怎么回事呢?

如何排查MySQL死锁警告

带着怀疑的态度,我们查看了一下语句的执行计划:

如何排查MySQL死锁警告

通过执行计划我们发现,这里并没有走死锁日志里出现的那个idx_user_id索引,而是走的主键索引,因此并没有产生死锁。

大胆猜测:是因为模拟的数据量太小,导致并没有走复合索引。

于是,我们往线下模拟库里灌入了大概100w左右的随机数据,再次查看执行计划:

如何排查MySQL死锁警告

果然,当数据量变大之后,就会走对应的复合索引了。再经过一次尝试,果然复现出了线上那种死锁场景,但是问题来了,为什么会出现这种情况呢?

柳暗花明

为了了解背后真实的原理,我们再次研读了MySQL锁相关的资料,也得知了事情的真相。

首先,简单说一下MySQL加锁的基本原则:

  •  原则 1:加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间;

  •  原则 2:查找过程中访问到的对象才会加锁。

优化 1:唯一索引上的等值查询加锁时,next-key lock 退化为行锁。

优化 2:非唯一索引上的等值查询加锁时,对where条件中的值所在区间向右(后)遍历时,该区间的右边界不满足等值条件的时候,next-key lock 退化为间隙锁。这个比较难理解,举个例子:

若在表ta的列a上有非唯一索引:index_a,该索引中存在的值为:1,1,3,3,7,9:当你执行select a from ta where ta.a=5时,就会从3开始往右(后)遍历,此时对应的 是(3,7]但是由于该区间的最后一个值7不满足=5的条件,因此该next-key lock就退化为gap lock (3,7)。

由此可知,当我们执行的update语句,在查询的时候,给对应的索引idx_user_id加上了间隙锁,从而互相之间产生了死锁。举个简单的例子说明一下:

如何排查MySQL死锁警告

  •  事务2执行了一个update, where 条件为3,因此获得了(1,3)的Gap锁;

  •  事务1也执行了一个update,where条件为5,因此获得了一个(5,+∞),同时等待(1,7)插入意向锁;

  •  事务2又执行了一个update,where条件为8,那么他将等待(5,+∞)。

于是乎,死锁就产生了。

那么,如何避免这种死锁再次发生呢?

通过唯一索引(一般主键都是)来更新,先通过select语句查出符合条件的记录的唯一索引,再通过唯一索引来更新。

select id from table where a=? and b=?;  update table set column=xxx where idid= id;

避免在同一时间点运行多个对同一表进行读写的脚本,特别注意加锁且操作数据量比较大的语句;我们经常会有一些定时脚本,避免它们在同一时间点运行;如本次事件所示,Gap 锁往往是程序中导致死锁的真凶,由于默认情况下 MySQL 的隔离级别是 RR,所以如果能确定幻读和不可重复读对应用的影响不大,可以考虑将隔离级别改成 RC,可以避免 Gap 锁导致的死锁。

“如何排查MySQL死锁警告”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联网站,小编将为大家输出更多高质量的实用文章!


文章标题:如何排查MySQL死锁警告
分享链接:http://myzitong.com/article/gcjdcc.html