MySQL Bugs: #54905: Connection with WRITE lock cannot ALTER table due to concurrent SHOW CREATE

Bug #54905	Connection with WRITE lock cannot ALTER table due to concurrent SHOW CREATE
Submitted:	30 Jun 2010 0:07	Modified:	12 Aug 2010 19:17
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.5.5-m3	OS:	Any
Assigned to:	Jon Olav Hauglid	CPU Architecture:	Any
Tags:	regression

Description:
Connection which previously acquired WRITE lock on a table cannot do ALTER if another connection performed SHOW CREATE TABLE in transactional context -- the first connection keeps waiting until the second connection finishes transaction (or until lock wait timeout exceeds).

This is a regression from 5.5.4 where it was the other way round -- SHOW CREATE TABLE could not be run if another connection had a lock on the table; and a regression from 5.1.48 where both operations could be run in parallel.

I assume it is related to bug#52593 fixed in 5.5.5.

How to repeat:
--connect (con2,localhost,root,,)
--connect (con1,localhost,root,,)

--disable_warnings
DROP DATABASE IF EXISTS d;
--enable_warnings
CREATE DATABASE d;
CREATE TABLE d.t ( i INT );

--echo # Connection con1 locks the table
LOCK TABLE d.t WRITE;

--connection con2

--echo # Connection con2 performs SHOW CREATE TABLE
START TRANSACTION;
SHOW CREATE TABLE d.t;

--connection con1
--echo # Connection con1 attempts to alter the table
ALTER TABLE d.t CHARACTER SET = utf8;
UNLOCK TABLES;

--connection con2
COMMIT;
DROP DATABASE d;

--exit

These are the metadata locks taken/requested (ignoring global locks):

con1: MDL_SHARED_NO_READ_WRITE from LOCK TABLE
con2: MDL_SHARED_HIGH_PRIO from SHOW CREATE
con1: MDL_EXCLUSIVE from ALTER TABLE <= Blocked by MDL_SHARED_HIGH_PRIO

Is this really a bug?
The transaction in con2 has accessed table metadata which
will be made invalid if ALTER TABLE is allowed to proceed.

Jon Olav,
the bug here is that SHOW CREATE TABLE keeps its metadata lock on the table after completion.
SHOW CREATE is an information statement, and should not hold the tables which metadata it queried MDL-locked (compare with a SELECT from INFORMATION_SCHEMA).

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/114047

3081 Jon Olav Hauglid	2010-07-21
      Bug #54905 Connection with WRITE lock cannot ALTER table due to
                 concurrent SHOW CREATE
      
      The problem was that a SHOW CREATE TABLE statement issued inside
      a transaction did not release its metadata locks at the end of the
      statement execution. This happened even if SHOW CREATE TABLE is an
      information statement. 
      
      The consequence was that SHOW CREATE TABLE was able to block other
      connections from accessing the table (e.g. using ALTER TABLE).
      
      This patch fixes the problem by explicitly releasing any metadata
      locks taken by SHOW CREATE TABLE after the statement completes.
      
      Test case added to show_check.test.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/114119

3082 Jon Olav Hauglid	2010-07-22
      Bug #54905 Connection with WRITE lock cannot ALTER table due to
                 concurrent SHOW CREATE
      
      The problem was that a SHOW CREATE TABLE statement issued inside
      a transaction did not release its metadata locks at the end of the
      statement execution. This happened even if SHOW CREATE TABLE is an
      information statement. 
      
      The consequence was that SHOW CREATE TABLE was able to block other
      connections from accessing the table (e.g. using ALTER TABLE).
      
      This patch fixes the problem by explicitly releasing any metadata
      locks taken by SHOW CREATE TABLE after the statement completes.
      
      Test case added to show_check.test.

Pushed to mysql-trunk-runtime (5.5.6). Patch approved by e-mail.

Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-next-mr (revid:alik@ibmvm-20100804081630-ntapn8bf9pko9vj3) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (pib:20)

Noted in 5.5.6 changelog.

A SHOW CREATE TABLE statement issued inside a transaction did not
release its metadata locks at the end of statement execution.
Consequently, SHOW CREATE TABLE was able to block other sessions from
accessing the table (for example, using ALTER TABLE).