FLUSH TABLE with READ Lock detailed

FLUSH TABLES with READ lock abbreviation (FTWRL), which is used primarily for backup tools to obtain a consistent backup (the data matches the Binlog bit). since FTWRL requires a total of two global MDL locks, and all table objects need to be closed, this command is very lethal and can easily cause the library to hang when executing commands. In the case of the main library, the business is not properly accessible, and if it is a standby, it causes the SQL thread to get stuck and the primary standby delay. This article will detail what FTWRL did, the impact of each operation on the library, and the reasons behind the operation.

What did FTWRL do?

FTWRL mainly consists of 3 steps:

1. On global read lock (Lock_global_read_lock)
2. Clean table cache (close_cached_tables)
3. On global commit lock (Make_global_read_lock_block_commit)

FTWRL Impact of each operation

a global read lock causes all update operations to be blocked, and when a table is closed, all queries and updates that access the table will need to wait for the table to be closed, and the active transaction commits are blocked when the global commit lock is in progress. Since FTWRL is primarily used by backup tools, it will explain in detail the role of each step and the necessity of its existence. The 1th and 3rd steps in FTWRL are implemented by the MDL Lock, and I have summarized the MDL lock article before, which mainly describes the process of clearing the table cache.  

Clean Table Cache

Each table has a table_cache in memory, and the cache objects for different tables are maintained through a hash list.
The Access cache object is protected by Lock_open mutex, and when each session opens a table, the reference count is share->ref_count++,
When you close the table, the reference count Share->ref_count--。
If found to be the last reference (share->ref_count==0) of the share object, and share has old_version,
The Table_def_cache is removed from the hash list and called Free_table_share for processing. The key function closetable process is as follows:

1. Close all unused table objects
2. Update the version number of the global dictionary
3. For the Table object in use, check each, if the table is still in use, call mdl_wait::timed_wait to wait
4. Associating a wait object with a Table_cache object
5. Continue traversing the Used Table object
6. The shutdown succeeds until all tables are no longer in use.

Cleanup Table Cache function calls

Mysql_execute_command->reload_acl_and_cache->close_cached_tables
->table_share::wait_for_old_version->mdl_wait::timed_wait->
Inline_mysql_cond_timedwait

Session Action Table Flow

1. Open the table operation, if you find Old_version, wait
2.share->ref_count++
3. After the operation, check whether the share->ref_count--is 0
4. If 0, and check that a new version number is found, the cache object is considered to be overloaded
5. Remove the Cache object and call Mdl_wait::set_status to wake up all waiting threads.

Closing Table Object function calls

Dispatch_command->mysql_parse->mysql_execute_command->
Close_thread_tables->close_open_tables->close_thread_table->
Intern_close_table->closefrm->release_table_share->my_hash_delete->
Table_def_free_entry->free_table_share

Typical scenario where closing a table causes a business library to clog

Assuming that there are 3 sessions, session a performs a large query, accesses the T table, and then a backup session B is in the Shutdown table stage, needs to close table T, and then session C requests access to the T table. Three sessions are executed in this order, and we will find that the threads for backup session B and session C accessing the T table are in the "waiting for table flush" state. This is caused by the closing of the table, which is very serious because the normal select query is blocked at this time. Here's a brief explanation of why:

1. Session a opens the table T and executes ...
2. Backup session B needs to clean up cache for table T, update version number (refresh_version++)
3. Session B Discovery Table T has an older version (version! = refresh_version), which indicates that a session is accessing table T,
Wait, join the wait queue for the share object
4. Subsequent session C also finds that there is an older version (version! = refresh_version),
Wait, join the wait queue for the share object
......
5. Large query execution is complete, call Free_table_share, wake up all waiting threads.

Free_table_share//Wake up all waiting threads individually.
{
while ((ticket= it++))
Ticket->get_ctx ()->m_wait.set_status (mdl_wait::granted);
}

Between the 4th and 5th steps, all sessions that access the table are in the "waiting for table flush" State, and the wait state can be lifted only after a large query has ended.

Master and Standby switching scenarios

In the production environment, in order to recover the general MySQL service is composed of the main standby library, when the main library problems, you can switch to the standby to run, to ensure high availability of services. In this process it is important to avoid double writing. Because there are many scenarios that cause a switchover, it may be because the main library is too stressed to hang, or it may be that the main library triggers a MySQL bug restart. When we turn on the standby, if the old Main library is alive, be sure to set it to the READ_ONLY state first. The "Set global Read_only=1" command is actually similar to FTWRL and requires two MDL, but does not need to clean up the table cache. If there is still a large update transaction on the old Main library, it will cause set global read_only hang, and the setting fails. So the switch program is designed with this in mind.

Key functions: Fix_read_only

1.lock_global_read_lock (), avoid new update transactions, prevent update operations
2.make_global_read_lock_block_commit, avoid active transaction submissions

FTWRL and Backup

the MySQL backup method mainly consists of two classes, logical backup and physical backup, the typical representative of logical backup is mysqldump, the typical representative of physical backup is extrabackup. Depending on whether the backup needs to stop the service, you can divide the backup into cold and hot spares. The cold requires the server to shut down, which is largely unrealistic in the production environment and is not related to FTWRL, where hot spares are discussed. The MySQL architecture supports the plug-in storage engine, usually with the support of transactional partitioning, typically represented by MyISAM and InnoDB, which are the default storage engines for both early and present MySQL tables. Our discussions are also focused on these two types of engines. For the InnoDB storage engine, when using mysqldump to obtain a consistent backup, we often use two parameters,--single-transaction and--master-data, which guarantee the data consistency of the InnoDB table, The latter guarantees consistent site matching with data backup and is used primarily for building replication. It is also necessary to use the MySQL master and slave cluster, which is basically standard. For MyISAM, it is necessary to achieve the same goal by--lock-all-tables parameters and--master-data. We are here to review the 3 steps of the FTWRL:

1. On global read lock
2. Clearing the table cache
3. On global commit lock

The first step is to plug in the update, when the backup, we expect to get the consistent state of the database at this time, do not want to have more update operations come in. For the InnoDB engine, its own MVCC mechanism ensures that the old version of the data is read, so the first step is to make it redundant. The second step is to clean up the table cache, which makes sense for MyISAM, and when the MyISAM table is closed, the cache drop of the table is enforced, which is meaningful for physical backup MyISAM tables because physical backups are directly copying physical files. For InnoDB tables, this is not necessary because InnoDB has its own redolog, as long as the LSN is logged,and then the Redolog after the LSN is backed up. The third step, is to ensure that the consistency of the Binlog site, which for the MyISAM and InnoDB role is the same.

so overall, FTWRL for the InnoDB engine, the most important thing is to get the consistency of the site, the first two steps are optional, so if the business table is all InnoDB table, this big lock from the principle can be dismantled, and Percona company did do such things , we can refer to the blog link . In addition, the official version of 5.5 and 5.6 for the mysqldump has made an optimization, the main change is that 5.5 backup a table, lock a table, back up the next table, then lock a table, the table lock has been backed up is not released, so continue, until the backup is completed before the unified release lock. 5.6 is to back up a table, release a lock, the implementation is mainly through the innodb point of retention mechanism. Related bug can refer to link: http://bugs.mysql.com/bug.php?id=71017.

Reference documents

https://www.percona.com/blog/2014/03/11/introducing-backup-locks-percona-server-2/

https://www.percona.com/blog/2012/03/23/how-flush-tables-with-read-lock-works-with-innodb-tables/

http://bugs.mysql.com/bug.php?id=71017

Http://www.cnblogs.com/bamboos/p/3458233.html

FLUSH TABLE with READ Lock detailed