index_merge引发的死锁排查

2018-06-17 23:26:22来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

概述

     前几天排查了一个死锁问题,最开始百思不得其解,因为发生死锁的两个事务是单语句事务,语句类型相同(where属性列相同,仅值不同),而且语句都走了相同的索引,但最终确实发生了死锁。通过定位排查发现,问题的源头就是index_merge,死锁的原因也很普通,两个事务加锁顺序不同,并存在相互等待的情况。因为这个案例比较特殊,所以在此分享给大家。

死锁信息

拿到死锁问题,首先需要查看几个基本信息,包括死锁等待关系,表结构定义等。

1.表结构定义

Create Table: CREATE TABLE `t_xxx_customer` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `partner_id` bigint(20) unsigned DEFAULT NULL,
  `customer_id` bigint(20) unsigned DEFAULT NULL,
  `deleted` tinyint(4) DEFAULT NULL,
  `partner_user_id` bigint(20) unsigned DEFAULT NULL,
  `xxx_id` varchar(128) DEFAULT NULL,
  `xxx_name` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `partner_id` (`partner_id`),
  KEY `customer_id` (`customer_id`),
  KEY `partner_user_id` (`partner_user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=140249 DEFAULT CHARSET=utf8;  

2.死锁信息提取与分析

通过show engine innodb status;命令可以获取innodb引擎中最近一次发生死锁的信息,信息如下

*** (1) TRANSACTION: UPDATE t_xxx_customer SET xxx_id='101', xxx_name='bbb' where customer_id=235646 and partner_id=1688 and deleted=0;

*** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 1640 page no 3947 n bits 432 index partner_id of table xxx.t_xxx_customer trx id 2625291980 lock_mode X locks rec but not gap Record lock, heap no 334 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 8; hex 0000000000000698; asc ;; 1: len 8; hex 0000000000021747; asc G;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1640 page no 3395 n bits 160 index  PRIMARY of table t_xxx_customer trx id 2625291980 lock_mode X locks rec but not gap waiting Record lock, heap no 89 PHYSICAL RECORD: n_fields 25; compact format; info bits 0

*** (2) TRANSACTION: UPDATE t_xxx_customer SET xxx_id='102', xxx_name='aaa' where customer_id=151069 and partner_id=1688 and deleted=0;

*** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 1640 page no 3395 n bits 160 index PRIMARY of table xxx.t_xxx_customer trx id 2625291981 lock_mode X locks rec but not gap

*** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1640 page no 3947 n bits 432 index partner_id of table xxx.t_xxx_customer trx id 2625291981 lock_mode X locks rec but not gap waiting Record lock, heap no 334 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 8; hex 0000000000000698; asc ;; 1: len 8; hex 0000000000021747; asc G;;

*** WE ROLL BACK TRANSACTION (2)

从死锁结果来看,我们很容易看到事务1持有 partner_id二级索引上的锁,等待PK索引上的锁;而事务2持有PK索引锁,等待partner_id二级索引上的锁,两个事务相互持有对方需要的锁资源,而无法往前推进,造成死锁。单从死锁信息来看,我们可能会比较疑惑,每个事务只有一个语句,为什么同样的语句,对二级索引和主键的加锁顺序会不同?

产生死锁的原因

      首先我们来看看语句的执行计划,

语句的type是index_merge,Extra的信息是Using intersect(customerid,partnerid),从而我们得知语句执行计划走了index_merge优化,单个语句通过两个索引(customerid,partnerid)来提取记录集合并取交集获得最终结果集。index_merge具体算法不在此展开,基本使用场景是语句包含多个查询条件,每个条件都单独存在索引,而单个条件的索引过滤度不高,组合起来过滤度比较高,这个时候就可能会走index_merge优化,使得单个SQL语句可以同时利用两个索引过滤。会不会与index_merge有关呢?

     在index_merge的情况下,会导致二级索引与主键索引顺序不一致的情况吗?结合上面的死锁信息,我们得知死锁两个的二级索引key是0x698,而主键索引key是0x21747。我们看看到底是哪条记录的主键和二级索引发生了死锁,

可以看到0x21747对应的customer_id为151069,partner_id为1688,是不是感觉似曾相识,对的,第二个事务的语句查询条件就是这两个条件的组合。这说明,对于这条记录,第一个事务语句只有partnerid索引(1688)满足条件;对于第二个事务,customer_id和partner_id索引都满足条件。由于每个语句执行时都需要利用两个二级索引,假设先使用customer_id索引扫描,然后使用partner_id索引扫描,那么对于id为0x21747的记录,事务1的partner_id=1688满足条件,加partner_id锁,然后对对应的PK索引加锁;对于事务2,对customer_id= 151069加锁,对对应的PK索引加锁,然后对partner_id=1688索引加锁。那么对partner_id二级索引和PK主键索引在两个事务的上锁顺序是相反的,所以导致了死锁。对于id为0x21747记录:

序号 事务1 事务2
1 customer_id 不满足条件不加锁 customer_id= 151069 加锁
2 partner_id=1688加锁 PK=0x21747加锁
3 PK=0x21747加锁 partner_id=1688加锁
4   PK=0x21747加锁

表格第2步和第3步,两个事务的加锁顺序是相反的,导致了死锁发生。

如何避免死锁 

前面啰啰嗦嗦讲了一个死锁案例的来龙去脉,但仅仅是产生死锁的一种情况。生产环境中,我们当然不需要死锁频繁发生,毕竟是需要部分事务回滚才能解锁的,下面介绍几个简单的原则,有助于减少死锁的发生。

1)  尽量按顺序加锁,从源头避免死锁
2)选择合适的隔离级别,隔离级别越高,并发冲突越激烈,实际场景Read-Committed就够用了
3)避免使用大事务,根据二段锁原则,只有事务结束(提交或回滚)才会释放锁,持有的锁越多,可能导致的冲突越大
4)为表添加合适的索引,避免走不到索引导致对每条记录都加锁

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:MySQL数据库基础(四)(子查询与链接)

下一篇:MySql 安装与使用图文教程