Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17522

INSERT ON DUPLICATE KEY UPDATE on table with unique key causes deadlocks

    XMLWordPrintable

Details

    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

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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