Waiting for table metadata lock

This blog is prepared in two parts for a detailed introduction to MySQL (based on MySQL 5.6) in metadata lock,

I. Description of metadata locking in official documentation (5.6)

8.11.4 Metadata Lockingmysql uses Metadata locking to manage concurrent access to database objects and to ensure data cons Istency.  Metadata locking applies not just to tables, but also to schemas and stored programs (procedures, functions, triggers, and  Scheduled events). <span style= "color: #330000;" >>>mysql uses metadata locking to ensure data consistency when database objects are accessed concurrently. Metadata locking is not only used to protect tables, but also to protect other objects in the database, such as schemas,procedures,functions,triggers,scheduled events. </span>metadata locking does involve some overhead, which increases as query volume increases.  Metadata contention increases the more this multiple queries attempt to access the same objects. <span style= "color: #330000;" >>> as the number of visits increases, so does the overhead associated with metadata locking. The more queries access the same object, the greater the likelihood that the metadata lock contention will occur. </span><span style= "color: #ff0000;" ></span>metadata locking is not a replacement for the table definition cache, and its mutexes and locks differ fr Om the Lock_open mutex. The following discussion provides SOME information about how metadata locking works. <span style= "color: #330000;" >>>metedata Locking cannot replace table definition cache. The following discusses how metadata locking works. </span>to ensure transaction serializability, the server must not permit one session to perform a data definition LA Nguage (DDL) statement on a table this is used in an uncompleted explicitly or implicitly started transaction in another s Ession. The server achieves the acquiring metadata locks on tables used within a transaction and deferring release of those lo Cks until the transaction ends. A metadata lock on a table prevents changes to the table ' s structure. This locking approach have the implication that a table that's being used by a transaction within one session cannot be us  Ed in DDL statements by other sessions until the transaction ends. <span style= "color: #330000;" >>> to ensure that things are serialized (on the same object), if an object is being used (shown or hidden) by an unfinished thing, then no other session can be allowed to perform DDL operations on it. MySQL implements serial access to objects by adding metadata lock to objects in the process of things (metadata lock endsBe released when the If a table is added with metadata lock, the DDL operation on the table is blocked (blocking the DDL, as the name lock for "metadata lock" says). </span>this principle applies not only to transactional tables and also to nontransactional tables. Suppose a session begins a transaction that uses transactional table T and nontransactional table NT as follows: < Span style= "color: #ff0000;" > </span><span style= "color: #330000;" >>> The metadata lock principle is not only for the things table (if the InnoDB table), but also for non-object tables (such as MyISAM tables). Suppose a session opens the following things (things that use the things table T and the non-object table NT) </span>start TRANSACTION; SELECT * from T; SELECT * from NT; The server holds metadata locks on both T and NT until the transaction ends. If Another session attempts a DDL or write lock operation on either table, it blocks until metadata lock release at Transa Ction end. For example, a second session blocks if it attempts any of these operations: <span style= "color: #330000;" >>> in the example above, MySQL adds metadata lock to both the things table T and the non-object table NT until the end of the thing. If the above thing is not finished, do the following in another session (DDL for T and NT), and you will find that the associated DDL operation is blocked </span>drop TABLE T ALTER table t ...;D rop table NT; ALTER TABLE nt ...; LOCK TABLE t ... Write;if the server acquires metadata locks for a statement that's syntactically valid but fails during execution, it doe s not release the locks early. <span style= "color: #ff0000;"  >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</span>.>> If a syntactically correct statement fails during execution, the metadata lock added to the table is not immediately released. It is not released until the end of the thing (I'll give an example later). The red part of the above table does not understand what it means, how to execute the failed statement is recorded in the Binlog? In autocommit mode, each statement was in effect a complete transaction, so metadata locks acquired for the statement are H  Eld only to the end of the statement. >> in autocommit mode, each statement is actually a complete thing, so metadata lock is added at the beginning of the statement and is released at the end of the statement metadata locks acquired during a PREPARE statement is released once the statement has been prepared, even if preparation occurs within a Multiple-statemen  T transaction. >>prepare statement obtained by metadata lock in SQL has been prePare will be released, even in a thing composed of multiple SQL. Before MySQL 5.5, when a transaction acquired the equivalent of a metadata lock for a table used within a statement, it re Leased the lock at the end of the statement. This approach had the disadvantage, if a DDL statement occurred for a table is being used by another session in  An active transaction, statements could is written to the binary log in the wrong order. >> before MySQL 5.5, the table was added a lock that resembles metadata lock during SQL execution, and the lock fails when the statement ends (it is now metadata lock in the whole thing, but in autocommit mode, is also locked during the execution of the statement, released after execution, because a statement is a thing). This approach has one drawback that may cause the Binlog record order to be inconsistent with the actual execution order.

Two. scenarios where waiting for table metadata lock appears in MySQL

At show Processlist we may have seen a statement in the waiting for table metadata lock state, and now let's analyze what happens when waiting for table metadata lock

Scenario 1:

When a table is used by an unfinished object (including Select) and the DDL operation is performed on the table in another session, the DDL operation is blocked because the exclusive metadata lock cannot be obtained, at which point the show Processlist view DDL operation is waiting For table metadata lock state. At this point we want to know what is blocking my DDL statement, and by the following three steps you can find out what is blocking your DDL operation:

1) Show Processlist

If the statement that caused the DDL statement to be blocked is executing, you can find such a statement through show processlist (view the operation of the table involved in the DDL statement).

2) Information_schema.innodb_trx

View the InnoDB things table Information_schema.innodb_trx, if you are in a Display object (using start transaction or begin), the operation for the related table is completed (so show Processlist cannot see the action), but the thing is not committed and the DDL operation is blocked in another session when the table is being DDL-free because the exclusive metadata lock cannot be obtained. Now you can see the active thing by looking at the Information_schema.innodb_trx table.

3) Performance_schema.events_statements_current

If a table's DDL operation is blocked because it cannot get exclusive metadata lock, and the methods in view 1 and 2 are not found, then there is also the possibility of a failed operation on table A in an explicit transaction (for example, querying a nonexistent field). At this point the transaction does not start, but the lock acquired by the failed statement is still valid. Failed statements can be found from the Performance_schema.events_statements_current table. The Events_statements_current table records the most recently executed SQL of the database thread.

Scenario 2:

We know that metadata lock is used to protect the data structure, and if Table A is being used by an active object, another session will block the DDL operation on table A. When table A's DDL operation is blocked because it cannot obtain exclusive metadata lock (waiting for table metadata lock), any subsequent operations on table A will be blocked, including a select operation with a status of waiting for table Metadata lock)

Scenario 3:

InPlace (of course, 5.6 starts with a majority of DDL operations that support ONLINE_DDL) The DDL that is executed against table A, blocking all DML operations for table A during the execution of the DDL statement. In the ONLINE_DDL process is not the entire statement execution process to table a exclusive metadata lock, in after table (ONLINE_DDL specific procedure see), will acquire the exclusive lock metadata lock, when carried out to altering Table (which is also the most time-consuming step), all DML can be performed normally.

Here is the process of ONLINE_DDL (profile)

+--------------------------------+----------+| Status | Duration |+--------------------------------+----------+| Starting | 0.000119 | | Checking Permissions | 0.000013 | | Checking Permissions | 0.000011 | | init | 0.000007 | | Opening Tables | 0.007743 | | Setup | 0.000055 | | Creating Table | 0.004871 | | After Create | 0.000152 | | System Lock | 0.000018 | | Preparing for ALTER TABLE | 0.009417 | | Altering table | 0.064343 | | Committing ALTER TABLE to Stor | 0.024200 | | End | 0.000025 | | Query End | 0.002839 | | Closing Tables | 0.000017 | | Freeing items | 0.000040 | | Cleaning Up | 0.000025 |+--------------------------------+----------+

Waiting for table metadata lock