From: Guilhem Bichot Date: August 3 2010 12:28pm Subject: Re: Effect of FLUSH TABLES WITH READ LOCK on temporary tables List-Archive: http://lists.mysql.com/internals/38011 Message-Id: <4C580B8B.2060505@oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit Hello, Dmitry Lenev a écrit, Le 03.08.2010 13:11: > Hello! > > While working on 5.5 version of a fix for bug #54673 "It takes too > long to get readlock for 'FLUSH TABLES WITH READ LOCK'" I have > stumbled upon the following question: > Should an active FLUSH TABLES WITH READ LOCK block statements > that use temporary tables or not? > > The current (i.e. 5.0 and 5.1) situation is convoluted: > - Most DML statements that affect temporary tables are blocked, > but some statements, for example, something like > "SELECT f1_that_updates_table_t1()" are blocked only as long > as they use transactional temporary tables. > - Most DDL statements statements on temporary tables are blocked, > but DROP TEMPORARY TABLES is not. > What is your opinion about this issue? As long as in 5.5 we can't > preserve the status quo, we'll have to fix the issue one way or > another. Should changes to temporary tables be blocked by FTWRL? > Do I miss any scenario in which it is important? If FTWRL blocks temp tables it makes backup possible with statement-based binary logging. But on the other hand, it is going to disturb people who use temporary tables as a buffer for complex queries (to avoid using a subquery for example, or in stored procedures). I think the latter case overweighs the former. Then it depends on whether implementing this is hard or not. -- Mr. Guilhem Bichot Oracle / MySQL / Optimizer team, Lead Software Engineer Bordeaux, France www.oracle.com / www.mysql.com