MySQL Bugs: #52593: SHOW CREATE TABLE is blocked if table is locked for write by another connection

Bug #52593	SHOW CREATE TABLE is blocked if table is locked for write by another connection
Submitted:	5 Apr 2010 8:58	Modified:	8 May 2010 21:57
Reporter:	Dmitry Lenev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.5.4-m3-bzr	OS:	Any
Assigned to:	Jon Olav Hauglid	CPU Architecture:	Any
Tags:	regression

Description:
On 5.5.4-m3 based server one is no longer allowed to run SHOW CREATE TABLE for table which is locked for write by another connection (i.e. such SHOW CREATE TABLE is blocked and has to wait until table is unlocked). The same works fine on 5.1.46-bzr version of server.

Preliminary analysis shows that this is unintentional regression caused by new MDL implementation.

How to repeat:
# Here is a script for mysqltest tool which reproduces the problem

connect (con1, localhost, root,,);
connection default;

create table t1 (i int primary key);
lock table t1 write;
--echo # Switching to connection 'con1'.
connection con1;
--echo # This statement should not be blocked but it is
show create table t1;
--echo # Switching to connection 'default'.
connection default;
unlock tables;

Verified just as described. 

I consider this a serious problems as numerous tools (from mysql command line tool, depending on options passed, to WB and other GUI tools) will just hang at start up waiting for the table to be unlocked.

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/105463

2999 Jon Olav Hauglid	2010-04-13
      Bug #52593 SHOW CREATE TABLE is blocked if table is locked
                 for write by another connection
      
      The problem was that if a table was locked for write by a connection
      (for example using LOCK TABLE ... WRITE), SHOW CREATE TABLE from
      another connection would be blocked. As SHOW CREATE TABLE only reads
      metadata about the table, such blocking is not needed.
      
      The problem was that when SHOW CREATE TABLE tried to get a metadata
      lock on the table in order to open it, it used the wrong type of
      metadata lock request. It used MDL_SHARED_READ which is used when
      the intent is to read both table metadata and table data. Instead
      it should have used MDL_SHARED_HIGH_PRIO which signifies an intent
      to only read metadata.
      
      This patch fixes the problem by making sure SHOW CREATE TABLE uses
      the MDL_SHARED_HIGH_PRIO metadata lock request type when trying to
      open the table. The patch also fixes a similar problem with the
      mysql_list_fields API call.
      
      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/105597

3000 Jon Olav Hauglid	2010-04-14
      Bug #52593 SHOW CREATE TABLE is blocked if table is locked
                 for write by another connection
      
      The problem was that if a table was locked in one connection by
      LOCK TABLES ... WRITE, REPAIR TABLE or OPTIMIZE TABLE, SHOW CREATE
      TABLE from another connection would be blocked. As SHOW CREATE TABLE
      only reads metadata about the table, such blocking is not needed.
      
      The problem was that when SHOW CREATE TABLE tried to get a metadata
      lock on the table in order to open it, it used the wrong type of
      metadata lock request. It used MDL_SHARED_READ which is used when
      the intent is to read both table metadata and table data. Instead
      it should have used MDL_SHARED_HIGH_PRIO which signifies an intent
      to only read metadata.
      
      This patch fixes the problem by making sure SHOW CREATE TABLE uses
      the MDL_SHARED_HIGH_PRIO metadata lock request type when trying to
      open the table. The patch also fixes a similar problem with the
      mysql_list_fields API call.
      
      Test case added to show_check.test.

Pushed to mysql-trunk-runtime (Ver 5.5.4-m3).

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100427094135-5s49ecp3ckson6e2) (version source revid:alik@sun.com-20100427093843-uekr85qkd7orx12t) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100427093804-a2k3rrjpwu5jegu8) (version source revid:alik@sun.com-20100427093804-a2k3rrjpwu5jegu8) (merge vers: 5.5.5-m3) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100427094036-38frbg3famdlvjup) (version source revid:alik@sun.com-20100427093825-92wc8b22d4yg34ju) (pib:16)

Noted in 5.5.5, 6.0.14 changelogs.

SHOW CREATE TABLE was blocked if the table was write locked by
another session.