I'm personally convinced that FLUSH TABLES, FLUSH TABLES <list>,
FLUSH TABLES WITH READ LOCK should wait for all started *write*
(only write) transactions against the involved tables to complete.
Not waiting for started transactions to complete means that
every updating statement needs to check at the beginning whether
there is a pending global read lock. Which, pretty much, means an
extra global mutex locked for every updating statement.
In fact, today at MySQL, we lock this mutex twice -- in
wait_if_global_read_lock() and start_waiting_global_read_lock().
With the new MDL in 5.5 this is redundant, and can be killed
The metadata locks taken by a transaction can be used to resolve
the global read lock. Except that the scope of MDL locks is
wider than a single statement.
I hear this argument:
But if we have to wait for all transactions to complete
FLUSH TABLES WITH READ LOCK will take ages, it takes indefinite
time already today!
No. This is a bug, and we're fixing it in 5.5. The reason it takes
so much is that the implementation in 5.0 and 5.1 can be starved,
and the new implementation ensures that the global read lock
FLUSH TABLES <list> WITH READ LOCK in 5.5 doesn't suffer from this
bug. Besides, we plan to add LOCK DATABASE <name> READ/LOCK
DATABASE <name> WRITE in a future release. The new architecture
allows to do it.
Thus, should FLUSH TABLES WITH READ LOCK wait for started
transactions or not?