Bug #55498 SHOW CREATE TRIGGER takes wrong type of metadata lock.

• Submitted: 23 Jul 2010 7:58
• Modified: 12 Aug 2010 19:11
• Reporter: Jon Olav Hauglid
• Status: Closed
• Category: MySQL Server: Locking
• Severity: S3 (Non-critical)
• Version: 5.5
• OS: Any
• Assigned to: Jon Olav Hauglid
• CPU Architecture: Any
• Tags: regression

[23 Jul 2010 7:58] Jon Olav Hauglid

Description:
SHOW CREATE TRIGGER takes MDL_SHARED_READ and not MDL_SHARED_HIGH_PRIO.
This causes unnecessary blocking. For example, LOCK TABLE WRITE in
one connection will block SHOW CREATE TRIGGER in another connection. 

Further, SHOW CREATE TRIGGER does not release its lock afterwards if
inside a transaction (this is similar to Bug#54905 for SHOW CREATE TABLE).
For example, SHOW CREATE TRIGGER inside a transaction will cause
ALTER TABLE from another connection to be blocked.

Both these issues are regressions compared to 5.1.

How to repeat:
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

CREATE TABLE t1 (a INT);
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a = 1;

--echo # Test 1: SHOW CREATE TRIGGER with WRITE locked table.

--echo # Connection con1
connect (con1, localhost, root);
LOCK TABLE t1 WRITE;

--echo # Connection default
connection default;
# Should not block.
SHOW CREATE TRIGGER t1_bi;

--echo # Connection con1
connection con1;
UNLOCK TABLES;

--echo # Test 2: ALTER TABLE with SHOW CREATE TRIGGER in transaction

--echo # Connection default
connection default;
START TRANSACTION;
SHOW CREATE TRIGGER t1_bi;

--echo # Connection con1
connection con1;
# Should not block.
ALTER TABLE t1 CHARACTER SET = utf8;

--echo # Connection default
connection default;
COMMIT;
DROP TRIGGER t1_bi;
DROP TABLE t1;
disconnect con1;

[23 Jul 2010 8:47] 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/114207

3084 Jon Olav Hauglid	2010-07-23
      Bug #55498 SHOW CREATE TRIGGER takes wrong type of metadata lock
      
      The first problem was that SHOW CREATE TRIGGER took a stronger metadata
      lock than required. This caused the statement to be blocked when it was
      not needed. For example, LOCK TABLE WRITE in one connection would block
      SHOW CREATE TRIGGER in another connection.
      
      Another problem was that a SHOW CREATE TRIGGER statement issued inside
      a transaction did not release its metadata locks at the end of the
      statement execution. This happened even if SHOW CREATE TRIGGER is an
      information statement. The consequence was that SHOW CREATE TRIGGER
      was able to block other connections from accessing the table
      (e.g. using ALTER TABLE).
      
      This patch fixes the problem by changing SHOW CREATE TRIGGER to take
      a MDL_SHARED_HIGH_PRIO metadata lock similar to what is already done
      for SHOW CREATE TABLE. The patch also changes SHOW CREATE TRIGGER to
      explicitly release any metadata locks taken by the statement after
      it completes.
      
      Test case added to show_check.test.

[23 Jul 2010 11:08] Jon Olav Hauglid

Pushed to mysql-trunk-runtime (5.5.6).

[4 Aug 2010 7:51] Bugs System

Pushed into mysql-trunk 5.5.6-m3 (revid:[e-mail]-20100731131027-1n61gseejyxsqk5d) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 8:07] Bugs System

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 8:23] Bugs System

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 9:03] Bugs System

Pushed into mysql-next-mr (revid:alik@ibmvm-20100804081630-ntapn8bf9pko9vj3) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (pib:20)

[12 Aug 2010 19:11] Paul DuBois

Noted in 5.5.6 changelog.

SHOW CREATE TRIGGER took a stronger metadata lock than required. This
caused the statement to be blocked unnecessarily. For example, LOCK
TABLES ... WRITE in one session blocked SHOW CREATE TRIGGER in
another session.
  
Also, a SHOW CREATE TRIGGER statement issued inside a transaction did
not release its metadata locks at the end of the statement execution.
This happened even if SHOW CREATE TRIGGER was an information
statement. Consequently, SHOW CREATE TRIGGER was able to block other
sessions from accessing the table (for example, using ALTER TABLE).