WL#5000: FLUSH TABLES|TABLE table_list WITH READ LOCK

With WL#4284/3726, we prohibited FLUSH TABLES under LOCK TABLE ... READ.
One is required to have a write lock to execute FLUSH TABLES.

This broke mysqlhotcopy, which uses the following sequence:
LOCK TABLE  READ;
FLUSH TABLES; // to write MyISAM keys if DELAYED KEY WRITE is on

With FLUSH TABLES  READ that will be implemented as a single
statement, which is deadlock-free. The implementation will take an
exclusive metadata lock, flush the table from the table definition cache,
acquire TL_READ data lock, and then downgrade the metadata lock to SHARED.

                
FLUSH TABLES  WITH READ LOCK.
            
In brief: take exclusive locks, expel tables from the table
cache, reopen the tables, enter the 'LOCKED TABLES' mode,
downgrade the locks.

Incompatible syntax change
--------------------------
Previously one could perform FLUSH TABLES, HOSTS, PRIVILEGES in 
a single statement.
After this change, FLUSH TABLES must always be alone on the list.
Judging by the test suite, however, the old extended syntax      
was never or very rarely used.                             
            
Required privileges
-------------------
Since the statement implicitly enters LOCK TABLES mode,
it requires LOCK TABLES privilege on every table.
But since the rest of FLUSH commands require
the global RELOAD_ACL, it also requires RELOAD_ACL.
            
Compatibility with the global read lock
---------------------------------------
We don't wait for the GRL, since neither the
5.1 combination that this new statement is intended to
replace (LOCK TABLE  WRITE; FLUSH TABLES;),
nor FLUSH TABLES WITH READ LOCK do.
@todo: this has not yet been implemented, see BUG#52044
Currently we wait for GRL in another connection,
but are compatible with a GRL in our own connection.

Behaviour under LOCK TABLES
---------------------------
Bail out: i.e. don't perform an implicit UNLOCK TABLES.
This is not consistent with LOCK TABLES statement, but is
in line with behaviour of FLUSH TABLES WITH READ LOCK, and we
try to not introduce any new statements with implicit
semantics.

Compatibility with parallel updates
-----------------------------------
As a result, we will wait for all open transactions
against the tables to complete. After the lock downgrade,
new transactions will be able to read the tables, but not
write to them.

Differences from FLUSH TABLES 
-------------------------------------
- you can't flush WITH READ LOCK a non-existent table
- you can't flush WITH READ LOCK under LOCK TABLES
- currently incompatible with the GRL (@todo: fix)
- flush tables  as of 5.5.3 doesn't yet wait
for open transactions against the tables to complete.
FLUSH TABLES  WITH READ LOCK does. We plan,
however, to fix FLUSH TABLES  in 5.5,
before it going GA.

Effect on views and temporary tables.
------------------------------------
You can only apply this command to existing base tables.
If a view with such name exists, ER_WRONG_OBJECT is returned.
If a temporary table with such name exists, it's ignored:
if there is a base table, it's used, otherwise ER_NO_SUCH_TABLE
is returned.

References
----------
BUG#42465 "mysqlhotcopy fails with "Table '' was locked with a READ
lock" error message."
BUG#1396 "Lost Data with delayed insert / mysqlhotcopy or lock and flush"
            

                
To extend the syntax of the FLUSH TABLES READ LOCK to accept the list of tables
in the form FLUSH TABLES  READ LOCK;

In the SQLCOM_FLUSH_TABLES implementation - to acquire exclusive mdl lock, then
to ease this lock to TL_READ. The table flush happens as a side effect of the
exclusive lock.

See also:
1) Initial mail discussing the feature:
https://intranet.mysql.com/secure/mailarchive/mail.php?folder=4&mail=30041
2) Discussion of the incompatible change in the syntax:
https://intranet.mysql.com/secure/mailarchive/mail.php?folder=4&mail=32004
3) commit mail - http://lists.mysql.com/commits/101761


            



        Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.