Bug#9459 - deadlock with flush with lock, and lock table write

Added a check before taking a global read lock if
the own thread has a write locked table.


mysql-test/r/flush.result:
  Bug#9459 - deadlock with flush with lock, and lock table write
  The test result.
mysql-test/t/flush.test:
  Bug#9459 - deadlock with flush with lock, and lock table write
  The test case.

3 files changed, 69 insertions, 0 deletions

diff --git a/mysql-test/r/flush.result b/mysql-test/r/flush.result
index bab9b543307..384bdc1214b 100644
--- a/mysql-test/r/flush.result
+++ b/mysql-test/r/flush.result
@@ -27,3 +27,24 @@ select * from t1;
 n
 345
 drop table t1;
+create table t1 (c1 int);
+lock table t1 write;
+flush tables with read lock;
+ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
+lock table t1 read;
+flush tables with read lock;
+lock table t1 write;
+ERROR HY000: Can't execute the query because you have a conflicting read lock
+lock table t1 read;
+lock table t1 write;
+ERROR HY000: Can't execute the query because you have a conflicting read lock
+unlock tables;
+create table t2 (c1 int);
+create table t3 (c1 int);
+lock table t1 read, t2 read, t3 write;
+flush tables with read lock;
+ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
+lock table t1 read, t2 read, t3 read;
+flush tables with read lock;
+unlock tables;
+drop table t1, t2, t3;
diff --git a/mysql-test/t/flush.test b/mysql-test/t/flush.test
index 5a9ab8db740..21ba9e8e9e7 100644
--- a/mysql-test/t/flush.test
+++ b/mysql-test/t/flush.test
@@ -70,4 +70,35 @@ insert into t1 values (345);
 select * from t1;
 drop table t1;
 
+#
+# Bug#9459 - deadlock with flush with lock, and lock table write
+#
+create table t1 (c1 int);
+lock table t1 write;
+# Cannot get the global read lock with write locked tables.
+--error 1192
+flush tables with read lock;
+lock table t1 read;
+# Can get the global read lock with read locked tables.
+flush tables with read lock;
+--error 1223
+lock table t1 write;
+lock table t1 read;
+--error 1223
+lock table t1 write;
+# Release all table locks and the global read lock.
+unlock tables;
+create table t2 (c1 int);
+create table t3 (c1 int);
+lock table t1 read, t2 read, t3 write;
+# Cannot get the global read lock with write locked tables.
+--error 1192
+flush tables with read lock;
+lock table t1 read, t2 read, t3 read;
+# Can get the global read lock with read locked tables.
+flush tables with read lock;
+# Release all table locks and the global read lock.
+unlock tables;
+drop table t1, t2, t3;
+
 # End of 4.1 tests
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index c32cbff0f5e..135cef43e90 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -5028,6 +5028,23 @@ bool reload_acl_and_cache(THD *thd, ulong options, TABLE_LIST *tables,
     if ((options & REFRESH_READ_LOCK) && thd)
     {
       /*
+        We must not try to aspire a global read lock if we have a write
+        locked table. This would lead to a deadlock when trying to
+        reopen (and re-lock) the table after the flush.
+      */
+      if (thd->locked_tables)
+      {
+        THR_LOCK_DATA **lock_p= thd->locked_tables->locks;
+        THR_LOCK_DATA **end_p= lock_p + thd->locked_tables->lock_count;
+
+        for (; lock_p < end_p; lock_p++)
+          if ((*lock_p)->type == TL_WRITE)
+          {
+            my_error(ER_LOCK_OR_ACTIVE_TRANSACTION, MYF(0));
+            return 1;
+          }
+      }
+      /*
 	Writing to the binlog could cause deadlocks, as we don't log
 	UNLOCK TABLES
       */