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

Unexpected deadlock upon concurrent insert into sequence

    XMLWordPrintable

Details

    Description

      I am not 100% sure it's a bug, but behavior is strange, so better to check if it's intended this way.

      In the test case below,

      • 1st connection (default) starts a transaction;
      • it selects NEXTVAL from an InnoDB sequence;
      • 2nd connection attempts to insert into the sequence and starts waiting;
      • 1st connection, in turn, also attempts to insert into the sequence;
      • 1st connection receives ER_LOCK_DEADLOCK;
      • 2nd connection keeps waiting (till lock_wait_timeout is exceeded, unless 1st connection releases the sequence earlier).

      Both before and after ER_LOCK_DEADLOCK processlist says that the 2nd connection is waiting for table metadata lock.

      Two things seem strange to me.
      First, that the deadlock occurs at all, it would appear that 1st connection should be in full possession of the sequence, if the other one is waiting for the lock.
      Second, if deadlock is valid, why the 2nd transaction does not recognize it and keeps waiting.

      --source include/have_innodb.inc
       
      create sequence s engine=InnoDB;
       
      begin;
      select nextval(s);
       
      --connect (con1,localhost,root,,)
       
      --echo # INSERT starts waiting for a metadata lock
      --send 
        insert into s values (1,1,100000000000000,1,1,1000,0,0);
       
      --connection default
       
      --let $show_statement= SHOW PROCESSLIST
      --let $field= State
      --let $condition= = 'Waiting for table metadata lock'
       
      --source include/wait_show_condition.inc
       
      --error ER_LOCK_DEADLOCK
      insert into s values (1,1,100000000000001,1,1,1000,0,0);
       
      # Cleanup
      commit;
      --connection con1
      --reap
      --disconnect con1
      --connection default
      DROP SEQUENCE s;
      

      Attachments

        Issue Links

          Activity

            People

              monty Michael Widenius
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.