【mysql】一次奇怪的replace into死锁

时间:2015-12-27 02:12:42      作者:beebol      标签: gap锁 next key replace into 死锁      分类: mysql

最近碰到一次死锁，开始看比较奇怪，两个replace into不同记录怎么造成了死锁，其实没有想的这么简单。具体看如下图：

单纯的看show engine innodb status的死锁信息，还不是很明确，因为只能看到目前事务1正在等待uk_a_b_c索引上的lock_mode X.而事务2已经持有lock_mode X,正在等等IX锁（lock_mode X locks gap before rec insert intention waiting）。两个都是在不同的记录上等待，

事务1的语句是：replace into tt (a,b,c,d)values(89,123,1,'xx-343s3');

事务2的语句是：replace into tt (a,b,c,d)values(86,123,1,'xx-3s3');

看到这里时，感觉很奇怪，为什么这两个sql会造成死锁，后面分析了全日志才知道，还存在一条sql。它是：replace into tt (a,b,c,d)values(89,123,1,'xx-3s3')，看到这条sql后，感觉发现了问题。

首先确认两个tips:

1、replace into时，如果记录存在会加Next Key锁（x+gap）2、对记录操作时，必须持有页锁（比如说修改记录得先申请页锁，然后再申请X，再修改数据页，如果需要等待锁，会立即释放掉页锁；获得X锁后，得重新申请页面锁）

2、对记录操作时，必须持有页锁（比如说修改记录得先申请页锁，然后再申请X，再修改数据页，如果需要等待锁，会立即释放掉页锁；获得X锁后，得重新申请页面锁）

实验环境：mysql 5.1.63  + redhat6u5

表结构：

CREATE TABLE `tt` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `a` int(8) NOT NULL,
  `b` int(8) NOT NULL,
  `c` int(8) NOT NULL,
  `d` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_a_b_c` (`a`,`b`,`c`)) ENGINE=InnoDB AUTO_INCREMENT=81 DEFAULT CHARSET=utf8
) ENGINE=InnoDB AUTO_INCREMENT=81 DEFAULT CHARSET=utf8

插入的数据：

mysql> select * from tt;          
+----+----+-----+---+----------+
| id | a  | b   | c | d        |
+----+----+-----+---+----------+
|  1 | 85 | 234 | 2 | zz       |
| 71 | 86 | 123 | 1 | yy-2xssd |
| 73 | 89 | 123 | 1 | yy-2sss  |+----+----+-----+---+----------+
+----+----+-----+---+----------+

如下是具体的执行步骤：

session1	session2	session3
start transaction; replace into tt (a,b,c,d)values(89,123,1,'xx-3s3'); 记录存在，标记删除后，再insert新记录,持Next key锁
		start transaction; replace into tt (a,b,c,d)values(86,123,1,'xx-3s3'); 请求Next key锁，被session1中的Next key锁住，等待(lock_mode X waiting)
	start transaction; replace into tt (a,b,c,d)values(89,123,1,'xx-343s3'); 获得页面锁，被session1锁定，释放页面锁，等待Next key锁；(lock_mode X waiting)
commit;
		session1提交后，成功获取Next key锁
	session1提交后，发现记录已经被修改，restart,重新判断记录状态，成功获得页面锁，发现数据记录存在，准备获取Next key锁，由于session3已经有next key锁，等待（lock_mode X waiting）
		标记数据为删除状态，准备插入记录，请求IX锁，但被与session2请求的Next Key锁冲突（ lock_mode X locks gap before rec insert intention waiting）
	ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction 这里防止session2饿死，根据权重将session2回滚