记一次mysql的index_merge引起的死锁问题的分析

最新推荐文章于 2023-03-28 11:54:11 发布

Relian哈哈

最新推荐文章于 2023-03-28 11:54:11 发布

阅读量987

点赞数

分类专栏： MySQL

本文链接：https://blog.youkuaiyun.com/lzxlfly/article/details/104872176

版权

MySQL 专栏收录该内容

11 篇文章

订阅专栏

mysql多个普通索引下，在upate操作时候index_merge引起的死锁，导致问题直接原因，不知道day_time被加了索引，导致SQL语句运用不当，间接原因，mysql的索引优化index_merge，根本原因对mysql锁机制理解不深入。

表结构如下：

CREATE TABLE `mc_k12_meal_msg_day` (
  `msg_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `merchant_id` int(11) NOT NULL DEFAULT '0' COMMENT '商户ID',
  `merchant_name` varchar(50) NOT NULL DEFAULT '' COMMENT '商户名称',
  `day_trade_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '日总交易笔数',
  `day_trade_meet_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '日符合条件交易笔数',
  `day_trade_member_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '日交易会员数量',
  `day_push_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '日推送数量',
  `day_click_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '日点击数量',
  `meal_type` int(2) NOT NULL DEFAULT '0' COMMENT '餐别  1早餐 2午餐 3晚餐',
  `platform` int(2) DEFAULT '0' COMMENT '平台 1支付宝、2微信、3iOS、4Android',
  `day_time` date NOT NULL DEFAULT '1000-01-01' COMMENT '每天日期',
  `active` int(2) DEFAULT '0' COMMENT '1有效 0无效',
  `created_time` datetime NOT NULL DEFAULT '1000-01-01 00:00:00' COMMENT '创建时间',
  `updated_time` datetime NOT NULL DEFAULT '1000-01-01 00:00:00' COMMENT '更新时间',
  `msg_type` tinyint(2) unsigned NOT NULL DEFAULT '1' COMMENT '1模板消息 2小宝消息 3订单中心',
  PRIMARY KEY (`msg_id`),
  KEY `idx_merchant_id` (`merchant_id`),
  KEY `idx_day_time` (`day_time`)
) ENGINE=InnoDB AUTO_INCREMENT=675 DEFAULT CHARSET=utf8 COMMENT='商户下用餐消息统计日表'

问题现象及背景

现象1：今天14:23接到DBA投诉：X locks rec but not gap watting，index inx_day_time of table “bizcenter.mc_k12_msg_day” trx 1921494612 lock_mode X locks rec but not gap waitting
现象2：查看日志，发下如下：content: ### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
背景：袋鼠校园小宝消息需求上线后，会给家长给支付宝发送孩子消费的服务提醒和push消息，当家长打开支付宝点击消息查看详情时候要记录点击量，对点击数量进行数据库更新操作，某个时间段可能可能存在点击高峰

影响范围

袋鼠校园的小宝消息的点击量统计不准确，会少统计

问题原因

MySQL innoDB下锁作用在索引上，条件更新操作有多个普通索引时会索引合并（index_merge）
条件更新列存在多个单一普通索引，且更新的列有索引列
存在多个事务的并发更新同一资源操作且加锁顺序不一致

最终导致不同事务并发更新时互相持有对方锁，导致死锁。

问题分析及解决办法

问题排查

问题发生后，DBA给出了死锁发生时的日志，通过DBA给出的死锁日志，看到“X lock rec but not gap waiting“，结合SQL可能是记录小宝消息点击量的表在更新的时候发生了死锁。
在项目日志中，通过搜索deadlock关键词，发现了更新MYSQL死锁导致的事务回滚日志，确定是小宝消息记录点击量更新数据库发生了锁记录

问题分析

“X lock rec but not gap waiting“意为只对record本身加锁，并不间隙等待，属于记录锁，记录锁作用于索引列，更新时会占有索引列行锁
根据上图日志发现事务1占有primary锁，在等待获取idx_day_time锁，与此同时事务2占有idx_day_time锁，等待获取primary锁。满足死锁的四个必要条件（互斥、请求与保持、不可剥夺，循环等待）

袋鼠校园上线的小宝消息，在记录点击量的时候，采用了以下更新语句：

update mc_k12_meal_msg_day SET day_click_count = day_click_count + ?, meal_type = ?, msg_type = ?, day_time = ?, active = ?, updated_time = ? WHERE merchant_id = ? AND meal_type = ? AND msg_type = ? AND day_time = ? AND active = ?

（1）其中更新条件列day_time字段是索引列，而day_time既是条件列也是更新列，对于非主键索引MySQL更新数据时会首先给普通索引加锁，再给主键索引加锁，若更新列有索引字段，需要再给更新列加锁。该语句中merchant_id和day_time都是普通索引列

（2）从MySQL5.1开始，引入了 index merge 优化技术：对多个索引分别进行条件扫描，然后将它们各自的结果进行取交集或合并(intersect/union)。 index merger优化可能会导致索引加锁顺序不一致等情况出现，优化导致的死锁，也是官方确认的一个bug。EXPLAIN查看以上SQL执行计划where后条件命中时会出现以下两种索引组合情况

type=index_merge，Extra=Using intersect(idx_merchant_id,idx_day_time);

type=index_merge，Extra=Using intersect(idx_day_time,idx_merchant_id);

可以看出MySQL进行了索引优化，对两个普通索引分别取交集

（3）同一条SQL执行update操作发生死锁一定是索引加锁顺序不一致，结合执行计划，推断出的并发时有以下两种情况可能发生的死锁

case1

序号	事务1	事务2
1	idx_merchant_id加锁	idx_day_time加锁
2	primary加锁	primary等待加锁
3	idx_day_time等待加锁	idx_merchant_id加锁未执行到
4	primary加锁未执行到	primary加锁未执行到
5	idx_day_time加锁未执行到	idx_day_time加锁未执行到

事务1到来，索引组合的顺序为intersect(idx_merchant_id,idx_day_time)，事务1首先对idx_merchant_id加锁，之后开始对primary加锁，准备对idx_day_time加锁。与此同时，事务2到来，索引组合顺序为intersect(idx_day_time,idx_merchant_id)，事务2首先占有idx_day_time锁，准备对primary主键加锁，发现primary锁被事务1占有，进入等待。而事务1等待的idx_day_time锁被事务2占有，互相等待，造成死锁。

case2

序号	事务1	事务2
1	idx_merchant_id加锁	idx_merchant_id加锁
2	primary加锁	primary加锁
3	idx_day_time等待加锁	idx_day_time加锁
4	primary加锁未执行到	primary等待加锁
5	idx_day_time加锁未执行到	idx_day_time加锁未执行到

由于采用了index_merge优化，会对两个索引分别读数据，然后将数据进行intersect(取交集)。事务1到来，索引组合的顺序为intersect(idx_merchant_id,idx_day_time)，事务1首先对idx_merchant_id加锁，之后开始对primary加锁，准备对idx_day_time加锁。与此同时，事务2到来，索引组合顺序为intersect(idx_merchant_id,idx_day_time)，事务2首先占有idx_merchant_id锁，之后对primary主键加锁，接着对idx_day_time加锁，等待对primary加锁。发现primary锁被事务1占有，进入等待。而事务1等待的idx_day_time锁被事务2占有，互相等待，造成死锁。

解决办法

改变更新方式，首先根据条件查询，得到主键，用主键做为条件更新，保证更新条件唯一及加锁顺序一致。

select msg_id, merchant_id,merchant_name,day_trade_count,day_trade_meet_count,day_trade_member_count, day_push_count, day_click_count, meal_type, platform, day_time, active, created_time, updated_time,msg_type from mc_k12_meal_msg_day where merchant_id = ? AND meal_type = ? AND msg_type = ? AND day_time = ? AND active = ?
update mc_k12_meal_msg_day set day_click_count = day_click_count+1,updated_time = ? where msg_id = ?