Puzzling deadlocks with "on duplicate key update"

DanieleTessaroDanieleTessaro Active Member Participant
Hi all,
I'm using mysql Ver 8.0.18, with InnoDb.
I have 'rankings' table with the following structure:
+-----------+--------------+------+-----+---------+----------------+

| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| namespace | varchar(255) | NO   | MUL | NULL    |                |
| bucket    | varchar(255) | NO   |     | NULL    |                |
| item      | varchar(255) | NO   |     | NULL    |                |
| score     | bigint(20)   | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

with indexes on 
"namespace", "bucket", "score"
AND
"namespace", "item", "bucket"  UNIQUE
There's multiple processes writing into this table, each of them processing a specific namespace-bucket couple and performing a single query like:
INSERT INTO `rankings` (`namespace`,`bucket`,`item`,`score`) VALUES ('nms1','bucket1','item1',100),('nms1','bucket1','item2',200)... ON DUPLICATE KEY UPDATE `rankings`.`score`=VALUES(`score`)
With this structure I get a lot of deadlocked transactions. By looking at the logs they look like:
------------------------
LATEST DETECTED DEADLOCK------------------------2020-05-22 11:52:32 0x7fb035cfd700*** (1) TRANSACTION:TRANSACTION 1836940429, ACTIVE 0 sec insertingmysql tables in use 1, locked 1LOCK WAIT 7 lock struct(s), heap size 1136, 4 row lock(s)MySQL thread id 78953000, OS thread handle 140372543633152, query id 1578279266 xxx.xxx.xxx.net x.x.x.x user updateINSERT INTO `rankings` (`namespace`,`bucket`,`item`,`score`) VALUES ('nmsp1','bucket1','item1',28854600),('nsmp1','bucket1','item2',213846600),... ON DUPLICATE KEY UPDATE `rankings`.`score`=VALUES(`score`)

*** (1) HOLDS THE LOCK(S):RECORD LOCKS space id 74 page no 470039 n bits 216 index PRIMARY of table `db`.`rankings` trx id 1836940429 lock_mode XRecord 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 74 page no 470039 n bits 216 index PRIMARY of table `db`.`rankings` trx id 1836940429 lock_mode X insert intention waitingRecord lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:TRANSACTION 1836940430, ACTIVE 0 sec insertingmysql tables in use 1, locked 1LOCK WAIT 6 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1MySQL thread id 78953009, OS thread handle 140373338322688, query id 1578279267 x.x.x.net x0.x.0.x user updateINSERT INTO `rankings` (`namespace`,`bucket`,`item`,`score`) VALUES ('nmps2','bucket2','item3',4218),('nmsp2','bucket2','item4',17406) ON DUPLICATE KEY UPDATE `rankings`.`score`=VALUES(`score`)
The first thing that puzzles me is that, from what I read, both transactions seem to be both holding and waiting on the same exclusive lock. How is that possible? How should I interpret this log?  
I have already tried sorting the inserted rows by `score` or `item`, as recommended in the documentation, but it had no effect.
Then I tried to change the isolation level to COMMITTED READS, and this fixes the problem, I guess because the exclusive lock on the row, in case of updates, is only taken on the record rather than on the gap (although I don't understand the deep reason behind this deadlock)

But now I'm not sure if this could introduce some inconsistencies in the data.
From what I understand COMMITTED READS should only affect reads within the transaction. But what kind of reads are performed in a "insert ... on duplicate key update" transaction?
Doesn't look like those insert queries should be affected by any concurrent change of data, especially because, as I mentioned, processes work on different namespace-bucket couples.
Thanks for your help.

D.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.