1200字范文 > Mysql并发插入引发的死锁

Mysql并发插入引发的死锁

时间：2021-03-17 23:58:16

相关推荐

Mysql并发插入引发的死锁

案发现场

项目中有一张业务关联表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-------------------------07-10 10:12:35 0x7f773116e700*** (1) TRANSACTION:TRANSACTION 314618527, ACTIVE 0 sec insertingmysql tables in use 1, locked 1LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1MySQL thread id 296544, OS thread handle 140149888374528, query id 950973010 192.168.109.81 root updateINSERT INTO t_biz_ref ( id,is_delete,app_key,biz_id,device_code,module_code ) VALUES( 'c88995fc93a54356b799866d79bf80b2','0','2d3c7bf923265e059017ae7c7e5bbc31','b3098940-c7fe-42cb-bd1e-f398f5000815','MX132130024989','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 waitingRecord lock, heap no 84 PHYSICAL RECORD: n_fields 4; compact format; info bits 00: 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 insertingmysql tables in use 1, locked 13 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 296487, OS thread handle 140149901420288, query id 950973038 192.168.109.81 root updateINSERT INTO t_biz_ref ( id,is_delete,app_key,biz_id,device_code,module_code ) VALUES( 'abd65256b88643698d7c21cbacb8ecae','0','2d3c7bf923265e059017ae7c7e5bbc31','b3098940-c7fe-42cb-bd1e-f398f5000818','MX132130024992','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 recRecord lock, heap no 84 PHYSICAL RECORD: n_fields 4; compact format; info bits 00: 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 waitingRecord lock, heap no 84 PHYSICAL RECORD: n_fields 4; compact format; info bits 00: 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后，相同并发下，死锁现象反而更加频繁了。