Bug#31409 RENAME TABLE causes server crash or deadlock when used with HANDLER statements

This deadlock occurs when a client issues a HANDLER ... OPEN statement
that tries to open a table that has a pending name-lock on it by another
client that also needs a name-lock on some other table which is already
open and associated to a HANDLER instance owned by the first client.
The deadlock happens because the open_table() function will back-off
and wait until the name-lock goes away, causing a circular wait if some
other name-lock is also pending for one of the open HANDLER tables.

Such situation, for example, can be easily repeated by issuing a RENAME
TABLE command in such a way that the existing table is already open
as a HANDLER table by another client and this client tries to open
a HANDLER to the new table name.

The solution is to allow handler tables with older versions (marked for
flush) to be closed before waiting for the name-lock completion. This is
safe because no other name-lock can be issued between the flush and the
check for pending name-locks.

The test case for this bug is going to be committed into 5.1 because it
requires a test feature only avaiable in 5.1 (wait_condition).

1 files changed, 11 insertions, 1 deletions

diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 289924ff418..905190cb9cd 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -1745,7 +1745,13 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
     DBUG_RETURN(0);
   }
 
-  /* close handler tables which are marked for flush */
+  /*
+    In order for the back off and re-start process to work properly,
+    handler tables having old versions (due to FLUSH TABLES or pending
+    name-lock) MUST be closed. This is specially important if a name-lock
+    is pending for any table of the handler_tables list, otherwise a
+    deadlock may occur.
+  */
   if (thd->handler_tables)
     mysql_ha_flush(thd, (TABLE_LIST*) NULL, MYSQL_HA_REOPEN_ON_USAGE, TRUE);
 
@@ -1810,6 +1816,10 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
         table->db_stat == 0 signals wait_for_locked_table_names
         that the tables in question are not used any more. See
         table_is_used call for details.
+
+        Notice that HANDLER tables were already taken care of by
+        the earlier call to mysql_ha_flush() in this same critical
+        section.
       */
       close_old_data_files(thd,thd->open_tables,0,0);
       /*