一、前言
在线上发布新功能后,追踪了日志记录,发现有死锁的报错记录。因业务复杂,仅简化问题,先将问题简化复原。特在此记录解决分析过程。
先抛出问题,后附解决方案;
事务A | 事务B |
---|---|
1、 insert into user_01 (name,age) select name,age user_02 | |
1、update user_02 set name = ‘lisi’ where id = ‘2’ | |
2、update user_02 set name = ‘lisi’ where id = ‘2’ | |
结果:更新成功 | 结果:Deadlock found when trying to get lock; try restarting transaction |
二、问题复原过程
1、建表,向表2插入数据
CREATE TABLE `user_01` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`age` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
);
CREATE TABLE `user_01` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`age` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
)