Bug #48122 Request for a faster version of FLUSH TABLES WITH READ LOCK
Submitted: 16 Oct 2009 19:14 Modified: 16 Oct 2009 21:07
Reporter: Mark Callaghan Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:5.0,5.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: Backup, flush, hot, lock, read, tables
Triage: Needs Triage: D5 (Feature request)

[16 Oct 2009 19:14] Mark Callaghan
Description:
I want a faster/safer version of FLUSH TABLES WITH READ LOCK. It can be faster when it doesn't close open tables. I assume the need to close open tables is caused by MyISAM. All deployments suffer from the current implementation whether or not they need to backup MyISAM tables.

I have seen FTWRL hang (take a really long time) more than once in production from long-running queries that blocked it.

More details are here:
http://mysqlha.blogspot.com/2009/10/be-careful-with-flush-tables-with-read.html

How to repeat:
1. start a long running query in one session
2. run FLUSH TABLE WITH READ LOCK in another session (this blocks on the query from 1.)
3. try to run insert,update,delete in another sessions (blocked on the global read lock obtained in 2.)

Suggested fix:
Provide an option that gets the global read lock and sets the flag to block commit but does not close open tables.
[16 Oct 2009 19:17] Mark Callaghan
Another way to describe my request is that I want to run ' run mysqldump --master-data --single-transaction' without worrying about it hanging on FLUSH TABLES WITH READ LOCK
[16 Oct 2009 21:07] Harrison Fisk
After discussion, this bug could be obsoleted by another feature request in Bug #48124.

Closing this as a duplicate of Bug #48124.