[MDEV-17522] INSERT ON DUPLICATE KEY UPDATE on table with unique key causes deadlocks - Jira

XML

Word

Printable

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.2.18
Fix Version/s: 10.3.11, 10.2.19
Component/s: Data Manipulation - Insert, Data Manipulation - Update, Storage Engine - InnoDB
Labels:
None

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 |

+-----------------+

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

MariaDBInsertDuplicateUpdateDeadlock.pl
2 kB
2018-10-22 19:05

Issue Links

is caused by

MDEV-17073 INSERT…ON DUPLICATE KEY UPDATE became more deadlock-prone

Closed

Activity

People

Assignee:: Marko Mäkelä

Reporter:: Geoff Montee (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2018-10-22 19:13

Updated:: 2024-07-07 23:38

Resolved:: 2018-11-02 18:17

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.