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
Triage: Triaged: D2 (Serious)

[30 Jun 2010 0:07] Elena Stepanova
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
[6 Jul 2010 12:21] Jon Olav Hauglid
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.
[6 Jul 2010 14:14] Konstantin Osipov
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).
[21 Jul 2010 14:28] Bugs System
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.
[22 Jul 2010 9:11] Bugs System
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.
[22 Jul 2010 11:53] Jon Olav Hauglid
Pushed to mysql-trunk-runtime (5.5.6). Patch approved by e-mail.
[4 Aug 2010 7:54] Bugs System
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)
[4 Aug 2010 8:11] Bugs System
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)
[4 Aug 2010 8:27] Bugs System
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)
[4 Aug 2010 9:05] Bugs System
Pushed into mysql-next-mr (revid:alik@ibmvm-20100804081630-ntapn8bf9pko9vj3) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (pib:20)
[12 Aug 2010 19:17] Paul Dubois
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).