今天有一个朋友问我了一个问题意思是假设我们有一张表需要每天 定时迁移数据 那么我们在进行迁移的时候某些环境下就会触发自增列 并发插入死锁 问题如下。
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种 设置。
而原有的环境以及生产环境为第二种,更新后改为第一种导致的。
评论已关闭