From: Dmitry Lenev
Date: June 13 2011 8:43pm
Subject: bzr commit into mysql-5.5 branch (Dmitry.Lenev:3441) Bug#12641342
List-Archive: http://lists.mysql.com/commits/139176
X-Bug: 12641342
Message-Id: <20110613204336.F065E740409@bandersnatch>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============1588071871=="

--===============1588071871==
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

#At file:///home/dlenev/src/bzr/mysql-5.5-12641342/ based on revid:sunanda.menon@stripped

 3441 Dmitry Lenev	2011-06-14
      Fix for bug #12641342 - "61401: UPDATE PERFORMANCE DEGRADES
      GRADUALLY IF A TRIGGER EXISTS".
      
      This bug manifested itself in two ways:
      
      - Firstly execution of any data-changing statement which
        required prelocking (i.e. involved stored function or
        trigger) as part of transaction slowed down a bit all
        subsequent statements in this transaction. So performance
        in transaction which periodically involved such statements
        gradually degraded over time.
      - Secondly execution of any data-changing statement which
        required prelocking as part of transaction prevented
        concurrent FLUSH TABLES WITH READ LOCK from proceeding
        until the end of transaction instead of end of particular
        statement.
        
      The problem was caused by incorrect handling of metadata lock
      used in FTWRL implementation for statements requiring prelocked 
      mode. 
      Each statement which changes data acquires global IX lock
      with STATEMENT duration. This lock is supposed to block 
      concurrent FTWRL from proceeding until the statement ends. 
      When entering prelocked mode duration of all metadata locks
      acquired so far was changed to EXPLICIT, to prevent 
      substatements from releasing these locks. When prelocked mode
      was left duration of metadata lock was changed to TRANSACTIONAL
      (with a few exceptions) so they can be properly released at
      the end of transaction. 
      Unfortunately, this meant that global IX lock blocking FTWRL
      with STATEMENT duration was moved to TRANSACTIONAL duration 
      after execution of statement requiring prelocking. As result
      concurrent FTWRL was blocked until the end of transaction
      instead of end of statement in such a situation.
      Moreover, since each subsequent statement that required 
      prelocking and tried to acquire global IX lock with STATEMENT 
      duration got a new instance of MDL_ticket, which was later
      moved to TRANSACTIONAL duration, this also led to unwarranted
      growth of number of tickets with TRANSACITONAL duration in
      this MDL_context. As result searching for other tickets in
      it became slow and acquisition of other metadata locks by this
      transaction started to hog CPU.
      
      This patch solves this problem by not moving locks to EXPLICIT
      duration when thread enters prelocked mode (unless it is a real 
      LOCK TABLES mode). This step turned out to be not really 
      necessary as substatements don't try to release metadata locks.
      Consequently, global IX lock blocking FTWRL keeps its STATEMENT
      duration and is properly released at the end of statement and
      the above issue goes away.
     @ mysql-test/r/flush.result
        Added test for bug #12641342 - "61401: UPDATE PERFORMANCE
        DEGRADES GRADUALLY IF A TRIGGER EXISTS".
     @ mysql-test/t/flush.test
        Added test for bug #12641342 - "61401: UPDATE PERFORMANCE
        DEGRADES GRADUALLY IF A TRIGGER EXISTS".
     @ sql/sql_class.cc
        Since we no longer change duration of metadata locks to EXPLICIT
        when entering prelocked mode (unless it is a real LOCK TABLES)
        there is no need to restore proper duration of the locks when
        leaving it.
     @ sql/sql_class.h
        Do not change duration of metadata locks to EXPLICIT when
        entering prelocking mode (unless it is a real LOCK TABLES).
        This allows to avoid problems with restoring correct duration
        when leaving this mode. It is possible to do this step as
        substatements won't release metadata locks in any case.
     @ sql/sql_parse.cc
        Added assert checking that we won't release metadata locks
        when in substatement.

    modified:
      mysql-test/r/flush.result
      mysql-test/t/flush.test
      sql/sql_class.cc
      sql/sql_class.h
      sql/sql_parse.cc
=== modified file 'mysql-test/r/flush.result'
--- a/mysql-test/r/flush.result	2011-03-07 09:08:10 +0000
+++ b/mysql-test/r/flush.result	2011-06-13 20:43:29 +0000
@@ -466,3 +466,26 @@ ALTER TABLE t1 COMMENT 'test';
 ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
 UNLOCK TABLES;
 DROP TABLE t1;
+#
+# Test for bug #12641342 - "61401: UPDATE PERFORMANCE DEGRADES
+#                           GRADUALLY IF A TRIGGER EXISTS".
+#
+# One of side-effects of this bug was that a transaction which
+# involved DML statements requiring prelocking blocked concurrent
+# FLUSH TABLES WITH READ LOCK for the whole its duration, while
+# correct behavior in this case is to block FTWRL only for duration
+# of individual DML statement.
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (id INT PRIMARY KEY, value INT);
+INSERT INTO t1 VALUES (1, 1);
+CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW SET @var = "a";
+BEGIN;
+UPDATE t1 SET value= value + 1 WHERE id = 1;
+# Switching to connection 'con1'.
+# The below FLUSH TABLES WITH READ LOCK should succeed and
+# should not be blocked by the transaction in default connection.
+FLUSH TABLES WITH READ LOCK;
+UNLOCK TABLES;
+# Switching to connection 'default'.
+COMMIT;
+DROP TABLE t1;

=== modified file 'mysql-test/t/flush.test'
--- a/mysql-test/t/flush.test	2011-03-07 09:08:10 +0000
+++ b/mysql-test/t/flush.test	2011-06-13 20:43:29 +0000
@@ -668,3 +668,36 @@ ALTER TABLE t1 COMMENT 'test';
 
 UNLOCK TABLES;
 DROP TABLE t1;
+
+
+--echo #
+--echo # Test for bug #12641342 - "61401: UPDATE PERFORMANCE DEGRADES
+--echo #                           GRADUALLY IF A TRIGGER EXISTS".
+--echo #
+--echo # One of side-effects of this bug was that a transaction which
+--echo # involved DML statements requiring prelocking blocked concurrent
+--echo # FLUSH TABLES WITH READ LOCK for the whole its duration, while
+--echo # correct behavior in this case is to block FTWRL only for duration
+--echo # of individual DML statement.
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+CREATE TABLE t1 (id INT PRIMARY KEY, value INT);
+INSERT INTO t1 VALUES (1, 1);
+CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW SET @var = "a";
+BEGIN;
+UPDATE t1 SET value= value + 1 WHERE id = 1;
+
+--echo # Switching to connection 'con1'.
+connect(con1, localhost, root);
+--echo # The below FLUSH TABLES WITH READ LOCK should succeed and
+--echo # should not be blocked by the transaction in default connection.
+FLUSH TABLES WITH READ LOCK;
+UNLOCK TABLES;
+disconnect con1;
+--source include/wait_until_disconnected.inc
+
+--echo # Switching to connection 'default'.
+connection default;
+COMMIT;
+DROP TABLE t1;

=== modified file 'sql/sql_class.cc'
--- a/sql/sql_class.cc	2011-05-21 09:29:10 +0000
+++ b/sql/sql_class.cc	2011-06-13 20:43:29 +0000
@@ -3790,16 +3790,25 @@ void THD::set_mysys_var(struct st_my_thr
 
 void THD::leave_locked_tables_mode()
 {
+  if (locked_tables_mode == LTM_LOCK_TABLES)
+  {
+    /*
+      When leaving LOCK TABLES mode we should change duration for most
+      of metadata locks (with HANDLER and GRL lock being exceptions) to
+      transactional in order for them to be properly released at the
+      end of UNLOCK TABLES statement.
+    */
+    mdl_context.set_transaction_duration_for_all_locks();
+    /*
+      Make sure we don't release the global read lock and commit blocker
+      when leaving LTM.
+    */
+    global_read_lock.set_explicit_lock_duration(this);
+    /* Also ensure that we don't release metadata locks for open HANDLERs. */
+    if (handler_tables_hash.records)
+      mysql_ha_set_explicit_lock_duration(this);
+  }
   locked_tables_mode= LTM_NONE;
-  mdl_context.set_transaction_duration_for_all_locks();
-  /*
-    Make sure we don't release the global read lock and commit blocker
-    when leaving LTM.
-  */
-  global_read_lock.set_explicit_lock_duration(this);
-  /* Also ensure that we don't release metadata locks for open HANDLERs. */
-  if (handler_tables_hash.records)
-    mysql_ha_set_explicit_lock_duration(this);
 }
 
 void THD::get_definer(LEX_USER *definer)

=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2011-05-20 09:18:36 +0000
+++ b/sql/sql_class.h	2011-06-13 20:43:29 +0000
@@ -2795,7 +2795,19 @@ public:
   {
     DBUG_ASSERT(locked_tables_mode == LTM_NONE);
 
-    mdl_context.set_explicit_duration_for_all_locks();
+    if (mode_arg == LTM_LOCK_TABLES)
+    {
+      /*
+        When entering LOCK TABLES mode we should set explicit duration
+        for all metadata locks acquired so far in order to avoid releasing
+        them till UNLOCK TABLES statement.
+        We don't do this when entering prelocked mode since sub-statements
+        don't release metadata locks and restoring status-quo after leaving
+        prelocking mode gets complicated.
+      */
+      mdl_context.set_explicit_duration_for_all_locks();
+    }
+
     locked_tables_mode= mode_arg;
   }
   void leave_locked_tables_mode();

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2011-06-10 07:20:15 +0000
+++ b/sql/sql_parse.cc	2011-06-13 20:43:29 +0000
@@ -2027,6 +2027,11 @@ mysql_execute_command(THD *thd)
   */
   if (stmt_causes_implicit_commit(thd, CF_IMPLICT_COMMIT_BEGIN))
   {
+    /*
+      Note that this should never happen inside of stored functions
+      or triggers as all such statements prohibited there.
+    */
+    DBUG_ASSERT(! thd->in_sub_stmt);
     /* Commit or rollback the statement transaction. */
     thd->is_error() ? trans_rollback_stmt(thd) : trans_commit_stmt(thd);
     /* Commit the normal transaction if one is active. */


--===============1588071871==
MIME-Version: 1.0
Content-Type: text/bzr-bundle; charset="us-ascii";
	name="bzr/dmitry.lenev@stripped"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

# Bazaar merge directive format 2 (Bazaar 0.90)
# revision_id: dmitry.lenev@stripped
# target_branch: file:///home/dlenev/src/bzr/mysql-5.5-12641342/
# testament_sha1: 51b4f528df3ca6cca93ea47e64344ab7775a57a9
# timestamp: 2011-06-14 00:43:36 +0400
# base_revision_id: sunanda.menon@stripped\
#   b8uyicz8j91clngo
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWZ8JlsQAB4H/gFQQAIB4////
f+/f4L////pgEqd3e77ut7Pbuy+8nfc9T3se+wFffe9dMVN912+2Lzbt53298vU+utTrGt1gfbd2
9zew77uPG2+EkkaAJipno0DUanlGmhoAH6oNlAGg09TRoJKaJoYmgETCm0aUntKfqjTIAaZAAD1A
eoEomjQiamaaJk1AwmamgMINGgaGgAACQkiaZCNNNJ6Jk1M0nqMyRkehPU9QaGQAABFIQJiDRMIy
amBT1HpE9T9U9TIaMmmanlNPUA9IJFBGgTINE0ynqMk9G1EANANBoep6gA9QelwDBzshGPrvl4ud
jIaNHG6jWRSTQLoOr39MCqKQln63ak9tMET71bFFvnn3yyQ9P2KoO7R0Ev6aOmuizdXseM+eeqru
4708cc9Dxxxxxsch18kM/W3w6/Ngb74mp6ipR0Q2UMyrYMDElYQtVIYOMA/RJb8h190wvGrxPdLD
e1nNxfh1cTdpXTe6Hx5avh0svEBw8WYshbP5OpaIDGAQgAORAPontO5HtTAOs+M+LI2OzKRyTzo8
qPAhjbbY22ZfsFbHl2HpIUXGscPDPFQ4XxTGQ6oTcnNkfDrOBnq8R60eB7e4q5rylh+RQaPEPM5f
I8CT+jfaH9+5T57fijIgbm5oGsTAefsSbmeXh44HQQl5+KHxNpZk7NNwfmZiC5fehZ0v4pWWhcV1
6n1IxDmlHnTpomY54w/z1fZXvFWEK9u1I+5rzwlL4eVwYE7yFjgrcTGSgWldVRiiKs8GQuY3CTsX
wi0IHJaDGktepsIrvckqtaLyyVYKNuRchHBznKN0gv2pQpbaaXoeH1xJYXt4ktovytqTaf0vUtYT
x8SEr7ZDMeNzXsQt1nVi3V250skrXU0eprVzEMa1ZLXXBt/nDCF1owMWYt4VdrNl0k0MoeKvbfMp
uuPwoWPbV2I3xtWORFMC5k1LGQpCC1Lolwt401bCQ9CKyTwthdSEFQYTlK6fAjFPTktCKPJUo/iX
Jo0jAqLaMKWaDcjMtfV2Vx1o+2xxPQsx3Nx8lzsPkMVbJ2Wu5Bc7PqPsKfcUxm2UNkQoXlTH0sYi
OSGowSym1HRMykJYrZyVaz1Q0TS1BWpKMSoXNOjEoDl4FGus4Up522WUx+2VO50zp260kSroDpZE
XjDNQnkrxzYj6+Tz+xm7dJHw0mFjbcBzTyIzSenDFrRnS4EPMYUUAu4g222222222MYvNvtwNyXH
sKLNKwwvxufzvlonHhx2bmRkexH3Gh+2FxsaOiqGXAfCRorEG7OtKZRC1iRo7gY1p3LOXiWAlmHJ
0S5RT2NVHGpirZnhWOiJToOqssLubfXVvdM0vnJmbG2CeCI7SO88YjybLdBypnw2pUVFBQE4YsF5
GeYvB9fAcJRSafgne98diX14pvuPnTpZU6+fvYiPfZTAbCRhKQdQquGp1ciCkDFQkdZW2rkPoxcL
Z8wsyAuLklQoUCIYdrIX+I4epXjWxQe8iEXKCkcyh2/RYUIHvMahWKmtGnYQs4Jm4Z1kuNOiHCGK
DViRJMEb/EZ4nlYX2iQxsMY3/G+VfqR7Wq653HjpLn64l+uwleLqyMZpZo2e34tc89+h6YZdRzyT
TqXGOfjw+by6HNBUsFSS8QzXtchi+0xmJXImyuSWjjEsbqcxIyIXUg14mtREMtKJURKj5aasxrOp
MatRNaY+tG5xcCllFgLP3Jrxw62RQS4Epy8Oc5gQJF4+0sqXE/+72jihvVPVPwrjWWko87hhWAQk
yDqJPEmFoJZmzy1OsE+0s0IZXHdWl7lat4wrJPOCfUScUg0k4bq7HW9bi9jsZe4ODfU5BhhfnprH
gQs9I6olEdpyrI7zoErS6gZji+yYr5b8TSTMZMoWnojUUvRVkQiZF/yTJjvNqY6Vs5ZDRvP4rNbb
+BwUWQ2DYEh4tbkGmU1Hy9xdu/oqq+25UZxoIPRGpzsMK6DjqOSaqcto506TyIbOM95aPYUhlaST
hizIuIHRRVwlwMlcyCWxYPH1CE+B71Q3ErMrBjW+hhR5U0IFuKNK0+aD92SsOBMnuPOPrN5aar1X
VSMc9NXKFwuOxbldwufZYNyqMtI2E3LPDUzkeHLCcyl1DZiY8xxEv/LlVbYGpJhJ6DVMnt6qZrGc
JXmdMC6021oLLMUETXOM3nZeS13t5ki84FRhL3bURjfewVXGFhDLfi8fkh9zNenDCcJ9+fNcleiY
OCuuceZvOA1hneC+JD9UhynPKPSMbTaR5fQJgwMpLM5DjwIucQDhDKIpwRy7IlLyzPFW5cgH7jSE
L9wlDIP4J8j7U/IrCR+nwVYDLg5q5bTehg2NdwCqr0Iif8D/EQVFICbEL8EPso0KJYZygUCiYf9j
5J7zSpkSIMwKSNfoOBF4aA/5EMIbTWjcrZluGQQUGCneZbVmkiDQmskBlita7ls+RW46V2GK0P8G
gJLnQwMxMbX4rcGwuWVSQAQQabGmKYPMWsYg2BNFZd0EBjS4DUj+EskWEvryvRtJG0MSsPEVnfjE
oMMEtwMIlgxjCqswEGsvJkngGKTJFwXIIIzBlJFwjaJmCRUtQSSa7DaFcNiagtLHSQCWqxZi3TDB
KqAZJPe0VRHWZEAgRjEgpCDcQFwWoGRUup1tpOxSxa1kHQxEa9KWm+hUQsE1iRkQhkvyKbqYBkpa
mRI27zqPcdfUdojO17Tp+EGV25kVZEQvLC4uaI57uqwjuwhipaxYLgKgionPxjDp4n5zyE9gEy8x
kInMeZKGHcrz1mZaWKR3YYn3brtZn3LlXjvSRM62USUwbohJQwh0ZjOSStaiTA/G2/wNAQL4Gnps
DkFecm9AkrUyl98sa0KneJV+dQuaK2rvzHRzizejo1slzxsuJJNMvyKCbWDMBFRjIIDUOsAtr03g
KkERklBwlo5NUkYo/fOqIFU5VWNHfRh5nmdDt3Xuc/M/o+MzNnPgORafMa1Mvul2HMtYJqTZfbzc
CEwmBamiPYJaDqYsBnosxO/SbDWd8zpJnM7TqPSJcivib98doQe7E6QKD3j7C15HBcCLWqvoWI9p
lkE2dlsRxsekXId9YUQpo2XoLGEWkoYQpCB4CUVOLfkqX9UfE/vIPUX+qUTCUGStFn3tCOlUrkqa
GoxQYRw3qRFzqlxVYbZQyQcQeSRXsI40UqoYqCxLUmNCg5uvSiBxMNETtLaODCxLkJTC5lVEuBVa
WHI9R1C3o6S+11zRgycS0uPHuPveK4QEEHZhs6OI0Og4l2IlvsKqzNN+uxL1GX8z4CcjzMV8zDKr
Z8SZq1VI0NtJ6CAMy6tXI3B0DZJcD2e6crqUcKIRytWTbxxTHYSts8xY9ZDkdqU8/BSy1xnoMVgk
pVY7VBJAiNn39OU76sWI/BPutxz36hDhcges4H6kySfGDdDLDIS0CDdQ68i0jfvZBfuuANmuwuO/
pv5EKnbSoWGQWnhVhqf2S0u4AeFsRkhpJC2bhUgS0jG5SNuizciLXGNct70TNu1JmRSS7Pd103Ki
OBVuI9S7p/eVMHXf8bikbOErjbDYc5hKMQ4dTQ2je7vCl67xJ+JVid9qg8YwjE0OrB5HdSsIuS9C
2KajX3zgVkuO7n2Ti0Th2urXydN+0S7OliHh4KT4QhDCd55SZaiymfWOAf4teIw0cFSOONDrAKyI
AtluHzcwueM2hvXRCD2qvo5OPI+djoximTyKSyTRLcSKPb26zJgXE632KQ3FOl9Bfel/YkJcmd73
6zTJ9JSZewOo9LReJukX1SYdWspQo9yRXwkLOBSCN31PF58qm9uhIgvIO7gQLiKO/sDBTUjPTDfI
1b12B1iY22P1BHgpNPmZzB4fXfQCJ1rYYHNhm+RAxCaML6N3nOxtjY3QvBEF6KMG2DY2mwsuEojA
ONZbTQvRQttaTdglHVGiPY8fO0PoxmtPXEN9Aosi40HT0AFDBMjpHfbwtd+IpjXNVBkdCJymFyB7
OnQelDuAg36d7lZosZ6YkEqtFChrmB1jjSdQUlrL1fu9HjjILtYhF7ExNNhebEj2aCtMQ222mmGh
TAnLjb5vHIGvN5AqaWEUBEHNIoyN/T7qnkvVYrmtipz9VwV6Fycdk40LOALZsUA8/lAgkZKi9XJS
AM9t96BtjdFJDSFr87YBXwq1QeFolC6VZCZt2AmUl22L4nGVapCsjBSx5QzPGPAXPI4NmF2Nqd5u
LqcWK4cYbYc7aOdxYXg4zCEjviUELQGhDI4m/5ZTBcCFICzEbo8m8VEftwH81CvZbNJOThIjmSK8
oerV7eWrphLOTeg2jVRGNzr/A2Ky6HJyGqBvYuL4sg8cBYU1rG3WtvyqCos/KWKjtg4CZOeq0Mlg
YmNpiISKIjIkl2iTBSMNCLQ5LBVUbY2cFMrRJKiTJgNMZGP3GXWJrwrrAJFp7oBS5KoebM1TtIwa
wbTxczJM7+58W5sbhYvC1GcCEQM3MEgpCBd4M1pk+bW2uPKdC+uSMAWQD2MRrOz6aNO0l1Zs2ZS/
6O5pVvGTEECjMXVPc29jLr2qc4BKqw1vrJkBlR8hTlAYIhs1wRBIHkRQITMwkh8ssSYlH3SbykJG
0CohdMH6Km8CojctrDwgDmdAnF1uStGqD6TkHJInpXP1zDtLrQubOtdaqKogjmqc2hpelEAgiYsd
DkMMV9SmdoVleL5eRW/TUvnAfZCOOqyDe4PT3Enhy2qNbxyXpWKvaaYPpwK2d6QgoxhIQbZGETkL
sHKEZkkTDAX1iV1PYAVSaeNbJnQjqlitqYN+AXXEBRTAWiXo1UWqErSs276DocboJvTSkycOczZL
2ZPDNZ3nRWKqJO/OvRVrBG/FSwDQjIpG/ID51cb61XVGwB9mujI8N+MHZqVCd7K1lZkV1mrOOPlW
9cXpuxCGZ02uwis4LbXzoyivtjGh4ZysypT8paBNzbPsxL8L8Be7ReuUykhtLUjfBaTO/iX8r6eM
4u20RUcWE0VJVV9Sz2DObyCZkbdItzscK6YNmrfXcjxvXJJJJJJZblFVK0ilqpVRxLeENJ7d90hs
DXkJZZ5BobosQ4uDSBawx0w6Bo22SuCsz+BXUa9UbWmGqqJxDmmrz2XVzZtOIY3WvXuyyHijNfkw
ZdIMPUJOJcboUcrFpaL4VZjjE0EvgvpvrSVFNDFbRBYs4BiGQSKII6xkEZzkOLK+eOyTICMes4UF
icg6ShCSkIjzvF7TtRNBwabS9D0lQGwXrH9avu22NhpCHgL1wsYNsGnyjWmSgY00MbG2M8Z0LLvY
vJUshCpCss1C3mjT59CpX6YwCD5JBx4DCPK0SZLtTZmWl34iSh2pEHY+fcBaedAdrp2L6uu+0ggh
iMg8mToqOlyd0Y1l2QDhMuxSKt+sSupycn2u/ve3of2UV1D6F+AQUNzEiZqbOJqCqGsCCwPX7Cam
dvQVYIzasaUVXAk35HAF5zqXqDxSmOD5liz8tiG7gcW3R1q2zYjWLJQPIsFBLNNZVypFc0cMMpEF
kAEfr8Gj4vg1Yj2t8M/EXIcm7wLNWnhwG/4u5IpwoSE+Ey2I


--===============1588071871==--