Introducing backup locks in Percona Server

TL;DR version: The backup locks feature introduced in Percona Server 5.6.16-64.0 is a lightweight alternative to FLUSH TABLES WITH READ LOCK and can be used to take both physical and logical backups with less downtime on busy servers. To employ the feature with mysqldump, use mysqldump --lock-for-backup --single-transaction. The next release of Percona XtraBackup will also be using backup locks automatically if the target server supports the feature.

Now on to the gory details, but let’s start with some history.

In the beginning…

In the beginning there was FLUSH TABLES, and users messed with their MyISAM tables under a live server and were not ashamed. Users could do nice things like:

And users were happy until someone realized that tables must be protected against concurrent access by queries in other connections. So Monty gave them FLUSH TABLES WITH READ LOCK, and users were enlightened.

Online backups

Users then started dreaming about online backups, i.e. creating consistent snapshots of a live MySQL server. mysqldump --lock-all-tables had been a viable option for a while. To provide consistency it used FLUSH TABLES WITH READ LOCK which was not quite the right tool for the job, but was “good enough”. Who cares if a mom-and-pop shop becomes unavailable for a few seconds required to dump ~100 MB of data, right?

With InnoDB gaining popularity users had realized that one could employ MVCC to guarantee consistency and FLUSH TABLES WITH READ LOCK doesn’t make much sense for InnoDB tables anyway (you cannot modify InnoDB tables under a live server even if the server is read-only). So Peter gave mysqldump the --single-transaction option, and users were enlightened. mysqldump --single-transaction allowed to avoid FTWRL, but there was a few catches:

  • one cannot perform any schema modifications or updates to non-InnoDB tables while mysqldump --single-transaction is in progress, because those operations are not transactional and thus would ignore the data snapshot created by --single-transaction;
  • one cannot get binary log coordinates with --master-data or
    --dump-slave, because in that case FTWRL would still be used to ensure that the binary log coordinates are consistent with the data dump;

Which makes --single-transaction similar to the --no-lock option in Percona XtraBackup: it shifts the responsibility for backup consistency to the user. Any change in the workload violating the prerequisites for those options may result in a broken backup without any signs for the user to take action.


Fast forward to present day. MySQL is capable of handling over a million queries per second, MyISAM is certainly not a popular choice to store data, and there are many backup solutions to choose from. Yet all of them still rely on FLUSH TABLES WITH READ LOCK in one way or another to guarantee consistency of .frm files, non-transactional tables and binary log coordinates.

To some extent, the problem with concurrent DDL + mysqldump --single-transaction has been alleviated with metadata locks in MySQL 5.5, which however made some users unhappy, and that behavior was partially reverted in MySQL 5.6.16 with the fix for bug #71017. But the fundamental problem is still there: mysqldump --single-transaction does not guarantee consistency with concurrent DDL statements and updates to non-transactional tables.

So the fact that FTWRL is an overkill for backups has been increasingly obvious for the reasons described below.

What’s the problem with FTWRL anyway?

A lot has been written on what FLUSH TABLES WITH READ LOCK really does. Here’s yet another walk-through in a bit more detail than described elsewhere:

  1. It first invalidates the Query Cache.
  2. It then waits for all in-flight updates to complete and at the same time it blocks all incoming updates. This is one problem for busy servers.
  3. It then closes all open tables (the FLUSH part) and expels them from the table cache. This is also when FTWRL has to wait for all SELECT queries to complete. And this is another, even bigger problem for busy servers, because that wait happens to occur with all updates blocked. What’s even worse, the server at this stage is essentially offline, because even incoming SELECT queries will get blocked.
  4. Finally, it blocks COMMITs.

Action #4 is not required for the original purpose of FTWRL, but is rather a kludge implemented due to the fact that FTWRL is (mis)used by backup utilities.

Actions #1-3 make perfect sense for the original reasons why FTWRL has been implemented. If we are going to access and possibly modify tables outside of the server, we want the server to forget everything it knows about both schema and data for all tables, and flush all in-memory buffers to make the on-disk data representation consistent.

And that’s what makes it an overkill for MySQL database backup utilities: they don’t require #1, because they never modify data. #2 is only required for non-InnoDB tables, because InnoDB provides other ways to ensure consistency for both logical and physical backups. And #3 is certainly not a problem for logical backup utilities like mysqldump or mydumper, because they don’t even access on-disk data directly. As we will see, it is not a big problem for physical backup solutions either.

To FLUSH or not to FLUSH?

So what exactly is flushed by FLUSH TABLES WITH READ LOCK?

Nothing for InnoDB tables, and no physical backup solution require it to flush anything.

For MyISAM it is more complicated. MyISAM key caches are normally write-through, i.e. by the time each update to a MyISAM table completes, all index updates are written to disk. The only exception is delayed key writing feature which you should not be using anyway, if you care about your data. MyISAM may also do data buffering for bulk inserts, e.g. while executing multi-row INSERTs or LOAD DATA statements. Those buffers, however, are flushed between statements, so have no effect on physical backups as long as we block all statements updating MyISAM tables.

The point is that without flushing each storage engine is not any less backup-safe as it is crash-safe, with the only difference that backups are guaranteed to wait for all currently executing INSERT/REPLACE/DELETE/UPDATE statements to complete.

Backup locks

Enter the backup locks feature. The following 3 new SQL statements have been introduced in Percona Server:



Quoting the documentation page from the manual:

LOCK TABLES FOR BACKUP uses a new MDL lock type to block updates to non-transactional tables and DDL statements for all tables. More specifically, if there’s an active LOCK TABLES FOR BACKUP lock, all DDL statements and updates to MyISAM, CSV, MEMORY and ARCHIVE tables will be blocked in the “Waiting for backup lock” status as visible in PERFORMANCE_SCHEMA or PROCESSLIST. SELECT queries for all tables and INSERT/REPLACE/UPDATE/DELETE against InnoDB, Blackhole and Federated tables are not affected by LOCK TABLES FOR BACKUP. Blackhole tables obviously have no relevance for backups, and Federated tables are ignored by both logical and physical backup tools.


  • blocks updates to MyISAM, MEMORY, CSV and ARCHIVE tables;
  • blocks DDL against any tables;
  • does not block updates to temporary and log tables.

Unlike FTWRL, the LOCK TABLES FOR BACKUP statement:

  • does not invalidate the Query Cache;
  • never waits for SELECT queries to complete regardless of the storage engines involved;
  • never blocks SELECTs, or updates to InnoDB, Blackhole and Federated tables.

In other words, it does exactly what backup utilities need: block non-transactional changes that are included into the backup, and leave everything else to InnoDB MVCC and crash recovery.

With the only exception of binary log coordinates obtained with SHOW MASTER STATUS and SHOW SLAVE STATUS.


This is when LOCK BINLOG FOR BACKUP comes in handy. It blocks all updates to binary log coordinates as reported by SHOW MASTER/SLAVE STATUS and used by backup utilities. It has no effect when all of the following conditions apply:

  • when the binary log is disabled. If it is disabled globally, then all connections will not be affected by LOCK BINLOG FOR BACKUP. If it is enabled globally, but disabled for specific connections via sql_log_bin, only those connections are allowed to commit;
  • the server is not a replication slave;

Even if binary logging is used, LOCK BINLOG FOR BACKUP will allow DDL and updates to any tables to proceed until they will be written to binlog (i.e. commit), and/or advance Exec_Master_Log_* / Exec_Gtid_Set when executed by a replication thread, provided that no other global locks are acquired.

To release the lock acquired by LOCK TABLES FOR BACKUP there’s already UNLOCK TABLES. And the LOCK BINLOG FOR BACKUP lock is released with UNLOCK BINLOG.

Let’s look how these statements can be used by MySQL backup utilities.


mysqldump got a new option, --lock-for-backup which along with --single-transaction essentially obsoletes --lock-all-tables (i.e. FLUSH TABLES WITH READ LOCK). It makes mysqldump use LOCK TABLES FOR BACKUP before it starts dumping tables to block all “unsafe” statement that might otherwise interfere with backup consistency.

Of course, that requires backup locks support by the target server, so mysqldump checks if they are indeed supported and fails with an error if they are not.

However, at the moment if binary lock coordinates are requested with --master-data, FTWRL is still used even if --lock-for-backup is specified. mysqldump could use LOCK BINLOG FOR BACKUP, but there’s a better solution for logical backups implemented in MariaDB, which has already been ported to Percona Server and queued for the next release.

There is also another important difference between just mysqldump --single-transaction and mysqldump --lock-for-backup --single-transaction. As of MySQL 5.5 mysqldump --single-transaction acquires shared metadata locks on all tables processed within the transaction. Which will also block DDL statements on those tables when they will try to acquire an exclusive lock. So far, so good. The problems start when there’s an incoming SELECT query against a table that already has a pending DDL statement. It will also be blocked on a pending exclusive MDL request for no apparent reasons. Which was one of the complaints in bug #71017.

It’s better illustrated with an example. Suppose there are 3 sessions: one created by mysqldump, and 2 user sessions.

This is what would happen with mysqldump --lock-for-backup --single-transaction:

This immediate problem was partially fixed in MySQL 5.6.16 by releasing metadata locks after processing each table with the help of savepoints. There is a couple of issues with this approach:

  • there is still a table metadata lock for the duration of SELECT executed by mysqldump. Which, as before, blocks DDL. So there is still a chance that mysqldump --single-transaction may eventually block SELECT queries.
  • after the table is processed and the metadata lock is released, there is now an opportunity for RENAME to break the backup, see bug #71214.

Both issues above along with bug #71215 and bug #71216 do not exist with mysqldump --lock-for-backup --single-transaction as all kinds of DDL statements are properly isolated by backup locks, which do not block SELECT queries at the same time.

Percona XtraBackup

Percona XtraBackup 2.2 will support backup locks and use them automatically if supported by the server being backed up.

The current locking used by XtraBackup is:

With backup locks it becomes:

Note that under the following conditions, no blocking occurs at any stage in the server:

  • no updates to non-transactional tables;
  • no DDL;
  • binary log is disabled;

They may look familiar, because they are essentially prerequisites for the --no-lock option. Except that with backup locks, you don’t have to take chances and take responsibility for backup consistency. All the locking will be handled automatically by the server, if and when it is necessary.


mylvmbackup takes the server read-only with FLUSH TABLES WITH READ LOCK while the snapshot is being created for two reasons:

  • flush non-transactional tables
  • ensure consistency with the binary log coordinates

For exactly the same reasons as with XtraBackup, it can use backup locks instead of FTWRL.


mydumper developers may want to add support for backup locks as well. mydumper relies on START TRANSACTION WITH CONSISTENT SNAPSHOT to ensure InnoDB consistency, but has to resort to FLUSH TABLES WITH READ LOCK to ensure consistency of non-InnoDB tables and binary log coordinates.

Another problem is that START TRANSACTION WITH CONSISTENT SNAPSHOT is not supposed to be used by multi-threaded logical backup utilities. But that is an opportunity for another server-side improvement and probably a separate blog post.

Share this post

Comments (3)

  • Joe Koenig

    I noticed both mydumper and XtraBackup were discussed. Does Percona have a recommendation between the two for support customers? We have a mix of InnoDB and MyISAM tables. Thanks!

    March 14, 2014 at 12:31 pm
  • Alexey Kopytov


    Each tool has its pros and cons and thus, recommendations depend on particular goals. Here’s a excellent blog post by Ryan Huddleston explaining how both are used by the Remote DBA team:

    March 14, 2014 at 12:58 pm
  • Joe Koenig

    Great, thanks!

    March 14, 2014 at 1:30 pm

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.