From: MARK CALLAGHAN Date: June 23 2010 4:53pm Subject: Re: Should FLUSH TABLES WITH READ LOCK wait for updating transactions? List-Archive: http://lists.mysql.com/internals/37970 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 On Mon, Jun 21, 2010 at 12:41 PM, Konstantin Osipov wrote: > Hello, > > I'm personally convinced that FLUSH TABLES, FLUSH TABLES , > 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