Not the answer you need?
Register and ask your own question!

Puzzling deadlocks with "on duplicate key update"

DanieleTessaroDanieleTessaro Current User Role Beginner
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.

Best Answer

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.