Description:
parallel transactions inserting disjointed sets of rows into a table with auto-increment PK will deadlock on supremum lock in repeatable-read isolation
this cannot be reproduced on 5.6
How to repeat:
schema:
CREATE TABLE `tt` (
`a` bigint NOT NULL AUTO_INCREMENT,
`b` bigint DEFAULT NULL,
`c` bigint DEFAULT NULL,
`d` bigint DEFAULT NULL,
PRIMARY KEY (`a`),
UNIQUE KEY `blah` (`b`,`c`)
)
T1: INSERT ... (b,c,d) VALUES (1, rand, rand),(1, rand, rand) ON DUPLICATE KEY UPDATE d=VALUES(d)
T2: INSERT ... (b,c,d) VALUES (2, rand, rand),(2, rand, rand) ON DUPLICATE KEY UPDATE d=VALUES(d)
this results in supremum deadlock:
------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-05-04 10:53:24 0x7f2e1b4ee700
*** (1) TRANSACTION:
TRANSACTION 2310041, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 7 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 291, OS thread handle 139847773697792, query id 768872 localhost local_admin:sys.database update
INSERT INTO S230677 (b, c, d) VALUES (2697564,509247.782987144,1620150804984388.8),(2697564,680856.9544766529,1620150804984389.8) ON DUPLICATE KEY UPDATE d=VALUES(d)
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 26 page no 34703 n bits 304 index PRIMARY of table `test`.`S230677` trx id 2310041 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 26 page no 34703 n bits 304 index PRIMARY of table `test`.`S230677` trx id 2310041 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 2310042, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 290, OS thread handle 139847812486912, query id 768873 localhost local_admin:sys.database update
INSERT INTO S230677 (b, c, d) VALUES (2697563,36712.30166479333,1620150804984440.0),(2697563,784844.3560763699,1620150804984440.8) ON DUPLICATE KEY UPDATE d=VALUES(d)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 26 page no 34703 n bits 304 index PRIMARY of table `test`.`S230677` trx id 2310042 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 26 page no 34703 n bits 304 index PRIMARY of table `test`.`S230677` trx id 2310042 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Suggested fix:
have same non-deadlocking behavior as in 5.6
