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.
As far as I understand FLUSH TABLES WITH READ LOCK is mostly used
for performing backups/creating database copies and bootstrapping
Since temporary tables are not preserved after server restart,
and, thus, are not necessary or in any way used in backups or for
bootstrapping of a slave, modification of temporary tables
during backup process probably can be allowed (**).
(**) There is a gotcha here:
Backup tools (e.g. mysqldump) assume that the state of the tables
locked with FTWRL and the binlog are in sync. Allowing statements
that change temporary tables to proceed under FTWRL means that
FTWRL no longer freezes the binlog position in statement-based
OTOH, on systems where statements with temporary tables end up
in the binary log some filtering of the binary log events
on restore is necessary anyway, since, as I mentioned earlier,
all temporary tables are lost after server restart.
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?
Dmitry Lenev, Software Developer
Sun Microsystems DBG/MySQL, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification