MySQL Metadata lock lock

In many cases, many problems can be avoided or solved in theory or management, but when it comes to reality because management or coordination or the implementation of the standard is not enough, there will be a variety of problems that should not arise, usually in the production environment does not appear, But the reality is that no matter in any part, you have to find a solution, many times the reason is part, the preventive action is also a part, but the solution is also necessary, because it is impossible to tell all the developers that you do as I said, there is no problem, because there will always be negligence or neglect.

The first two days, the test environment upgrade script, ran halfway to the lock timeout, several times after the test to allow assistance to solve. Looked under, is a truncate operation in the acquisition of metadata lock, the environment is very complex, there are more than 10 applications connected, some are directly from the simulation line, testing, development also do not want to stop the application, or toss too much trouble. The default lock time-out for this environment is 60s, and a non-autocommit is set, and a query is queried by the application of the TRUNCATE table (which is known later) because the query also needs to metadata lock, unless commit or rollback. In the process of solving, the biggest technical problem is that MySQL native could not see the metadata lock blocking information, which resulted in the time-to-application confirmation (5.7 Metadata_locks has always been an impression, But found that 5.6 did not), but development does not know exactly which applications will query or DML the table. Finally, set the autocommit to 1 temporarily, set the lock timeout to 3,600 seconds, and upgrade the past after two minutes.

Beginning with MySQL 5.7, the metadata lock holder and the blocker can be queried in the following ways:

UPDATE performance_schema.setup_consumers SET ENABLED = ' YES ' WHERE NAME = ' global_instrumentation ';

UPDATE performance_schema.setup_instruments SET ENABLED = ' YES ' WHERE NAME = ' wait/lock/metadata/sql/mdl ';

SELECT ml.*,thr.* from ' metadata_locks ' ml,threads thr
WHERE ml. ' owner_thread_id ' = thr. ' thread_id ';

Later, MySQL 5.6 Someone has developed a mysql-plugin-mdl-info plug-in, you can view similar information.

MySQL Metadata lock lock