[MDEV-17522] INSERT ON DUPLICATE KEY UPDATE on table with unique key causes deadlocks Created: 2018-10-22  Updated: 2020-08-25  Resolved: 2018-11-02

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Insert, Data Manipulation - Update, Storage Engine - InnoDB
Affects Version/s: 10.2.18
Fix Version/s: 10.3.11, 10.2.19

Type: Bug Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
Labels: None

Attachments: File MariaDBInsertDuplicateUpdateDeadlock.pl    
Issue Links:
Problem/Incident
is caused by MDEV-17073 INSERT…ON DUPLICATE KEY UPDATE became... Closed

 Description   

If you run the attached perl program against MariaDB 10.2, you should see deadlocks. For example, see the following output with 10.2.18:

[ec2-user@ip-172-30-0-58 ~]$ perl ./MariaDBInsertDuplicateUpdateDeadlock.pl
 
Starting thread: 1
 
Starting thread: 2
 
Starting thread: 3
 
Starting thread: 4
 
Starting thread: 5
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at ./MariaDBInsertDuplicateUpdateDeadlock.pl line 26.
 
Joining thread: 1
 
Joining thread: 2
 
Joining thread: 3
 
Joining thread: 4
 
Joining thread: 5
 
[ec2-user@ip-172-30-0-58 ~]$ mysql -u root --execute "SELECT version()"
 
+-----------------+
 
| version()       |
 
+-----------------+
 
| 10.2.18-MariaDB |
 
+-----------------+

The relevant information from SHOW ENGINE INNODB STATUS:

2018-10-22 15:02:57 0x7f11300ca700
 
*** (1) TRANSACTION:
 
TRANSACTION 1214597, ACTIVE 0 sec updating or deleting
 
mysql tables in use 1, locked 1
 
LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
 
MySQL thread id 30, OS thread handle 139711797913344, query id 592192 localhost root update
 
INSERT INTO insert_duplicate_update_tab (id, rnd) VALUES (LAST_INSERT_ID(), 1102697) ON DUPLICATE KEY UPDATE unq = LAST_INSERT_ID()
 
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
 
RECORD LOCKS space id 486 page no 40 n bits 1112 index unq of table `db1`.`insert_duplicate_update_tab` trx id 1214597 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 1214600, ACTIVE 0 sec updating or deleting
 
mysql tables in use 1, locked 1
 
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
 
MySQL thread id 27, OS thread handle 139711797307136, query id 592194 localhost root update
 
INSERT INTO insert_duplicate_update_tab (id, rnd) VALUES (LAST_INSERT_ID(), 3133877) ON DUPLICATE KEY UPDATE unq = LAST_INSERT_ID()
 
*** (2) HOLDS THE LOCK(S):
 
RECORD LOCKS space id 486 page no 40 n bits 1112 index unq of table `db1`.`insert_duplicate_update_tab` trx id 1214600 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 486 page no 40 n bits 1112 index unq of table `db1`.`insert_duplicate_update_tab` trx id 1214600 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;;
 
 
 
*** WE ROLL BACK TRANSACTION (2)

I've noticed the following patterns in these deadlocks:

1.) One transaction always has an exclusive (X) lock that appears to lock the final range (see the definition of "supremum") of the "unq" unique index on the "unq" column.

https://dev.mysql.com/doc/internals/en/innodb-infimum-and-supremum-records.html

2.) Both transactions are always waiting on an "insert intention" lock on the same range.

Point #1 above makes me think that this might be a consequence of the following:

INSERT ... ON DUPLICATE KEY UPDATE differs from a simple INSERT in that an exclusive lock rather than a shared lock is placed on the row to be updated when a duplicate-key error occurs. An exclusive index-record lock is taken for a duplicate primary key value. An exclusive next-key lock is taken for a duplicate unique key value.

https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

I wonder if the exclusive lock in point #1 could refer to an exclusive next-key lock caused by a duplicate unique key value.

Is there a way to prevent deadlocks in this scenario?

For reference, the table definition in the attached test case is:

CREATE TABLE insert_duplicate_update_tab(
 
   id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
 
   rnd int(11) NOT NULL,
 
   unq int(11) NULL,
 
   UNIQUE KEY (unq)
 
 ) ENGINE=InnoDB;

And it reproduces the issue by running the following queries in multiple threads concurrently:

INSERT INTO insert_duplicate_update_tab (rnd) VALUES (<random integer>);
 
INSERT INTO insert_duplicate_update_tab (id, rnd) VALUES (LAST_INSERT_ID(), <random integer>) ON DUPLICATE KEY UPDATE unq = LAST_INSERT_ID();

MariaDB 10.1 does not seem to be effected. For example, I encountered no deadlocks with 10.1.36:

[ec2-user@ip-172-30-0-249 ~]$ perl ./MariaDBInsertDuplicateUpdateDeadlock.pl
 
Starting thread: 1
 
Starting thread: 2
 
Starting thread: 3
 
Starting thread: 4
 
Starting thread: 5
 
Joining thread: 1
 
Joining thread: 2
 
Joining thread: 3
 
Joining thread: 4
 
Joining thread: 5
 
[ec2-user@ip-172-30-0-249 ~]$ mysql -u root --execute "SELECT version()"
 
+-----------------+
 
| version()       |
 
+-----------------+
 
| 10.1.36-MariaDB |
 
+-----------------+



 Comments   
Comment by Elena Stepanova [ 2018-10-24 ]

Deadlocks are reproducible easily, but I'm not sure if there is anything to be done about it. Assigning to marko to decide.

Comment by Marko Mäkelä [ 2018-11-02 ]

With the MDEV-17073 fix in place, I cannot repeat any deadlocks using MariaDBInsertDuplicateUpdateDeadlock.pl :

Starting thread: 1
 
Starting thread: 2
 
Starting thread: 3
 
Starting thread: 4
 
Starting thread: 5
 
Joining thread: 1
 
Joining thread: 2
 
Joining thread: 3
 
Joining thread: 4
 
Joining thread: 5

It took less than a minute with a release build (and did not finish within a reasonable time using a debug build).
With the fix reverted, I get the deadlock messages immediately, both from the debug and non-debug builds. So, MDEV-17073 definitely fixed this.

Note: Until MDEV-17603 has been fixed, these deadlocks can occur in replication, except when using row-based replication.

Comment by Marko Mäkelä [ 2019-10-01 ]

MDEV-17603 is an optimization. The deadlocks in statement-based replication were fixed in the merge of MDEV-17614 to 10.2.

Generated at Thu Feb 08 08:37:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.