mysql 死锁优化_MySQL优化--INSERT ON DUPLICATE UPDATE死锁

INSERT ON DUPLICATE UPDATE与死锁

在MySQL中提供两种插入更新的方式：REPLACE INTO和INSERT ON DUPLICATE UPDATE，简化了“存在则更新，不存在则插入”的实现逻辑，但这两种方式在MySQL内部都被拆分为多个操作步骤且引入GAP锁来保证数据完整性，因此在高并发情况下极易产生死锁。

##==================================================##

在MySQL中INSERT ON DUPLICATE UPDATE的加锁流程：

1.Innodb存储引擎尝试INSER INTO操作

2.如果插入成功，则忽略DUPLICATE UPDATE部分并返回

3.如果插入失败，则表明有相同记录存在，对该记录加共享锁(S)

4.执行DUPLICATE UPDATE语句，对记录加X锁，然后更新记录。

##==================================================##

对于INSERT ON DUPLICATE UPDATE操作，当两个会话S1和S2使用INSERT ON DUPLICATE UPDATE语句操作相同数据且表中存在相同键值记录时，触发死锁场景为：

1.由于表中已存在重复键值的记录，导致会话先后尝试INSER失败

2.会话S1进入步骤3尝试获取记录的S锁，该记录未被其他会话加锁，获取S锁成功。

3.会话S2进入步骤3尝试获取记录的S锁，该记录上被加持S锁，但由于S锁与S锁兼容，获取S锁成功

4.会话S1进入步骤4尝试获取记录的X锁，由于会话S2对该记录持有S锁，S锁与X锁不兼容，获取X锁失败，会话S1被阻塞

5.会话S2进入步骤4尝试获取记录的X锁，由于会话S1对该记录持有S锁，S锁与X锁不兼容，获取X锁失败，会话S2被阻塞

6.会话S2被阻塞后进入死锁检查环节，发现阻塞S1->S2和S2->S1形成死锁环路，触发死锁机制强制回滚S1或S2事务。

##==================================================##

在MySQL默认隔离级别REPEATABLE READ下，为避免出现"幻读"发生，防止其他会话插入相同键值的记录。

对于普通INSERT操作加锁如下：

1.对于非唯一索引，需要对新记录加排他锁(X)，另外对新记录和新记录的相邻记录的区间加gap锁。

2.对于唯一索引，仅需要对新记录加排他锁(X)，唯一索引特性保证其他会话无法插入相同键值。

对于INSERT ON DUPLICATE UPDATE操作，当表中未存在重复键值记录时，加锁特点如下：

1.对于唯一索引和非唯一索引，都需要对新记录加排他锁(X)，另外对新记录和新记录的相邻记录的区间加gap锁。

##==================================================##

准备测试数据：

CREATE TABLE `tb2002` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`c1` varchar(20) DEFAULT NULL,

`c2` varchar(20) DEFAULT NULL,

`c3` int(11) DEFAULT '0',

PRIMARY KEY (`id`),

UNIQUE KEY `idx_c1` (`c1`,`c2`)

) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

insert into tb2002(c1,c2) values('a','1');

insert into tb2002(c1,c2) values('f','1');

开始测试：

会话1执行:

insert into tb2002(c1,c2) values('d','2') ON DUPLICATE KEY UPDATE c3=c3+1;

执行成功

会话2执行：

insert into tb2002(c1,c2) values('c','1') ON DUPLICATE KEY UPDATE c3=c3+1;

执行被阻塞，等待X+GAP锁，GAP锁的行记录目标是('f','1')

会话1执行：

insert into tb2002(c1,c2) values('d','1') ON DUPLICATE KEY UPDATE c3=c3+1;

触发死锁，会话2被回滚，会话1执行成功

##==================================================##

通过上面两个死锁案例，我们强烈建议在生产环境中尽量避免使用REPLACE INTO和INSERT INTO ON DUPLICATE UPDATE语句，改用普通INSERT操作，并对INSER操作部分代码加入异常加查，当INSERT失败时改为UPDATE操作。