Waiting for table metadata lock problem handling
When using mysql, we sometimes encounter lock waiting for table metadata lock. However, this lock wait is quite special and cannot be found in the innodb_lock_wait
and show engine innodb status
tables.
The impact of this lock
+ ------- + ------------------ + ----------- + ------ + - -------- + -------- + -------------------------------- - +------------------------------------------------ ----------------------- + | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | + ------- + ------------------ + ----------- + ------ + ---- ----- + -------- + ---------------------------------- + - -------------------------------------------------- -------------------- + | 1 | event_scheduler | localhost | NULL | Daemon | 365869 | Waiting on empty queue | NULL | | 13006 | root | localhost | NULL | Query | 35 | Waiting for table metadata lock | alter table test.t add column s int | | 13040 | root | localhost | NULL | Query | 0 | executing | select * from information_schema.processlist where command != 'sleep' | | 13044 | root | localhost | NULL | Query | 4 | Waiting for table metadata lock | update test.t set b = 10 where a = 3 | | 13043 | root | localhost | NULL | Query | 21 | Waiting for table metadata lock | select * from test.t | + ------- + ------------------ + ----------- + ------ + ---- ----- + -------- + ---------------------------------- + - -------------------------------------------------- -------------------- +
It can be seen that if a table generates Waiting for table metadata lock, then any operation on the table will be locked, including select, which will have a huge impact on production. We can reduce the length of time this lock can wait by configuring the parameter lock_wait_timeout
. But the most important thing is to reduce the occurrence of such lock waits.
lock_wait_timeout
This variable specifies the timeout in seconds for attempts to acquire metadata locks. The permissible values range from 1 to 31536000 (1 year). The default is 31536000.
This parameter controls the maximum time that the metadata lock can wait. If it exceeds, a waiting timeout will be reported. The default is 31536000 seconds, which is one year, and can be modified dynamically.
Processing method
Method 1: INNODB_TRX
SELECT t.PROCESSLIST_ID, t.PROCESSLIST_USER, t.PROCESSLIST_HOST, t.PROCESSLIST_DB, t.PROCESSLIST_STATE, t.PROCESSLIST_COMMAND, t.PROCESSLIST_TIME, t.PROCESSLIST_INFO, e.CURRENT_SCHEMA, group_concat(e.SQL_TEXT separator '\ ') as sql_text FROM `performance_schema`.threads t , `information_schema`.INNODB_TRX trx, `performance_schema`.events_statements_history e WHERE t.thread_id = e.thread_id and t.PROCESSLIST_ID = trx.trx_mysql_thread_id group by t.THREAD_ID desc
If you can only find one session from INNODB_TRX, then you are lucky, it must be caused by this.
So if you encounter more than one situation, you can use the sql in the PROCESSLIST_INFO field to determine which one it is. But in some cases, the session holding the metadata lock is in the sleep state. That is to say, the statement that caused the lock to be held has been executed, but because there was no submission or rollback, the session still held the lock. If you encounter this situation, the PROCESSLIST_INFO field may be empty, and you can only guess by judging the statements that have been executed in the session.
Of course, the guess is not a blind guess, it is a well-founded guess. Mysql has an events_statements_history
table. You can connect to this table to see what statements have been executed in the session. If there are statements involving tables waiting for locks, you can roughly guess which one it is. For example, the following result
+ ---------------- + ------------------ + ---------- -------- + ---------------- + ------------------- + ---- ------------------ + ------------------ + ------------- ----- + ---------------- + ----------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------------------- + | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_STATE | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_INFO | CURRENT_SCHEMA | sql_text | +----------------+ ------------------+------------- ----- + ---------------- + ------------------- + ------- ---------------- + ------------------ + ---------------- -- + ---------------- + -------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------------- + | 12268 | root | localhost | NULL | NULL | Sleep | 435 | NULL | NULL | select @@version_comment limit 1;select USER();begin;select * from t;select * from test.t;update t set b = 4 where a = 3;update test.t set b = 4 where a = 3 | +----------------+ ------------------+------------- ----- + ---------------- + ------------------- + ------- ---------------- + ------------------ + ---------------- -- + ---------------- + -------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------------- +
The last field shows what statements were executed in this session. Session 12268 has executed one select, two update, etc. statements. This most likely indicates that session 12268 holds the metadata lock on the t table. Generally speaking, killing the session can solve the problem.
But this events_statements_history has a limitation. It does not store all executed statements, but the latest N executed statements. This N is controlled by performance_schema_events_statements_history_size
. This parameter is read-only and can only take effect when mysql is restarted. The default is 10. If the executed statements exceed this limit, it is very likely that the statements that modify the table will not be visible. At this time, you need to guess.
Method 2: metadata_locks table
Starting from mysql5.7, there is a performance_schema.metadata_locks
table, which is used to display session information waiting for and holding metadata locks. Effectively simplifies the method of handling metadata lock waiting.
This feature is not enabled by default in version 5.7 and needs to be enabled manually. Starting from 8.0, it is enabled by default and does not require configuration.
How to open online
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';
Online closing method
UPDATE performance_schema.setup_instruments SET ENABLED = 'NO', TIMED = 'NO' WHERE NAME = 'wait/lock/metadata/sql/mdl';
Permanent opening method
Add in the mysqld block of the configuration file
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
After it is enabled, you can execute sql
SELECT t1.OBJECT_SCHEMA, t1.OBJECT_NAME, t1.LOCK_TYPE, t1.LOCK_STATUS, t2.PROCESSLIST_ID, t2.PROCESSLIST_USER, t2.PROCESSLIST_HOST, t2.PROCESSLIST_DB, t2.PROCESSLIST_COMMAND, t2.PROCESSLIST_STATE, t2.PROCESSLIST_INFO FROM `performance_schema`.metadata_locks t1, `performance_schema`.threads t2 WHERE t1.owner_thread_id = t2.thread_id AND t1.OBJECT_SCHEMA = 'test' AND t1.OBJECT_NAME = 't'
Only sessions holding or waiting for metadata locks that occur after the feature is enabled will be recorded.
+ --------------- + ------------- + ---------------- --- + ------------- + ---------------- + --------------- --- + ------------------ + ---------------- + ---------- ---------- + ---------------------------------- + ---- ---------------------------- + | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_STATUS | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_STATE | PROCESSLIST_INFO | + --------------- + ------------- + ------------------ + ------------- + ---------------- + ------------------ + ------------------ + ---------------- + ---------------- -------- + --------------------------------- + ------- ----------------------- + | test | t | SHARED_WRITE | GRANTED | 12268 | root | localhost | test | Sleep | NULL | NULL | | test | t | SHARED_READ | GRANTED | 12268 | root | localhost | test | Sleep | NULL | NULL | | test | t | SHARED_UPGRADABLE | GRANTED | 12378 | root | localhost | test | Query | Waiting for table metadata lock | alter table t add column f int | | test | t | EXCLUSIVE | PENDING | 12378 | root | localhost | test | Query | Waiting for table metadata lock | alter table t add column f int | + --------------- + ------------- + ------------------ + ------------- + ---------------- + ------------------ + ------------------ + ---------------- + ---------------- -------- + --------------------------------- + ------- ----------------------- +
In the above result, the LOCK_STATUS field indicates the connection’s holding status of the matadata lock, GRANTED indicates holding, and PENDING indicates waiting. It is easy to see that session 12268 holds the lock, and session 12378 is waiting for the lock. This problem can be solved by killing the 12268 statement or letting 12268 commit and rollback.
Summary
If your mysql is still 5.6, you can only handle it according to method one. If your mysql is version 5.7, you can first execute select * from performance_schema.setup_instruments WHERE NAME = ‘wait/lock/metadata/sql/mdl’; to check whether the metadata_locks table feature is enabled. If it is enabled, you can use the method directly. Second, deal with it. If it is mysql8, then congratulations, you can directly use method two to handle it, it is simple and fast. Generally, submitting the SQL in time or optimizing it as much as possible to shorten the execution time of the SQL can reduce the number of occurrences of Waiting for table metadata lock.
Copyright statement: This article is an original article by CSDN blogger “jaysea123” and follows the CC 4.0 BY-SA copyright agreement. Please attach the original source link and this statement when reprinting. Original link: https://blog.csdn.net/jaysea123/article/details/108073854