Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 5.7.20-29.24, 5.7.21-29.26
    • Fix Version/s: 5.7.22
    • Component/s: None
    • Security Level: Public Bug
    • Labels:
      None

      Description

      Cluster enters a deadlock state if table has a unique key and INSERT uses on duplicate key update statement

       

      SHOW ENGINE INNODB STATUS: 

      ------------
      TRANSACTIONS
      ------------
      Trx id counter 311392
      Purge done for trx's n:o < 311392 undo n:o < 0 state: running but idle
      History list length 36
      LIST OF TRANSACTIONS FOR EACH SESSION:
      ---TRANSACTION 311387, ACTIVE 88 sec updating or deleting
      mysql tables in use 1, locked 1
      LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
      MySQL thread id 1, OS thread handle 140517898209024, query id 131042 wsrep: updating row for write-set (155244)
      ------- TRX HAS BEEN WAITING 88 SEC FOR THIS LOCK TO BE GRANTED:
      RECORD LOCKS space id 23 page no 4 n bits 1088 index col1 of table `test`.`dbstress` trx id 311387 lock_mode X locks rec but not gap waiting
      ------------------
      TABLE LOCK table `test`.`dbstress` trx id 311387 lock mode IX
      RECORD LOCKS space id 23 page no 22 n bits 624 index PRIMARY of table `test`.`dbstress` trx id 311387 lock_mode X locks rec but not gap
      RECORD LOCKS space id 23 page no 4 n bits 1088 index col1 of table `test`.`dbstress` trx id 311387 lock_mode X locks rec but not gap waiting
      ---TRANSACTION 311386, ACTIVE (PREPARED) 88 sec
      mysql tables in use 1, locked 1
      4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
      MySQL thread id 4, OS thread handle 140517865191168, query id 131040 192.168.19.1 test wsrep: initiating pre-commit for write set (155246)
      insert into dbstress (col1) values (?) on duplicate key update col1=values(col1),id=values(id)
      TABLE LOCK table `test`.`dbstress` trx id 311386 lock mode IX
      RECORD LOCKS space id 23 page no 4 n bits 1088 index col1 of table `test`.`dbstress` trx id 311386 lock_mode X
      RECORD LOCKS space id 23 page no 17 n bits 280 index PRIMARY of table `test`.`dbstress` trx id 311386 lock_mode X locks rec but not gap
      RECORD LOCKS space id 23 page no 4 n bits 1088 index col1 of table `test`.`dbstress` trx id 311386 lock_mode X locks gap before rec
      

      How to repeat

      •  Install the latest version of pxc
      • Create database, table and user:
        CREATE DATABASE test;
        USE test;
        CREATE TABLE dbstress (id int unsigned auto_increment primary key not null, col1 int unsigned unique key);
        GRANT ALL ON test.* to test@'%' IDENTIFIED BY 'test';
      • Run ./dbstress_linux --hosts="node1:3306,node2:3306,node3:3306"
      • Wait until it starts to report N: 0 - 0

       

      I cannot reproduce on 5.6 only on 5.7

        Attachments

          Expenses

            Activity

              People

              • Assignee:
                krunal.bauskar Krunal Bauskar
                Reporter:
                marcelo.altmann Marcelo Altmann
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - 0 minutes
                  0m
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 1 day, 2 hours
                  1d 2h