SoFunction
Updated on 2025-03-03

MySQL daily lock table detailed explanation of flush_tables

1. Introduction to Flush tables

Introduction to Flush tables in the official manual

Closes all open tables, forces all tables in use to be closed, and flushes the query cache.

FLUSH TABLES also removes all query results from the query cache, like the RESET QUERY CACHE statement.

Its parsing is to close all open table objects and clear the results in the query cache. That is to say, one effect of Flush tables is to wait for all running SQL requests to end.

Because, before the statement is executed, the corresponding table object will be opened, such as the select * from t1 statement, the frm file of the t1 table will be found and the table memory object will be opened.

To control the memory space and other resources used by table objects, MySQL will either implicitly (backend table object management thread) or explicitly (flush tables, etc.) close open but not used table objects.

However, the table object being used cannot be closed (such as the SQL request is still running), so the Flush Tables operation will be blocked by the running SQL request.

2. The impact of Flush tables

2.1 Flush tables

Since Flush tables will wait for all running SQL requests, will this operation block other sessions?

Below, we will use an example to illustrate.

  • Session 1:
Select sleep(50) from t1 limit 1;
--block，The execution time is50s
+-----------+
| sleep(50) |
+-----------+
| 0 |
+-----------+
1 row in set (50.13 sec)
  • Session 2:
Flush tables;
--block，Until the session1Finish
Query OK, 0 rows affected (48.27 sec)
  • Session 3:
Select c1 from t1 limit 1;
--block，Until the session1and conversation2Finish
+------+
| c1 |
+------+
| 1 |
+------+
1 row in set (47.23 sec)
  • Session 4:
Select c1 from t2 limit 1;
--No blockage
+------+
| c1 |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

Perform concurrent tests of 4 sessions, and the 4 session statements are executed in turn.

  • 1 Session 1 constructs a SQL request with execution time of 50 seconds through sleep(50). The operation table is t1
  • 2 Session 2 Execute flush tables
  • 3 Session 3 also executes a normal query for t1
  • 4 Session 4 executes a normal query on t2

From the test phenomenon, session 4 is not blocked, session 2 and session 3 wait for session 1 to complete, so

1) flush tables will wait for all running statements to end even if the query request is running;

2) If flush tables waiting for SQL request operation set is {tables}, the tables combination here should be the tables that are currently running with SQL and have been opened. In the same library, tables that do not run SQL are not included. Therefore, if there are 3 tables in the library star and tx1 and tx2 are opened, if there are any tables in the new request operation {tables}, these requests will be blocked (even ordinary queries), such as session 3;

3) If another session requests another table other than {tables}, it will not be blocked, such as session 4.

From this we can see that the flush tables operation can be considered as a table-level exclusive lock for all tables of {tables}, which will block all operations on the {tables} table in other sessions. Assuming that a flush tables operation is performed during a large query or long transaction (such as session 1), flush tables will wait for the end of the long transaction (such as session 1) while blocking new requests for {tables}.

2.2 Flush tables with read lock

Flush tables with read lock is another common operation. It works the same as Flush tables. It will also wait for all running SQL requests to end, but add a global read lock, that is, blocking all tables of all libraries until the unlock tables operation is completed.

Use an example to illustrate their differences:

  • Session 1:
Select sleep(50) from t1 limit 1;
--block，The execution time is50s
+-----------+
| sleep(50) |
+-----------+
| 0 |
+-----------+
1 row in set (50.13 sec)
  • Session 2:
Flush tables with read lock;
--block，Until the session1Finish
Query OK, 0 rows affected (48.27 sec)

Unlock tables;
Query OK, 0 rows affected (0.01 sec)

Session 3:

Select c1 from t1 limit 1;
--block，Until the session1and conversation2Finish
+------+
| c1 |
+------+
| 1 |
+------+
1 row in set (47.23 sec)
  • Session 4:
Select c1 from t2 limit 1;
--No blockage
+------+
| c1 |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
  • Session 5:
Insert into t2(c1) values(10);
--block，Until the session2Execute inunlock tables
Query OK, 1 row affected (50.23 sec)

Similar to the first example, multiple sessions are executed in sequence. The difference is that session 2 executes Flush tables with read lock, and adds session 5 to insert records on t2 tables.

From the test phenomenon, this operation has the following characteristics:

1) Like flush tables, flush tables with read lock will wait for all running statements to end (such as session 1);

2) If flush tables with read lock waiting for SQL request operation set is {tables}, if there is any table in {tables} for new request operation, these requests will block

  • a) If it is a query request, it can be executed after the flush tables with read lock ends, such as session 3;
  • b) If it is a write request such as insertion, update, etc., you must wait for unlock tables to release the read lock, which is similar to session 5
  • 3) If another session requests another table other than {tables},
  • a) If it is a query request, it will not be blocked, such as session 4;
  • b) If it is a write request, you must wait for unlock tables to release the read lock, such as session 5

Therefore, the flush tables with read lock operation is a table-level exclusive lock for all tables {tables}, and is also a library-level read lock, which blocks all write operations on the library until unlock tables are executed. Its influence is greater than flush tables.

That is, it has a little more impact than flush tables, blocking the write operation of tables other than {tables}, and does not affect its read operation. Only after unlocking tables and releasing the global read lock to the library can you write.

3. Consistent backup issues

Generally speaking, flush tables and flush tables with read lock operations are rarely used on the active use.

More use of these two commands is when mysqldump is used for data backup.

If the parameters --master-data and --single-transaction are generally specified when using mysqldump for consistent backup, then before the backup operation is executed, the two commands of flush tables and flush tables with read lock are executed to obtain the binlog position for consistent read.

The process of obtaining the binlog location is:

  • 1) The flush tables operation is to wait for all running operations to end;
  • 2) flush tables with read locks are to add library-level global read locks and prohibit writing operations;
  • 3) Obtain the binlog position at this time through show master status;
  • 4) unlock tables release global read locks and allow write requests.

The reason for executing flush tables first instead of executing flush tables with read locks directly is that flush tables are less likely to block other requests. Assuming that a large query appears in the process of flush tables, from the previous analysis, it will only affect other session requests about the {tables} tables, unlike flush tables with read locks that block all write operations.

However, the above operations only greatly reduce the impact of global read locks. If there is a large transaction between flush tables and flush tables with read locks, all write operations may still be hang. Therefore, the functionality of consistent backup must be used with caution.

In addition, after testing, if flush tables block other sessions, such as session 3 and session 5 operations, the slow query log will not be recorded, but in fact, the application may not receive a quick response.

solve

1：show open tables where in_use >=1;

2: Find the query statement that blocks the table

SELECT * FROM information_schema.`PROCESSLIST`
WHERE info IS NOT NULL
AND state NOT LIKE 'Waiting for table flush'
AND info NOT LIKE 'SELECT * FROM information_schema.`PROCESSLIST`%'
AND INFO LIKE '%Table name %'
ORDER BY TIME DESC

3: kill id kills the sql process

Note: You can also kill the flush tables process. This operation is used with caution and may be performed consistent backups.

SELECT * FROM information_schema.`PROCESSLIST`
WHERE info LIKE '%flush tables%'
AND info NOT LIKE 'SELECT * FROM information_schema.`PROCESSLIST`%'

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.