Cherry-picking a patch from Bug 12828477 from mysql-5.5

to mysql-5.5.16-release.

Original revision:
# revision-id: dmitry.lenev@oracle.com-20110811155849-feyt3h7tj48padiu
# parent: tatjana.nuernberg@oracle.com-20110811120945-c6x9a5d2du8s9oj2
# committer: Dmitry Lenev <Dmitry.Lenev@oracle.com>
# branch nick: mysql-5.5-12828477
# timestamp: Thu 2011-08-11 19:58:49 +0400
# message:
#   Fix for bug #12828477 - "MDL SUBSYSTEM CREATES BIG OVERHEAD 
#   FOR CERTAIN QUERIES TO INFORMATION_SCHEMA".
#   
#   The problem was that metadata locking subsystem introduced
#   too much overhead for queries to I_S which were processed by
#   opening only .FRM or .TRG files and had to scanned a lot of
#   tables (e.g. SELECT COUNT(*) FROM I_S.TRIGGERS was affected). 
#   The same effect was not observed for similar queries which 
#   performed full-blown table open in order to fill I_S table.
#   
#   The problem stemmed from the fact that in case when I_S 
#   implementation opened only .FRM or .TRG file for each table 
#   processed it didn't release metadata lock it has acquired on 
#   the table after finishing its processing. As result, list
#   of acquired metadata locks were growing until the end of 
#   statement. Since acquisition of each new lock required 
#   search in the list of already acquired locks performance
#   degraded.
#   
#   The same effect is not observed when I_S implementation
#   performs full-blown table open for each table being
#   processed, as in the latter cases metadata lock on the
#   table is released right after table processing.
#   
#   This fix addressed the problem by ensuring that I_S 
#   implementation releases metadata lock after processing
#   the table in both cases of full-blown table open and in 
#   case when only .FRM or .TRG file is read.

1 files changed, 28 insertions, 5 deletions

diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index fb5ed62ecdf..887115b38ad 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -3157,6 +3157,10 @@ end:
   */
   thd->temporary_tables= NULL;
   close_thread_tables(thd);
+  /*
+    Release metadata lock we might have acquired.
+    See comment in fill_schema_table_from_frm() for details.
+  */
   thd->mdl_context.rollback_to_savepoint(open_tables_state_backup->mdl_system_tables_svp);
 
   thd->lex= old_lex;
@@ -3339,6 +3343,9 @@ try_acquire_high_prio_shared_mdl_lock(THD *thd, TABLE_LIST *table,
   @param[in]      db_name                  database name
   @param[in]      table_name               table name
   @param[in]      schema_table_idx         I_S table index
+  @param[in]      open_tables_state_backup Open_tables_state object which is used
+                                           to save/restore original state of metadata
+                                           locks.
   @param[in]      can_deadlock             Indicates that deadlocks are possible
                                            due to metadata locks, so to avoid
                                            them we should not wait in case if
@@ -3356,6 +3363,7 @@ static int fill_schema_table_from_frm(THD *thd, TABLE_LIST *tables,
                                       LEX_STRING *db_name,
                                       LEX_STRING *table_name,
                                       enum enum_schema_tables schema_table_idx,
+                                      Open_tables_backup *open_tables_state_backup,
                                       bool can_deadlock)
 {
   TABLE *table= tables->table;
@@ -3501,13 +3509,27 @@ end_share:
 
 end_unlock:
   mysql_mutex_unlock(&LOCK_open);
-  /*
-    Don't release the MDL lock, it can be part of a transaction.
-    If it is not, it will be released by the call to
-    MDL_context::rollback_to_savepoint() in the caller.
-  */
 
 end:
+  /*
+    Release metadata lock we might have acquired.
+
+    Without this step metadata locks acquired for each table processed
+    will be accumulated. In situation when a lot of tables are processed
+    by I_S query this will result in transaction with too many metadata
+    locks. As result performance of acquisition of new lock will suffer.
+
+    Of course, the fact that we don't hold metadata lock on tables which
+    were processed till the end of I_S query makes execution less isolated
+    from concurrent DDL. Consequently one might get 'dirty' results from
+    such a query. But we have never promised serializability of I_S queries
+    anyway.
+
+    We don't have any tables open since we took backup, so rolling back to
+    savepoint is safe.
+  */
+  DBUG_ASSERT(thd->open_tables == NULL);
+  thd->mdl_context.rollback_to_savepoint(open_tables_state_backup->mdl_system_tables_svp);
   thd->clear_error();
   return res;
 }
@@ -3758,6 +3780,7 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond)
               int res= fill_schema_table_from_frm(thd, tables, schema_table,
                                                   db_name, table_name,
                                                   schema_table_idx,
+                                                  &open_tables_state_backup,
                                                   can_deadlock);
 
               thd->pop_internal_handler();