Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to understand FLUSH TABLES and FLUSH TABLES WITH READ LOCK in mysql". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to understand FLUSH TABLES and FLUSH TABLES WITH READ LOCK in mysql".

1. FLUSH TABLES closes all open tables, forcibly closes all tables in use, and flushes the query cache and prepared statement cache without refreshing dirty blocks.

2. FLUSH TABLES WITH READ LOCK closes all open tables and uses global read locks to lock all tables in all databases without refreshing dirty blocks.

3. If a table is locked by using a LOCK TABLES tbl_name lock_type statement in a session, before the table lock is released, the execution of the FLUSH TABLES statement in another session will be blocked and the execution of FLUSH TABLES WITH READ LOCK will be blocked.

4. If one session is executing DDL statements, the execution of FLUSH TABLES statements in another session will be blocked, and the execution of FLUSH TABLES WITH READ LOCK will also be blocked.

5. If a session is performing a large DML transaction (the DML statement is executing and the data is being modified, instead of using lock in share mode and for update statements to explicitly lock it), then the execution of the FLUSH TABLES statement in another session will be blocked and the execution of FLUSH TABLES WITH READ LOCK will be blocked.

6. The FLUSH TABLES WITH READ LOCK statement will not block the writing of log tables, such as query logs, slow query logs, etc.

7. The-master-data and-- lock-all-tables parameters of mysqldump trigger FLUSH TABLES and FLUSH TABLES WITH READ LOCK

8 、 FLUSH TABLES tbl_name [, tbl_name]... FOR EXPORT will refresh dirty blocks.

9. FLUSH TABLES WITH READ LOCK can be locked against a single table, for example, if only table1 is locked, then flush tables table1 with read lock

FLUSH TABLES

Https://dev.mysql.com/doc/refman/5.7/en/flush.html#flush-tables

Closes all open tables, forces all tables in use to be closed, and flushes the query cache and prepared statement cache. FLUSH TABLES also removes all query results from the query cache, like the RESET QUERY CACHE statement.

Close all open tables, force all tables in use to close, and flush the query cache and the prepared statement cache. FLUSH TABLES also removes all query results, such as RESET QUERY CACHE statements, from the query cache.

RESET QUERY CACHE

Https://dev.mysql.com/doc/refman/5.7/en/query-cache.html

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.

The query cache can be useful in an environment where you have tables that do not change very often and for which the server receives many identical queries. This is a typical situation for many Web servers that generate many dynamic pages based on database content.

The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.

The query cache stores the text of the SELECT statement and the corresponding results sent to the client. If the same statement is received later, the server retrieves the results from the query cache instead of parsing and executing the statement again. The query cache is shared between sessions, so you can send a result set generated by one client in response to the same query issued by another client.

Query caching is useful in an environment where you have tables that change infrequently and the server receives many of the same queries. This is typical of many Web servers that generate many dynamic pages based on database content.

The query cache does not return obsolete data. When you modify the table, all related entries in the query cache are refreshed.

FLUSH TABLES is not permitted when there is an active LOCK TABLES... READ. To flush and lock tables, use FLUSH TABLES tbl_name... WITH READ LOCK instead.

When a watch is in LOCK TABLES... When READ statements are locked, FLUSH TABLES statements are not allowed (another session execution of FLUSH TABLES will be blocked), if LOCK TABLES has been used. To refresh a table when the READ statement adds a read lock to another table, you can use FLUSH TABLES tbl_name in another session. WITH READ LOCK statement

Session 1 executes first

Mysql > lock tables table1 read

Session 2, blocking

Mysql > flush tables

Session 3, blocking

Mysql > flush tables table1 with read lock

Session 4, not clogged

Mysql > flush tables table2 with read lock

FLUSH TABLES tbl_name [, tbl_name]...

With a list of one or more comma-separated table names, this statement is like FLUSH TABLES with no names except that the server flushes only the named tables. If a named table does not exist, no error occurs.

Use a comma-separated list of table names to indicate that only tables with those table names are refreshed, and if the named table does not exist, no error occurs.

FLUSH TABLES WITH READ LOCK

Closes all open tables and locks all tables for all databases with a global read lock. This is a very convenient way to get backups if you have a file system such as Veritas or ZFS that can take snapshots in time. Use UNLOCK TABLES to release the lock.

Close all open tables and lock all tables in all databases using a global read lock. This is a very convenient backup method if you have a file system such as Veritas or ZFS that can take snapshots in time. Use UNLOCK TABLES to release the lock. You can use the file system that supports snapshots to make snapshot backups in time. After the backup is complete, use the UNLOCK TABLES statement to release the lock.)

FLUSH TABLES WITH READ LOCK acquires a global read lock rather than 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:

UNLOCK TABLES implicitly commits any active transaction only if any tables currently have been locked with LOCK TABLES. The commit does not occur for UNLOCK TABLES following FLUSH TABLES WITH READ LOCK because the latter statement does not acquire table locks.

Beginning a transaction causes table locks acquired with LOCK TABLES to be released, as though you had executed UNLOCK TABLES. Beginning a transaction does not release a global read lock acquired with FLUSH TABLES WITH READ LOCK.

FLUSH TABLES WITH READ LOCK acquires global read locks instead of table locks, so table locking and implicit commit do not behave like LOCK TABLES and UNLOCK TABLES statements:

When any table is currently locked by a LOCK TABLES tbl_name lock_type statement, UNLOCK TABLES implicitly commits any active transaction. However, after executing FLUSH TABLES WITH READ LOCK, executing UNLOCK TABLES does not commit because the latter statement does not acquire the table lock.

Starting a transaction results in releasing the table lock acquired using the LOCK TABLES tbl_name lock_type statement, as if you had already executed UNLOCK TABLES. Starting a transaction does not release global read locks acquired using FLUSH TABLES WITH READ LOCK.

FLUSH TABLES WITH READ LOCK is not compatible with XA transactions.

FLUSH TABLES WITH READ LOCK does not prevent the server from inserting rows into the log tables

FLUSH TABLES WITH READ LOCK is not compatible with XA transactions.

FLUSH TABLES WITH READ LOCK does not prevent the server from inserting rows into log tables, such as query logs, slow query logs, etc.

FLUSH TABLES tbl_name [, tbl_name]... WITH READ LOCK

This statement flushes and acquires read locks for the named tables. The statement first acquires exclusive metadata locks for the tables, so it waits for transactions that have those tables open to complete. Then the statement flushes the tables from the table cache, reopens the tables, acquires table locks (like LOCK TABLES... READ), and downgrades the metadata locks from exclusive to shared. After the statement acquires locks and downgrades the metadata locks, other sessions can read but not modify the tables.

Because this statement acquires table locks, you must have the LOCK TABLES privilege for each table, in addition to the RELOAD privilege that is required to use any FLUSH statement.

This statement applies only to existing base (non-TEMPORARY) tables. If a name refers to a base table, that table is used. If it refers to a TEMPORARY table, it is ignored. If a name applies to a view, an ER_WRONG_OBJECT error occurs. Otherwise, an ER_NO_SUCH_TABLE error occurs.

Use UNLOCK TABLES to release the locks, LOCK TABLES to release the locks and acquire other locks, or START TRANSACTION to release the locks and begin a new transaction.

This FLUSH TABLES variant enables tables to be flushed and locked in a single operation. It provides a workaround for the restriction that FLUSH TABLES is not permitted when there is an active LOCK TABLES... READ.

This statement does not perform an implicit UNLOCK TABLES, so an error results if you use the statement while there is any active LOCK TABLES or use it a second time without first releasing the locks acquired.

If a flushed table was opened with HANDLER, the handler is implicitly flushed and loses its position.

This statement refreshes and gets the read lock for the specified table. This statement first acquires the exclusive metadata lock for the tables, so it waits for the transaction that opened the tables to complete. The statement then refreshes the table from the table cache, reopens the table, and acquires the table lock (such as LOCK TABLES. READ) and demote the metadata lock from exclusive to shared. After the statement acquires the lock and degrades the metadata lock, other sessions can read but cannot modify the table.

Because this statement acquires a table lock, you must have LOCK TABLES permission for each table in addition to the RELOAD permissions required to use any FLUSH statement.

This statement applies only to existing basic (non-TEMPORARY) tables. If the name refers to the base table, the base table is used. If it references the TEMPORARY table, ignore it. If the name applies to the view, an ER_WRONG_OBJECT error occurs. Otherwise, an ER_NO_SUCH_TABLE error occurs.

Release the lock using UNLOCK TABLES, release the lock and acquire other locks using LOCK TABLES, or release the lock and start a new transaction using START TRANSACTION.

This FLUSH TABLES variable enables tables to be refreshed and locked in a single operation. It provides a solution when there is an active LOCK TABLES. When READ, FLUSH TABLES is not allowed.

This statement does not execute an implicit UNLOCK TABLES, so it can cause an error if you use it when there is any active LOCK TABLES, or if you use it again without first releasing the acquired lock.

If you open a refreshed table using HANDLER, the handler is implicitly refreshed and its location is lost.

FLUSH TABLES tbl_name [, tbl_name]... FOR EXPORT

This FLUSH TABLES variant applies to InnoDB tables. It ensures that changes to the named tables have been flushed to disk so that binary table copies can be made while the server is running.

The statement works like this:

A.It acquires shared metadata locks for the named tables. The statement blocks as long as other sessions have active transactions that have modified those tables or hold table locks for them. When the locks have been acquired, the statement blocks transactions that attempt to update the tables, while permitting read-only operations to continue.

B.It checks whether all storage engines for the tables support FOR EXPORT. If any do not, an ER_ILLEGAL_HA error occurs and the statement fails.

C.The statement notifies the storage engine for each table to make the table ready for export. The storage engine must ensure that any pending changes are written to disk.

D.The statement puts the session in lock-tables mode so that the metadata locks acquired earlier are not released when the FOR EXPORT statement completes.

The FLUSH TABLES... FOR EXPORT statement requires that you have the SELECT privilege for each table. Because this statement acquires table locks, you must also have the LOCK TABLES privilege for each table, in addition to the RELOAD privilege that is required to use any FLUSH statement.

This statement applies only to existing base (non-TEMPORARY) tables. If a name refers to a base table, that table is used. If it refers to a TEMPORARY table, it is ignored. If a name applies to a view, an ER_WRONG_OBJECT error occurs. Otherwise, an ER_NO_SUCH_TABLE error occurs.

InnoDB supports FOR EXPORT for tables that have their own. IBD file file (that is, tables created with the innodb_file_per_table setting enabled). InnoDB ensures when notified by the FOR EXPORT statement that any changes have been flushed to disk. This permits a binary copy of table contents to be made while the FOR EXPORT statement is in effect because the .ibd file is transaction consistent and can be copied while the server is running. FOR EXPORT does not apply to InnoDB system tablespace files, or to InnoDB tables that have FULLTEXT indexes.

FLUSH TABLES... FOR EXPORT is supported for partitioned InnoDB tables.

When notified by FOR EXPORT, InnoDB writes to disk certain kinds of data that is normally held in memory or in separate disk buffers outside the tablespace files. For each table, InnoDB also produces a file named table_name.cfg in the same database directory as the table. The .cfg file contains metadata needed to reimport the tablespace files later, into the same or different server.

When the FOR EXPORT statement completes, InnoDB will have flushed all dirty pages to the table data files. Any change buffer entries are merged prior to flushing. At this point, the tables are locked and quiescent: The tables are in a transactionally consistent state on disk and you can copy the .ibd tablespace files along with the corresponding .cfg files to get a consistent snapshot of those tables.

For the procedure to reimport the copied table data into a MySQL instance, see Section 14.6.3.7, "Copying Tablespaces to Another Instance".

After you are done with the tables, use UNLOCK TABLES to release the locks, LOCK TABLES to release the locks and acquire other locks, or START TRANSACTION to release the locks and begin a new transaction.

While any of these statements is in effect within the session, attempts to use FLUSH TABLES... FOR EXPORT produce an error:

FLUSH TABLES... WITH READ LOCK

FLUSH TABLES... FOR EXPORT

LOCK TABLES... READ

LOCK TABLES... WRITE

While FLUSH TABLES... FOR EXPORT is in effect within the session, attempts to use any of these statements produce an error:

FLUSH TABLES WITH READ LOCK

FLUSH TABLES... WITH READ LOCK

FLUSH TABLES... FOR EXPORT

FLUSH TABLES tbl_name [, tbl_name]... FOR EXPORT applies to InnoDB tables. It ensures that changes to the specified table are flushed to disk to create a copy of the binary table while the server is running.

The purpose of the declaration is as follows:

a. It acquires the shared metadata lock for the specified table. This statement blocks as long as other sessions have active transactions that have modified these tables or saved table locks for them. When the lock is acquired, the statement blocks transactions that attempt to update the table while allowing read-only operations to continue.

b. It checks whether all storage engines of the table support FOR EXPORT. If not, an ER_ILLEGAL_HA error occurs and the statement fails.

c. This statement notifies the storage engine of each table to prepare the table for export. The storage engine must ensure that all pending changes are written to disk.

d. This statement places the session in lock table mode so that previously acquired metadata locks are not released when the FOR EXPORT statement completes.

FLUSH TABLES... The FOR EXPORT statement requires that you have SELECT permission for each table. Because this statement acquires a table lock, you must have LOCK TABLES permission for each table in addition to the RELOAD permissions required to use any FLUSH statement.

This statement applies only to existing basic (non-TEMPORARY) tables. If the name refers to the base table, the base table is used. If it references the TEMPORARY table, ignore it. If the name applies to the view, an ER_WRONG_OBJECT error occurs. Otherwise, an ER_NO_SUCH_TABLE error occurs.

InnoDB supports FOR EXPORT for tables that have their own .ibd file files (that is, tables created with innodb_file_per_table settings enabled). InnoDB ensures that any changes are flushed to disk when the FOR EXPORT statement is issued. This allows a binary copy of the table contents to be made when the FOR EXPORT statement takes effect, because .ibd files are transactional consistent and can be replicated when the server running. FOR EXPORT does not apply to InnoDB system table space files, nor does it apply to InnoDB tables with FULLTEXT indexes.

FLUSH TABLES... FOR EXPORT supports partitioned InnoDB tables.

When notified by FOR EXPORT, InnoDB writes data to disk, which is usually stored in memory or in a separate disk buffer outside of the tablespace file. For each table, InnoDB also generates a file named table_name.cfg in the same database directory as the table. The .cfg file contains the metadata needed to reimport the tablespace file into the same or different servers later.

When the FOR EXPORT statement is complete, InnoDB flushes all dirty pages to the table data file. Merge any change buffer entries before flushing. At this point, the table is locked and at rest: the table is in a transactional consistent state on disk, and you can copy the .ibd tablespace file with the corresponding .cfg file to get a consistent snapshot of these tables.

For the process of re-importing replicated table data into an MySQL instance, see Section 14.6.3.7, "copying tablespaces to another instance."

After completing the table, release the lock using UNLOCK TABLES, release the lock and acquire other locks using LOCK TABLES, or release the lock and start a new transaction using START TRANSACTION.

Any of the following statements are valid in the session, but execute FLUSH TABLES in this session. FOR EXPORT produces an error:

(error message ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction, of course, execution in other sessions will not report an error, but it will wait for this session to be released.)

FLUSH TABLES... WITH READ LOCK

FLUSH TABLES... FOR EXPORT

LOCK TABLES... READ

LOCK TABLES... WRITE

Although FLUSH TABLES... FOR EXPORT takes effect in a session, but using any of the following statements in this session will result in an error:

(error message ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction, of course, execution in other sessions will not report an error, but it will wait for this session to be released.)

FLUSH TABLES WITH READ LOCK

FLUSH TABLES... WITH READ LOCK

FLUSH TABLES... FOR EXPORT

Thank you for your reading, the above is the content of "how to understand FLUSH TABLES and FLUSH TABLES WITH READ LOCK in mysql". After the study of this article, I believe you have a deeper understanding of how to understand FLUSH TABLES and FLUSH TABLES WITH READ LOCK in mysql. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.