Description:
The bug happened on RC mode and version before 5.7.2
It caused a deadlock when i executed Update and INSERT...ON DUPLICATE KEY UPDATE in transaction 1, and executed INSERT...ON DUPLICATE KEY UPDATE in transaction 2.
LATEST DETECTED DEADLOCK
------------------------
2024-07-03 16:03:43 8ac
*** (1) TRANSACTION:
TRANSACTION 1821, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 0x41cc, query id 296 localhost ::1 root update
INSERT INTO `test1` (`a_id`,`b_id`,`c`) VALUES ('a1','b1',3) ON DUPLICATE KEY UPDATE `c`=VALUES(`c`)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10 page no 4 n bits 72 index `record_id` of table `test`.`test1` trx id 1821 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 2; hex 6131; asc a1;;
1: len 2; hex 6231; asc b1;;
2: len 4; hex 00000001; asc ;;
*** (2) TRANSACTION:
TRANSACTION 1820, ACTIVE 11 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2
MySQL thread id 4, OS thread handle 0x8ac, query id 300 localhost ::1 root update
INSERT INTO `test1` (`a_id`,`b_id`,`c`) VALUES ('a1','b1',0) ON DUPLICATE KEY UPDATE `c`=VALUES(`c`)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10 page no 4 n bits 72 index `record_id` of table `test`.`test1` trx id 1820 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 2; hex 6131; asc a1;;
1: len 2; hex 6231; asc b1;;
2: len 4; hex 00000001; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10 page no 4 n bits 72 index `record_id` of table `test`.`test1` trx id 1820 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 2; hex 6131; asc a1;;
1: len 2; hex 6231; asc b1;;
2: len 4; hex 00000001; asc ;;
*** WE ROLL BACK TRANSACTION (1)
How to repeat:
READ COMMITTED
CREATE TABLE `test1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`a_id` varchar(100) NOT NULL ,
`b_id` varchar(100) NOT NULL,
`c` int(11) NOT NULL ,
PRIMARY KEY (`id`),
UNIQUE KEY `record_id` (`a_id`,`b_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `test1` (`a_id`,`b_id`,`c`) VALUES ('a1','b1',0) ON DUPLICATE KEY UPDATE `c`=VALUES(`c`);
Transaction 1:
UPDATE `test1` SET `c`=0 WHERE a_id in ('a1');
(execute Transation 2)
INSERT INTO `test1` (`a_id`,`b_id`,`c`) VALUES ('a1','b1',0) ON DUPLICATE KEY UPDATE `c`=VALUES(`c`);
Transaction 2:
INSERT INTO `test1` (`a_id`,`b_id`,`c`) VALUES ('a1','b1',3) ON DUPLICATE KEY UPDATE `c`=VALUES(`c`);
Description: The bug happened on RC mode and version before 5.7.2 It caused a deadlock when i executed Update and INSERT...ON DUPLICATE KEY UPDATE in transaction 1, and executed INSERT...ON DUPLICATE KEY UPDATE in transaction 2. LATEST DETECTED DEADLOCK ------------------------ 2024-07-03 16:03:43 8ac *** (1) TRANSACTION: TRANSACTION 1821, ACTIVE 4 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1 MySQL thread id 5, OS thread handle 0x41cc, query id 296 localhost ::1 root update INSERT INTO `test1` (`a_id`,`b_id`,`c`) VALUES ('a1','b1',3) ON DUPLICATE KEY UPDATE `c`=VALUES(`c`) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 10 page no 4 n bits 72 index `record_id` of table `test`.`test1` trx id 1821 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 2; hex 6131; asc a1;; 1: len 2; hex 6231; asc b1;; 2: len 4; hex 00000001; asc ;; *** (2) TRANSACTION: TRANSACTION 1820, ACTIVE 11 sec inserting mysql tables in use 1, locked 1 4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2 MySQL thread id 4, OS thread handle 0x8ac, query id 300 localhost ::1 root update INSERT INTO `test1` (`a_id`,`b_id`,`c`) VALUES ('a1','b1',0) ON DUPLICATE KEY UPDATE `c`=VALUES(`c`) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 10 page no 4 n bits 72 index `record_id` of table `test`.`test1` trx id 1820 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 2; hex 6131; asc a1;; 1: len 2; hex 6231; asc b1;; 2: len 4; hex 00000001; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 10 page no 4 n bits 72 index `record_id` of table `test`.`test1` trx id 1820 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 2; hex 6131; asc a1;; 1: len 2; hex 6231; asc b1;; 2: len 4; hex 00000001; asc ;; *** WE ROLL BACK TRANSACTION (1) How to repeat: READ COMMITTED CREATE TABLE `test1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `a_id` varchar(100) NOT NULL , `b_id` varchar(100) NOT NULL, `c` int(11) NOT NULL , PRIMARY KEY (`id`), UNIQUE KEY `record_id` (`a_id`,`b_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `test1` (`a_id`,`b_id`,`c`) VALUES ('a1','b1',0) ON DUPLICATE KEY UPDATE `c`=VALUES(`c`); Transaction 1: UPDATE `test1` SET `c`=0 WHERE a_id in ('a1'); (execute Transation 2) INSERT INTO `test1` (`a_id`,`b_id`,`c`) VALUES ('a1','b1',0) ON DUPLICATE KEY UPDATE `c`=VALUES(`c`); Transaction 2: INSERT INTO `test1` (`a_id`,`b_id`,`c`) VALUES ('a1','b1',3) ON DUPLICATE KEY UPDATE `c`=VALUES(`c`);