bzr commit into mysql-5.0-bugteam branch (davi:2724) Bug#43230

[Davi Arnaut]

# At a local mysql-5.0-bugteam repository of davi

 2724 Davi Arnaut	2009-04-01
      Bug#43230: SELECT ... FOR UPDATE can hang with FLUSH TABLES WITH READ LOCK indefinitely
      
      The problem is that a SELECT .. FOR UPDATE statement might open
      a table and later wait for a impeding global read lock without
      noticing whether it is holding a table that is being waited upon
      the the flush phase of the process that took the global read
      lock.
      
      The solution is to make a SELECT .. FOR UPDATE statement wait
      for a impending global read lock before opening the tables.
      If there is no impending global read lock, the statement raises
      a temporary protection against global read locks and progresses
      smoothly towards completion.
     @ mysql-test/r/lock_multi.result
        Add test case result for Bug#43230
     @ mysql-test/t/lock_multi.test
        Add test case for Bug#43230
     @ sql/sql_lex.cc
        Initialize flag.
     @ sql/sql_lex.h
        Add a flag to the lexer.
     @ sql/sql_parse.cc
        Wait for the global read lock is a write lock is going to be
        taken. The wait is done before opening tables.
     @ sql/sql_yacc.yy
        Protect against the GRL if its a SELECT .. FOR UPDATE statement.

    modified:
      mysql-test/r/lock_multi.result
      mysql-test/t/lock_multi.test
      sql/sql_lex.cc
      sql/sql_lex.h
      sql/sql_parse.cc
      sql/sql_yacc.yy
=== modified file 'mysql-test/r/lock_multi.result'
--- a/mysql-test/r/lock_multi.result	2009-03-23 20:19:41 +0000
+++ b/mysql-test/r/lock_multi.result	2009-04-01 23:57:44 +0000
@@ -133,3 +133,21 @@ ALTER TABLE t1 ADD COLUMN a INT;
 # 2.2.1. normal mode
 # 2.2.2. PS mode
 DROP TABLE t1;
+create table t1 (a int);
+create table t2 like t1;
+# con1
+lock tables t1 write;
+# con2
+flush tables with read lock;
+# con5
+# global read lock is taken
+# con3
+select * from t2 for update;
+# waiting for release of read lock
+# con4
+# would hang and later cause a deadlock
+flush tables t2;
+# clean up
+unlock tables;
+unlock tables;
+a

=== modified file 'mysql-test/t/lock_multi.test'
--- a/mysql-test/t/lock_multi.test	2009-03-23 20:19:41 +0000
+++ b/mysql-test/t/lock_multi.test	2009-04-01 23:57:44 +0000
@@ -683,6 +683,60 @@ DROP TABLE t1;
 --disconnect locker
 --disconnect writer
 
+#
+# Bug#43230: SELECT ... FOR UPDATE can hang with FLUSH TABLES WITH READ LOCK indefinitely
+#
+
+connect (con1,localhost,root,,);
+connect (con2,localhost,root,,);
+connect (con3,localhost,root,,);
+connect (con4,localhost,root,,);
+connect (con5,localhost,root,,);
+
+create table t1 (a int);
+create table t2 like t1;
+
+connection con1;
+--echo # con1
+lock tables t1 write;
+connection con2;
+--echo # con2
+send flush tables with read lock;
+connection con5;
+--echo # con5
+let $show_statement= SHOW PROCESSLIST;
+let $field= State;
+let $condition= = 'Flushing tables';
+--source include/wait_show_condition.inc
+--echo # global read lock is taken
+connection con3;
+--echo # con3
+send select * from t2 for update;
+connection con5;
+let $show_statement= SHOW PROCESSLIST;
+let $field= State;
+let $condition= = 'Waiting for release of readlock';
+--source include/wait_show_condition.inc
+--echo # waiting for release of read lock
+connection con4;
+--echo # con4
+--echo # would hang and later cause a deadlock
+flush tables t2;
+connection con1;
+--echo # clean up
+unlock tables;
+connection con2;
+--reap
+unlock tables;
+connection con3;
+--reap
+connection default;
+disconnect con5;
+disconnect con4;
+disconnect con3;
+disconnect con2;
+disconnect con1;
+
 # End of 5.0 tests
 
 # Wait till all disconnects are completed

=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc	2009-02-10 22:47:54 +0000
+++ b/sql/sql_lex.cc	2009-04-01 23:57:44 +0000
@@ -204,6 +204,7 @@ void lex_start(THD *thd)
   lex->nest_level=0 ;
   lex->allow_sum_func= 0;
   lex->in_sum_func= NULL;
+  lex->protect_against_global_read_lock= FALSE;
   DBUG_VOID_RETURN;
 }
 

=== modified file 'sql/sql_lex.h'
--- a/sql/sql_lex.h	2009-01-15 10:48:31 +0000
+++ b/sql/sql_lex.h	2009-04-01 23:57:44 +0000
@@ -1176,6 +1176,22 @@ typedef struct st_lex : public Query_tab
 
   bool escape_used;
 
+  /*
+    Special case for SELECT .. FOR UPDATE.
+
+    Protect from a impending GRL as otherwise the thread might deadlock
+    if it starts waiting for the GRL in mysql_lock_tables.
+
+    The procection is needed because there is a race between setting
+    the global read lock and waiting for all open tables to be closed.
+    The problem is a circular wait where a thread holding "old" open
+    tables will wait for the global read lock to be released while the
+    thread holding the global read lock will wait for all "old" open
+    tables to be closed -- the flush part of flush tables with read
+    lock.
+  */
+  bool protect_against_global_read_lock;
+
   st_lex();
 
   virtual ~st_lex()

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2009-03-25 17:50:42 +0000
+++ b/sql/sql_parse.cc	2009-04-01 23:57:44 +0000
@@ -2800,6 +2800,13 @@ mysql_execute_command(THD *thd)
     if (res)
       goto error;
 
+    if (!thd->locked_tables && lex->protect_against_global_read_lock &&
+        !(need_start_waiting= !wait_if_global_read_lock(thd, 0, 1)))
+    {
+      res= 1;
+      break;
+    }
+
     if (!(res= open_and_lock_tables(thd, all_tables)))
     {
       if (lex->describe)

=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy	2009-02-12 14:36:43 +0000
+++ b/sql/sql_yacc.yy	2009-04-01 23:57:44 +0000
@@ -4522,6 +4522,7 @@ select_lock_type:
 	    LEX *lex=Lex;
 	    lex->current_select->set_lock_for_tables(TL_WRITE);
 	    lex->safe_to_cache_query=0;
+            lex->protect_against_global_read_lock= TRUE;
 	  }
 	| LOCK_SYM IN_SYM SHARE_SYM MODE_SYM
 	  {


Attachment: [text/bzr-bundle] bzr/[e-mail]