MySQL Bugs: #48122: Request for a faster version of FLUSH TABLES WITH READ LOCK

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

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.

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

After discussion, this bug could be obsoleted by another feature request in Bug #48124.

Closing this as a duplicate of Bug #48124.