背景
MySQL5.7版本多个线程并发往同一个数据间隙INSERT …… ON DUPLICATE KEY UPDATE,执行的是insert时产生死锁
死锁场景复现
环境: mysql5.7,事务隔离级别REPEATABLE-READ
表结构:
CREATE TABLE `a1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB
数据:
mysql> select * from a1;
+----+-----+
| id | uid |
+----+-----+
| 1 | 1 |
| 10 | 10 |
| 15 | 15 |
+----+-----+
并发事务:
T1
T2
T3
begin;
insert into a1(id, uid) values(11, 11) on duplicate key update id = values(id),uid = values(uid);
Query OK, 1 row affected (0.00 sec)
begin;
insert into a1(id, uid) values(12,