Mysql并发插入引发的死锁

最新推荐文章于 2024-08-19 15:29:52 发布

置顶 Bingo_xu

最新推荐文章于 2024-08-19 15:29:52 发布

阅读量4.4k

点赞数

分类专栏： Mysql 文章标签： Mysql 死锁间隙锁索引

本文链接：https://blog.csdn.net/xubin320121/article/details/95745878

版权

Mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

案发现场

项目中有一张业务关联表t_biz_ref，最近给该表建立了复合索引，测试阶段并发执行插入操作频繁出现死锁情况。

t_biz_ref表结构

CREATE TABLE `t_biz_ref` (
  `id` varchar(32) NOT NULL COMMENT 'id',
  `app_key` varchar(64) NOT NULL COMMENT '应用key',
  `biz_id` varchar(64) NOT NULL COMMENT '业务对象id',
  `device_code` varchar(50) NOT NULL COMMENT '设备code',
  `module_code` varchar(50) NOT NULL COMMENT '业务模块编码',
  `is_delete` char(1) NOT NULL DEFAULT '0' COMMENT '是否删除0未删除1已删除',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `creator` varchar(4) DEFAULT NULL COMMENT '创建人',
  `creator_id` varchar(36) DEFAULT NULL COMMENT '创建人ID',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `modifier` varchar(4) DEFAULT NULL COMMENT '修改人',
  `modifier_id` varchar(36) DEFAULT NULL COMMENT '修改人ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='业务关联';

后对表添加索引:

CREATE INDEX UNION_IDX_1 ON t_biz_ref (app_key, module_code, device_code);

CREATE INDEX UNION_IDX_2 ON t_biz_ref (app_key, module_code, biz_id);

报错日志
在mysql终端执行如下语句，可以看到最近一次死锁详细信息

show engine innodb status \G;

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-07-10 10:12:35 0x7f773116e700
*** (1) TRANSACTION:
TRANSACTION 314618527, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 296544, OS thread handle 140149888374528, query id 950973010 192.168.109.81 root update
INSERT INTO t_biz_ref  ( id,is_delete,app_key,biz_id,device_code,module_code ) VALUES( 'c88995fc93a54356b799866d79bf80b2','0','2d3c7bf923265e059017ae7c7e5bbc31','b3098940-c7fe-42cb-bd1e-f398f5000815','MX2019132130024989','faceset' )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5279 page no 340 n bits 208 index UNION_IDX_1 of table `salmon`.`t_biz_ref` trx id 314618527 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 84 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 30; hex 343137663165633235316163346539303063646661386566616137306237; asc 417f1ec251ac4e900cdfa8efaa70b7; (total 32 bytes);
 1: len 6; hex 726567696f6e; asc region;;
 2: len 13; hex 31353438303538343530343834; asc 1548058450484;;
 3: len 30; hex 343237643863323265373962343466363932303962616262396532633530; asc 427d8c22e79b44f69209babb9e2c50; (total 32 bytes);

*** (2) TRANSACTION:
TRANSACTION 314618531, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 296487, OS thread handle 140149901420288, query id 950973038 192.168.109.81 root update
INSERT INTO t_biz_ref  ( id,is_delete,app_key,biz_id,device_code,module_code ) VALUES( 'abd65256b88643698d7c21cbacb8ecae','0','2d3c7bf923265e059017ae7c7e5bbc31','b3098940-c7fe-42cb-bd1e-f398f5000818','MX2019132130024992','faceset' )
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 5279 page no 340 n bits 208 index UNION_IDX_1 of table `salmon`.`t_biz_ref` trx id 314618531 lock_mode X locks gap before rec
Record lock, heap no 84 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 30; hex 343137663165633235316163346539303063646661386566616137306237; asc 417f1ec251ac4e900cdfa8efaa70b7; (total 32 bytes);
 1: len 6; hex 726567696f6e; asc region;;
 2: len 13; hex 31353438303538343530343834; asc 1548058450484;;
 3: len 30; hex 343237643863323265373962343466363932303962616262396532633530; asc 427d8c22e79b44f69209babb9e2c50; (total 32 bytes);

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5279 page no 340 n bits 208 index UNION_IDX_1 of table `salmon`.`t_biz_ref` trx id 314618531 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 84 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 30; hex 343137663165633235316163346539303063646661386566616137306237; asc 417f1ec251ac4e900cdfa8efaa70b7; (total 32 bytes);
 1: len 6; hex 726567696f6e; asc region;;
 2: len 13; hex 31353438303538343530343834; asc 1548058450484;;
 3: len 30; hex 343237643863323265373962343466363932303962616262396532633530; asc 427d8c22e79b44f69209babb9e2c50; (total 32 bytes);

作案动机

t_biz_ref表使用innodb引擎，在执行插入方法中开启了事务T1。
事务T1包含两个操作：
1.先根据插入对象的app_key+module_ccode+device_code三个字段执行删除；
2.再执行插入对象操作。
添加复合索引UNION_IDX_1是由于项目中app_key+module_ccode+device_code的查询效率有要求，同时事务T1在执行删除时，如果没有UNION_IDX_1索引情况下会进行全表锁，这也会加大死锁概率。但是在加入索引UNION_IDX_1后，相同并发下，死锁现象反而更加频繁了。

破案实录

根据mysql死锁日志，我们将破案点锁定在了此次复合索引UNION_IDX_1上面。
在这里插入图片描述
业务方法分两种情况：
1.在执行删除操作时，如果UNION_IDX_1命中到数据则会执行行锁RK，而后续进行插入操作并不会出现锁冲突。
2.在执行删除操作时，如果UNION_IDX_1没有命中任务索引，那么问题就出现了，这时候UNION_IDX_1会执行一个间隙锁GK，而间隙锁的范围大大加大了出现死锁的概率。
找到“真凶”后如何防范于未然呢？解决办法有两个：
1.降低事务级别，间隙锁只在RR（Repeatable Read）和Serializable中存在，而RU（Read Uncommit）和RC（Read Commit）中不启用。
2.修改业务方法，删除方法前先执行查询操作，存在数据再进行删除，这样索引上加的就是行锁，规避使用到间隙锁。