MySQL死锁记录(INSERT INTO ... SELECT.... )

一、前言

在线上发布新功能后，追踪了日志记录，发现有死锁的报错记录。因业务复杂，仅简化问题，先将问题简化复原。特在此记录解决分析过程。

先抛出问题，后附解决方案；

事务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
);
INSERT INTO `</