Closes all open tables and locks all tables for all databases with a read lock until you explicitly release the lock by executingThe implementation does this:UNLOCK TABLES. This is very convenient way to get backups if you have a filesystem such as Veritas that can take snapshots in time.
FLUSH TABLES WITH READ LOCKacquires a global read lock and not table locks, so it is not subject to the same behavior asLOCK TABLESandUNLOCK TABLESwith respect to table locking and implicit commits:
- set the global read lock - after this step, insert/update/delete/replace/alter statements cannot run
- close open tables - this step will block until all statements started previously have stopped
- set a flag to block commits
Here is the code:
if (lock_global_read_lock(thd))
return 1;// Killed
result=close_cached_tables(thd,(options & REFRESH_FAST) ? 0 : 1,
tables);
if (make_global_read_lock_block_commit(thd)) // Killed
{
/* Don't leave things in a half-locked state */
unlock_global_read_lock(thd);
return 1;


Interesting. Good to get additional insight based on actual code ;-)
ReplyDeleteDelving into Drizzle after years of not delving into MySQL much any more because it causes headache, there'll hopefully be more insights to come.
<div>Cool</div>
ReplyDeleteMuch better!!!!
ReplyDeleteugghhh, whst sn ugly way to get a consistent snapshot.
ReplyDeleteHello I have a query ?
ReplyDeleteHow can i make sure that FLUSH TABLES WITH READ LOCK statement has placed a global lock on the DB.
I mean how can i view global lock on a DB ?
regds
Lodh
Hi, I have a question - does the FLUSH TABLES WITH READ LOCK affect ALL database schemas running on a particular instance? I.e, when you say it's a global lock, is that global lock on all tables within the schema you issued the command to, or is the global lock on all tables within all schemas which are installed on the same MySQL Instance? Thanks, Alistair
ReplyDeleteIt is global for all databases/tables per instance. In the google patch we experimented with commmands to make particular databases readonly. But that code will go stale unless Percona picks it up. It might be a nice feature for database hosting providers.
ReplyDeleteA handy thing to use in this case, although outside of MySQL's realm is 'xfs_freeze' for XFS. I can't comment if other filesystems have a similar feature.
ReplyDeleteWhen this command is issued to XFS, MySQL and anything running on that filesystem just interpret it as bad IO and wait.
Tim
@Tim - thanks for telling me about that. DDL in MySQL isn't atomic. The two steps are 1) do something in the storage engine and 2) do something to the frm file. A snapshot taken between the two will be inconsistent for the DDL operation and almost always manual repair.
ReplyDelete