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

set lock_wait_timeout = 1;flush tables with read lock; lock not released after timeout

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.0.13, 10.1.22, 10.2.5, 10.0(EOL), 10.1(EOL), 10.2(EOL)
    • 10.1.25, 10.2.7, 10.3.1
    • Locking
    • None
    • probably all, tested on Centos 6 and Ubuntu Xenial.

    Description

      Reproducing:

      Prepare:

      Drop table if exists t;
      		 
      CREATE TABLE `t` (
      		 
      `id` int(11) NOT NULL AUTO_INCREMENT,
      		 
      PRIMARY KEY (`id`)
      		 
      ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 ;

      Session 1:

      set autocommit = 0;
      		 
      insert into t values(1);
      		 
      select sleep(10) from t;

      Session 2 (execute before sleep(10) is finshed)

      set lock_wait_timeout = 1;flush tables with read lock;

      Checked with metadata plugin:

      MariaDB [(none)]> select *                
          -> from information_schema.METADATA_LOCK_INFO;
      		 
      Select thread_id,lock_mode,lock_type
      		 
       from information_schema.METADATA_LOCK_INFO;
      		 
      +-----------+------------+------------------+
      		 
      | thread_id | lock_mode  | lock_type        |
      		 
      +-----------+------------+------------------+
      		 
      |         7 | MDL_SHARED | Global read lock |
      		 
      |         7 | MDL_SHARED | Commit lock      |
      		 
      |         6 | MDL_SHARED_WRITE | Table metadata lock      |
      		 
      +-----------+------------+------------------+

      Even if the metadatalock from the uncommited transaction from session 1 will be released with abort session 1, commit lock and global read lock still exists.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          Richard Richard Stracke created issue -
          serg Sergei Golubchik made changes -
          Field Original Value New Value
          Description


          Reproducing:

          Prepare:
          Drop table if exists t;
          CREATE TABLE `t` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          PRIMARY KEY (`id`)
          ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 ;

          Session 1:
          Step1: set autocommit = 0;
          Step2: insert into t values(1);
          Step 3: select sleep(10) from t;


          Session 2:
          Step 4:set lock_wait_timeout = 1;flush tables with read lock;
          (execute before Step 3 is finshed)


          Checked with metadataplugin:

          MariaDB [(none)]> select *
              -> from information_schema.METADATA_LOCK_INFO;

          Select thread_id,lock_mode,lock_type
           from information_schema.METADATA_LOCK_INFO;

          +-----------+------------+------------------+
          | thread_id | lock_mode | lock_type |
          +-----------+------------+------------------+
          | 7 | MDL_SHARED | Global read lock |
          | 7 | MDL_SHARED | Commit lock |
          | 6 | MDL_SHARED_WRITE | Table metadata lock |
          +-----------+------------+------------------+

          Even if the metadatalock from the uncommited transaction from session 1 will be released with abort session 1, commit lock and global read lock still exists.







          Reproducing:

          Prepare:
          {code:sql}
          Drop table if exists t;
          CREATE TABLE `t` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          PRIMARY KEY (`id`)
          ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 ;
          {code}
          Session 1:
          {code:sql}
          set autocommit = 0;
          insert into t values(1);
          select sleep(10) from t;
          {code}

          Session 2 (execute before {{sleep(10)}} is finshed)
          {code:sql}
          set lock_wait_timeout = 1;flush tables with read lock;
          {code}

          Checked with metadata plugin:

          {noformat}
          MariaDB [(none)]> select *
              -> from information_schema.METADATA_LOCK_INFO;
          Select thread_id,lock_mode,lock_type
           from information_schema.METADATA_LOCK_INFO;
          +-----------+------------+------------------+
          | thread_id | lock_mode | lock_type |
          +-----------+------------+------------------+
          | 7 | MDL_SHARED | Global read lock |
          | 7 | MDL_SHARED | Commit lock |
          | 6 | MDL_SHARED_WRITE | Table metadata lock |
          +-----------+------------+------------------+
          {noformat}
          Even if the metadatalock from the uncommited transaction from session 1 will be released with abort session 1, commit lock and global read lock still exists.
          elenst Elena Stepanova made changes -
          Status Open [ 1 ] Confirmed [ 10101 ]
          elenst Elena Stepanova made changes -
          Fix Version/s 10.1 [ 16100 ]
          Fix Version/s 10.2 [ 14601 ]
          Affects Version/s 10.0.13 [ 16300 ]
          Affects Version/s 10.0 [ 16000 ]
          Affects Version/s 10.1 [ 16100 ]
          Affects Version/s 10.2 [ 14601 ]
          Assignee Sergey Vojtovich [ svoj ]
          Description
          Reproducing:

          Prepare:
          {code:sql}
          Drop table if exists t;
          CREATE TABLE `t` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          PRIMARY KEY (`id`)
          ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 ;
          {code}
          Session 1:
          {code:sql}
          set autocommit = 0;
          insert into t values(1);
          select sleep(10) from t;
          {code}

          Session 2 (execute before {{sleep(10)}} is finshed)
          {code:sql}
          set lock_wait_timeout = 1;flush tables with read lock;
          {code}

          Checked with metadata plugin:

          {noformat}
          MariaDB [(none)]> select *
              -> from information_schema.METADATA_LOCK_INFO;
          Select thread_id,lock_mode,lock_type
           from information_schema.METADATA_LOCK_INFO;
          +-----------+------------+------------------+
          | thread_id | lock_mode | lock_type |
          +-----------+------------+------------------+
          | 7 | MDL_SHARED | Global read lock |
          | 7 | MDL_SHARED | Commit lock |
          | 6 | MDL_SHARED_WRITE | Table metadata lock |
          +-----------+------------+------------------+
          {noformat}
          Even if the metadatalock from the uncommited transaction from session 1 will be released with abort session 1, commit lock and global read lock still exists.           		Reproducing:

          Prepare:
          {code:sql}
          Drop table if exists t;
          CREATE TABLE `t` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          PRIMARY KEY (`id`)
          ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 ;
          {code}
          Session 1:
          {code:sql}
          set autocommit = 0;
          insert into t values(1);
          select sleep(10) from t;
          {code}

          Session 2 (execute before {{sleep(10)}} is finshed)
          {code:sql}
          set lock_wait_timeout = 1;flush tables with read lock;
          {code}

          Checked with metadata plugin:

          {noformat}
          MariaDB [(none)]> select *
              -> from information_schema.METADATA_LOCK_INFO;
          Select thread_id,lock_mode,lock_type
           from information_schema.METADATA_LOCK_INFO;
          +-----------+------------+------------------+
          | thread_id | lock_mode | lock_type |
          +-----------+------------+------------------+
          | 7 | MDL_SHARED | Global read lock |
          | 7 | MDL_SHARED | Commit lock |
          | 6 | MDL_SHARED_WRITE | Table metadata lock |
          +-----------+------------+------------------+
          {noformat}
          Even if the metadatalock from the uncommited transaction from session 1 will be released with abort session 1, commit lock and global read lock still exists.
          aadant Arnaud Adant added a comment -

          Also occurs in MySQL 5.7.18 btw.

          aadant Arnaud Adant added a comment - Also occurs in MySQL 5.7.18 btw.
          svoj Sergey Vojtovich added a comment -

          serg, please review fix for this bug.

          svoj Sergey Vojtovich added a comment - serg , please review fix for this bug.
          svoj Sergey Vojtovich made changes -
          Assignee Sergey Vojtovich [ svoj ] Sergei Golubchik [ serg ]
          Status Confirmed [ 10101 ] In Review [ 10002 ]
          serg Sergei Golubchik made changes -
          Assignee Sergei Golubchik [ serg ] Sergey Vojtovich [ svoj ]
          Status In Review [ 10002 ] Stalled [ 10000 ]
          svoj Sergey Vojtovich made changes -
          Fix Version/s 10.1.25 [ 22542 ]
          Fix Version/s 10.2.7 [ 22543 ]
          Fix Version/s 10.3.1 [ 22532 ]
          Fix Version/s 10.2 [ 14601 ]
          Fix Version/s 10.1 [ 16100 ]
          Resolution Fixed [ 1 ]
          Status Stalled [ 10000 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 80519 ] MariaDB v4 [ 152038 ]

          People

            svoj Sergey Vojtovich
            Richard Richard Stracke
            Votes:
            0 Vote for this issue
            Watchers:
            5 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.