今天有一个朋友问我了一个问题意思是假设我们有一张表需要每天 定时迁移数据 那么我们在进行迁移的时候某些环境下就会触发自增列 并发插入死锁 问题如下。

mysql自增列并发插入死锁问题

mysql 死锁问题测试

　 触发 死锁问题 采用一下 sql 进行 测试 复现

insert into data_cache (customerID,organizationID,createTime)
(
    select   customerID,organizationID,createTime
    from   data
    where   DATE(createTime) <= DATE(?)
    and   autoIndex >= ?
    and   autoIndex <= ?
);

　大致意思是根据 autoIndex 去判定那些数据需要迁移,在程序中已经分好区域了,比如 1~100 , 101~200 , 201~300 这些,两张表的数据表结构均一致如下

CREATE TABLE `data` (

  `customerID` varchar(50) NOT NULL COMMENT '客户编号',

  `organizationID` varchar(50) DEFAULT NULL COMMENT '机构号',

  `createTime` timestamp NULL DEFAULT current_timestamp() COMMENT '创建时间',

  `lastModifiedDatetime` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '最近修改时间',

  `autoIndex` int(11) NOT NULL AUTO_INCREMENT COMMENT '索引',

  `modifyDate` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '修改日期',

  PRIMARY KEY (`customerID`),

  KEY `autoIndex` (`autoIndex`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=468 DEFAULT CHARSET=utf8

mysql 触发死锁异常

　之前测试环境,甚至生产环境都是正常的代码,最近更新了数据库,出现了死锁异常如下

insert into   data_cache ( customerID,organizationID,createTime)
(
select customerID,organizationID,createTime from data where DATE(createTime) <= DATE(?) and autoIndex >= ? and autoIndex <= ? 
)

Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
　org.springframework.dao.DeadlockLoserDataAccessException: PreparedStatementCallback;

　通过以上异常提示信息分析发现 mysql 插入居然报 死锁 了,还是 两条插入并发 在数据源 没有交集 的情况下,并且之前一直是正常,百思不得其解。

解决 mysql 触发死锁异常

　我们只需要 移除掉缓存表 中的 autoIndex 字段,取消自增以及非空重试正常。

触发自增列并发插入死锁问题根源

　其实真正的问题更远涉及到 mysql 对自增的设计。

　大致意思就是数据库模式对这种自增插入有 3种 设置。

　而原有的环境以及生产环境为第二种,更新后改为第一种导致的。