Re: Should FLUSH TABLES WITH READ LOCK wait for updating transactions?

[MARK CALLAGHAN]

On Mon, Jun 21, 2010 at 12:41 PM, Konstantin Osipov <kostja@stripped> wrote:
> Hello,
>
> 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().
>

I prefer less mutex contention. The problem I have with FLUSH TABLES
WITH READ LOCK in production is that it is blocked by long running
SELECT statements in MySQL 5.0 and 5.1. Once it blocks queries that
are started after it block. At that point you have a useless db
server. Anyone with automation that runs FTWRL must have additional
automation to detect this when this happens.

The servers I care about don't have long running insert/update/delete
statements, but there are intermittent periods where those statements
run for 60+ seconds. So even with the change you want, I can get
unacceptable (to me) outages if waits for FTWRL to complete imply any
queries that are started after it can get blocked.

I am in favor of the change you describe, but the solution for me is
to use FTWRL as little as possible.


-- 
Mark Callaghan
mdcallag@stripped