Wednesday, July 16, 2008

What exactly does FLUSH TABLES WITH READ LOCK do?

FLUSH TABLES WITH READ LOCK can do wonders. But you should understand what it does to avoid problems. The manual describes it with this:
Closes all open tables and locks all tables for all databases with a read lock until you explicitly release the lock by executing 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 LOCK acquires a global read lock and not table locks, so it is not subject to the same behavior as LOCK TABLES and UNLOCK TABLES with respect to table locking and implicit commits:
The implementation does this:
  1. set the global read lock - after this step, insert/update/delete/replace/alter statements cannot run
  2. close open tables - this step will block until all statements started previously have stopped
  3. set a flag to block commits
If long-running statements were started prior to step 2, then the FLUSH TABLES WITH READ LOCK command will block until they complete. This is a bad state to get stuck in as the server is in read-only mode at this point and this statement is frequently run on a primary. It might be a good idea to kill long-running statements prior to running the FLUSH command or when it gets stuck. Note that commit can still be done until step 3 finishes.

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;

9 comments:

  1. Interesting. Good to get additional insight based on actual code ;-)
    Delving into Drizzle after years of not delving into MySQL much any more because it causes headache, there'll hopefully be more insights to come.

    ReplyDelete
  2. <div>Cool</div>

    ReplyDelete
  3. ugghhh, whst sn ugly way to get a consistent snapshot.

    ReplyDelete
  4. Hello I have a query ?

    How 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

    ReplyDelete
  5. 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

    ReplyDelete
  6. It 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.

    ReplyDelete
  7. A 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.

    When this command is issued to XFS, MySQL and anything running on that filesystem just interpret it as bad IO and wait.

    Tim

    ReplyDelete
  8. @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

 
Creative Commons License
This work is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.