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, durations of all metadata locks
acquired so far were changed to EXPLICIT, to prevent 
substatements from releasing these locks. When prelocked mode
was left, durations of metadata locks were changed to
TRANSACTIONAL (with a few exceptions) so they can be properly
released at the end of transaction. 
Unfortunately, this meant that the global IX lock blocking
FTWRL with STATEMENT duration was moved to TRANSACTIONAL
duration after execution of statement requiring prelocking.

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 led to unwarranted growth of
number of tickets with TRANSACITONAL duration in this
connection's 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.

Moreover, this also meant that after execution of statement
requiring prelocking concurrent FTWRL was blocked
until the end of transaction instead of end of statement.

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, the 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/mdl.h:
  Added comment describing various types of metadata lock
  duration.
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 prelocked mode.
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 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.

1 files changed, 18 insertions, 5 deletions

diff --git a/sql/mdl.h b/sql/mdl.h
index e0934cb732b..39e47da61dd 100644
--- a/sql/mdl.h
+++ b/sql/mdl.h
@@ -152,11 +152,24 @@ enum enum_mdl_type {
 
 /** Duration of metadata lock. */
 
-enum enum_mdl_duration { MDL_STATEMENT= 0,
-                         MDL_TRANSACTION,
-                         MDL_EXPLICIT,
-                         /* This should be the last ! */
-                         MDL_DURATION_END };
+enum enum_mdl_duration {
+  /**
+    Locks with statement duration are automatically released at the end
+    of statement or transaction.
+  */
+  MDL_STATEMENT= 0,
+  /**
+    Locks with transaction duration are automatically released at the end
+    of transaction.
+  */
+  MDL_TRANSACTION,
+  /**
+    Locks with explicit duration survive the end of statement and transaction.
+    They have to be released explicitly by calling MDL_context::release_lock().
+  */
+  MDL_EXPLICIT,
+  /* This should be the last ! */
+  MDL_DURATION_END };
 
 
 /** Maximal length of key for metadata locking subsystem. */