When MySQL performs DDL operations such as alter table, sometimes a waiting scenario of Waiting for table metadata lock appears. Moreover, once the operation of alter table TableA stalls in the Waiting for table metadata lock state, any subsequent operations on TableA (including reading) cannot be performed, because they will also enter the Waiting for table metadata lock lock during the opening tables stage. Waiting in the queue. If such a lock waiting queue appears in the core table of the production environment, it will cause catastrophic consequences.
The reason that causes the alter table to produce the Waiting for table metadata lock is actually very simple, generally the following simple scenarios:
Scenario 1: Long transaction runs, blocking DDL, and then blocking all subsequent operations of the same table
Through show processlist, you can see that there are ongoing operations (including reading) on TableA. At this time, the alter table statement cannot obtain the metadata exclusive lock and will wait.
This is the most basic situation, which does not conflict with the online ddl in mysql 5.6. Generally during the operation of the alter table (see the figure below), the metadata exclusive lock will be acquired in the after create step. When the altering table is in the process (usually the most time-consuming step), the reading and writing of the table can be normal Proceeding, this is the performance of online ddl, and will not block writes during the entire alter table process as before. (Of course, not all types of alter operations can be online. For details, please refer to the official manual: http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html )
Processing method: kill the session where the DDL is located.
Scenario 2: Uncommitted things, blocking DDL, and then blocking all subsequent operations of the same table
You can’t see any operations on TableA through show processlist, but there are actually uncommitted transactions, which can be viewed in information_schema.innodb_trx . Before the transaction is completed, the lock on TableA will not be released, and the alter table also cannot obtain the exclusive lock of metadata.
Processing method: through select * from information_schema.innodb_trx \G, find the sid of the uncommitted thing, then kill it and let it roll back.
Scene three:
You can’t see any operations on TableA through show processlist, and there are no ongoing transactions in information_schema.innodb_trx. This is probably because in an explicit transaction, a failed operation was performed on TableA (for example, a non-existent field was queried). At this time, the transaction did not start, but the lock acquired by the failed statement was still valid and was not released.. The failed statement can be found in the performance_schema.events_statements_current table.
The description of this in the official manual is as follows:
If the server acquires metadata locks for a statement that is 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 is written to the binary log and the locks protect log consistency.
In other words, except for syntax errors, the locks acquired by other incorrect statements will still not be released before the transaction is committed or rolled back. because the failed statement is written to the binary log and the locks protect log consistency But it is difficult to understand the reason for this behavior because the wrong statement will not be recorded to the binary log at all.
Processing method: find its sid through performance_schema.events_statements_current, kill the session. You can also kill the session where the DDL is located.
In short, the statement of alter table is very dangerous (in fact, his danger is caused by uncommitted things or long transactions). Before the operation, it is best to confirm that there are no ongoing operations on the table to be operated, no uncommitted transactions, There is also no error statement in an explicit transaction. If there is an alter table maintenance task and it is run when unsupervised, it is best to set the timeout time through lock_wait_timeout to avoid long waits for the metedata lock.
lock_wait_timeout represents the timeout (in seconds) for acquiring the metadata lock, and the allowed value range is 1 to 31536000 (1 year). The default value is 31536000. See https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_lock_wait_timeout for details. The default value is one year! ! !
Adjust it to 30 minutes
set session lock_wait_timeout = 1800; set global lock_wait_timeout = 1800;