Optimize flush tables with read lock (FTWRL) to not wait for select's

Part of MDEV-5336 Implement LOCK FOR BACKUP

The idea is that instead of waiting in close_cached_tables() for all
tables to be closed, we instead call flush_tables() that does:
- Flush not used objects in table cache to free memory
- Collect all tables that are open
- Call HA_EXTRA_FLUSH on the objects, to get them into "closed state"
- Added HA_EXTRA_FLUSH support to archive and CSV
- Added multi-user protection to HA_EXTRA_FLUSH in MyISAM and Aria

The benefit compared to old code is:
- FTWRL doesn't have to wait for long running read operations or
  open HANDLER's

1 files changed, 29 insertions, 3 deletions

diff --git a/mysql-test/main/flush_read_lock.result b/mysql-test/main/flush_read_lock.result
index 55c31ae8d12..aa67ccdce55 100644
--- a/mysql-test/main/flush_read_lock.result
+++ b/mysql-test/main/flush_read_lock.result
@@ -652,6 +652,7 @@ connection default;
 # 14.2) FLUSH TABLES <list> WITH READ LOCK is not blocked by
 #       active FTWRL. But since the latter keeps tables open
 #       FTWRL is blocked by FLUSH TABLES <list> WITH READ LOCK.
+#       Fixed by MDEV-5336
 flush tables with read lock;
 # FT <list> WRL is allowed under FTWRL at the moment.
 # It does not make much sense though.
@@ -668,12 +669,9 @@ connection default;
 flush tables t1_base, t2_base with read lock;
 connection con1;
 flush tables with read lock;
-connection con2;
-# Wait until FTWRL is blocked.
 connection default;
 unlock tables;
 connection con1;
-# Reap FTWRL.
 unlock tables;
 connection default;
 #
@@ -1677,3 +1675,31 @@ disconnect con1;
 disconnect con2;
 disconnect con3;
 set global sql_mode=default;
+#
+# Deadlock between FTWRL under open handler and DDL/LOCK TABLES
+#
+CREATE TABLE t1(a INT);
+HANDLER t1 OPEN;
+#
+connect  con1,localhost,root,,;
+SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL ready';
+LOCK TABLE t1 WRITE;
+#
+# we need to do it in a separate connection,
+# because SET DEBUG_SYNC call open_tables()/mysql_ha_flush() :(
+connect  con2,localhost,root,,;
+SET DEBUG_SYNC= 'now WAIT_FOR ready';
+disconnect con2;
+#
+connection default;
+FLUSH TABLES WITH READ LOCK;
+#
+connection con1;
+UNLOCK TABLES;
+disconnect con1;
+#
+connection default;
+UNLOCK TABLES;
+HANDLER t1 CLOSE;
+DROP TABLE t1;
+SET DEBUG_SYNC= 'RESET';