Mysql 死锁案例6-并发 insert on duplicate key 导致的死锁

最新推荐文章于 2024-06-26 23:14:09 发布

码上得天下

最新推荐文章于 2024-06-26 23:14:09 发布

阅读量692

点赞数 8

分类专栏： Mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/meser88/article/details/136695377

版权

Mysql 专栏收录该内容

107 篇文章 12 订阅

订阅专栏

本文探讨了在mysql5.7.12中，两个事务并发操作导致的RR隔离级别下死锁现象，涉及事务结构、死锁原因及解决方法。

摘要由CSDN通过智能技术生成

场景复现

mysql 5.7.12 ,事务隔离级别RR

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `t` */

insert  into `t`(`id`,`a`,`b`) values 

(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

	事务1	事务2
T1	BEGIN; INSERT INTO `t`(`id`,`a`,`b`) VALUES (2,2,2) ON DUPLICATE KEY UPDATE b =666
T2		BEGIN INSERT INTO `t`(`id`,`a`,`b`) VALUES (3,2,3) ON DUPLICATE KEY UPDATE b =666(阻塞)
T3	INSERT INTO `t`(`id`,`a`,`b`) VALUES (3,3,3) ON DUPLICATE KEY UPDATE b =666（阻塞）
T4		Deadlock found

SHOW ENGINE INNODB STATUS 死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-03-14 00:56:54 0x1b5c
*** (1) TRANSACTION:
TRANSACTION 488617, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 24, OS thread handle 5388, query id 4322 localhost ::1 root update
INSERT  INTO `t`(`id`,`a`,`b`) VALUES (3,2,3) ON DUPLICATE KEY UPDATE b =666
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1087 page no 4 n bits 80 index a of table `test`.`t` trx id 488617 lock_mode X waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 488616, ACTIVE 11 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 30, OS thread handle 7004, query id 4327 localhost ::1 root update
INSERT  INTO `t`(`id`,`a`,`b`) VALUES (4,1,1)  ON DUPLICATE KEY UPDATE b =666
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1087 page no 4 n bits 80 index a of table `test`.`t` trx id 488616 lock_mode X locks rec but not gap
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1087 page no 4 n bits 80 index a of table `test`.`t` trx id 488616 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS

死锁分析

T1事务1插入成功，加（0，2）和（2,5）间隙锁写锁、a=2的记录锁写锁和id=2的主键记录锁
T2事务2插入数据发现冲突，申请（0,2]临键锁写锁（ insert on duplicate key 语句发现冲突时是加冲突值的临键锁写锁），与事务1的记录锁冲突，阻塞
T1事务1申请（0,2）意向插入锁，与事务2的临键锁冲突得等待，进入死锁