MySQL Bugs: #3522: How to lock all the databases of a MySQL instance?

Bug #3522	How to lock all the databases of a MySQL instance?
Submitted:	21 Apr 2004 6:57	Modified:	27 Apr 2004 16:32
Reporter:	[ name withheld ]	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1	OS:	HP/UX (HP UX)
Assigned to:	Dean Ellis	CPU Architecture:	Any

Description:
During a mysqldump of several databases, each of them is locked (by LOCK TABLES option) to avoid inconsistencies, but they are locked one at a time; in this way, if a transaction involving all the databases is performed, it's possible that some inconsistencies are generated.
E.g. MySQL instance manages A, B, C
mysqldump of A (A locked, B and C not locked)
mysqldump of B (B locked, A and C not locked) ... transaction T on A, B and C
transaction done on A and C
end of mysqldump of B -> transaction done on B
mysqldump of C ...

At the end the export contains just A and B without the transaction T, but it contains C with the transaction T.

It's advisable to LOCK all the databases in a shot.
Can I do it?

How to repeat:
Simply try to perform a mysqldump of multiple databases and perform in the meanwhile a transaction involving all of them.

Suggested fix:
a LOCK ALL command.

This is not a bug, however you can use FLUSH TABLES WITH READ LOCK to accomplish this, which mysqldump will use if you provide the --first-slave (or -x) option.