Why MySQL appears waiting for table metadata lock and how to fix it

Why MySQL appears waiting for table metadata lock and how to fix it

When MySQL is doing DDL operations such as ALTER TABLE, there are times when waiting for table metadata lock is waiting for the scene. Furthermore, once ALTER TABLE TableA's operation is stuck in the waiting for table metadata lock state, any subsequent operations on TABLEA (including read) will not work because they are also in opening The tables stage enters the lock wait queue for the waiting for table metadata lock. If there is such a lock waiting queue in the core table of the product environment, it will have disastrous results.

The reason for the ALTER table to produce waiting for table metadata lock is actually very simple, typically following a few simple scenarios:

Scenario One: Long things run, Block DDL, and then block all subsequent operations on the same table

With show Processlist you can see that there is an ongoing operation (including read) on the TableA, at which time the ALTER TABLE statement cannot get to the metadata exclusive lock and waits.

This is the most basic scenario, and this does not conflict with the online DDL in MySQL 5.6. During the operation of the general ALTER TABLE (see), when the after create step acquires the metadata exclusive lock, the read-write to the table can be performed normally when the process to altering table (usually the most time-taking step) is performed, which is the online The performance of the DDL does not block writes as before throughout the ALTER table procedure. (Of course, not all types of alter operations can be online, see the Official manual: http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html)
Workaround: kill the session where the DDL is located.

Scenario Two: Uncommitted things, blocking DDL, and then blocking all subsequent operations on the same table

There is no action on TableA through show processlist, but there are actually uncommitted transactions that can be found in Information_schema.innodb_trx . The lock on the TableA is not released until the transaction is complete, and ALTER TABLE also acquires an exclusive lock of metadata.

Processing method: Through the select * from information_schema.innodb_trx\g, find the SID of the uncommitted thing, and then kill it and let it roll back.

Scenario Three:

There is no action on TableA through show processlist, and there is no ongoing transaction in Information_schema.innodb_trx. This is most likely because in an explicit transaction, a failed operation was performed on TableA (such as querying a nonexistent field), and the transaction did not begin, but the lock obtained by the failed statement was still valid and was not released. Failed statements can be found from the performance_schema.events_statements_current table.

This is described in the official manual as follows:

If the server acquires metadata locks for a statement that's syntactically valid but fails during execution, it does not Release the locks early. Lock release is still deferred to the end of the transaction because the failed statement are written to the binary log and The locks protect log consistency.

In other words, except for syntax errors, the locks acquired by other error statements are still not released until the transaction commits or rolls back. Because the failed statement is written to the binary log and the locks protect log consistency But the reason for explaining this behavior is difficult to understand because the wrong statement simply does not will be logged to a binary log.

Workaround: find its SID through Performance_schema.events_statements_current and kill the session. You can also kill the session where the DDL is located.

In short, ALTER TABLE's statement is very dangerous (in fact, his danger is the result of uncommitted things or long transactions), it is better to confirm before the operation of the table does not have any in-progress operation, no uncommitted transactions, and no explicit transaction error statements. If there is an ALTER TABLE maintenance task, running unattended, it is best to set the time-out by lock_wait_timeout and avoid long metedata lock waits.

Waiting for table metadata lock problem in-depth analysis I believe many msyql DBAs are having problems with locks, and the metadata LOCK:DDL statement introduced in MySQL 5.5.3 has broken the isolation level of the transaction.
Then there will be classmates asked, why before MySQL 5.5.3 rarely encountered such a lock? Reason is
Before the 5.5.3 version, MySQL transaction is for table structure metadata ( Metadata) The lock is statement (statement) Granularity: That is, the table structure can be updated by other sessions after the execution of the statement, regardless of whether the transaction is complete or not.
When Metadata lock is introduced, the locking of Table structure metadata (Metadata) becomes Transaction (transaction) granularity, that is metadata Lock is released only at the end of the transaction。

How did that happen?

The program or script explicitly opens the transaction (Start transaction), and the query statement within the transaction (including select) consumes the metadata lock (profile:opening tables stage) of the related table. All subsequent DDL operation statements are blocked because metadata lock is not available. (after MySQL 5.6 version has optimized) the official manual see: http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

We will explore this issue in the following case to solve the problem:

Case

The business executes a simple ALTER TABLE operation, adds a field, a very common SQL, and the table is small, the amount of data is very small, the execution consumes hundreds of seconds does not respond (the scene forgets)

Add a test diagram

From the diagram you can see that the business execution statement encountered metadata lock.

1. analysis of instances of MySQL

1.1 mysql> show Processlist;

There is no other operating process except for a waiting for table , all of which are the sleep process. Do you feel strange at this time? Why is there no other process that locks the table and causes the DDL statement to stay stuck? We went on to analyze.

1.2 See if the table is too large mysql> Show table status like ' Tbl_xx ' \g

Figure 1.2

See the table is very small, there is no due to the large amount of data caused by the slow update problem;

1.3 Viewing engine status mysql> show engine InnoDB status \g

The amount of data is too large, a screen is not finished, do not look.

Since a few more direct methods can not find the reason, it can only be more in-depth check, I intend to check the data dictionary (Information_schema,performance_schema):

1.4, find the current pending transaction:

Mysql> SELECT * from Performance_schema. events_waits_current;

Empty Set (0.03 sec)

Show empty.

Find the event table (events) in Information_schema , the lock waiting Table (innodb_lock_waits), INNODB the currently occurring lock (innodb_ LOCKS) did not see the exception (this is not the map).

1.5 Finding transactions

Since the cause of the lock is that the transaction is not committed, we should go to find out if there is currently a transaction running (runing Note: Because the transaction is always runing state, which is why I did not find the various locks before the reason)

Mysql> select * from Information_schema.innodb_trx;

(This picture is lost again) but there is a big discovery: a trx_mysql_thread_id:275255348 is from trx_started:2015-12-03 14:58:45 Always in the runing state.

Now that we've found the ID, we'll go back to using show Processlist to find the ID:

Did you find out that the ID is always the sleep state. It is difficult to see that the process has opened this table (you can view the currently open table through show open tables).

Workaround: ask for the script to develop this point, operation. After confirmation through the background MySQL directly kill the process, the business of the alter operation is completed instantaneously.

Attached: Welcome everyone to discuss the research

Why MySQL appears waiting for table metadata lock and how to fix it