Shulou(Shulou.com)06/01 Report--

Find uncommitted MySQL threads / transactions:

SELECT * from information_schema.processlist; can see which SQL thread ID above (thread 378 in the figure below is the culprit for MDL locks)

SELECT trx_id,trx_state,trx_started,trx_mysql_thread_id,CURRENT_TIMESTAMP-trx_started AS RUN_TIME from information_schema.innodb_trx

Then kill can drop 378 threads.

Add:

Scenario 3:

You don't see any operations on TableA through show processlist, and there are no transactions in progress in information_schema.innodb_trx. This is probably because in an explicit transaction, a failed operation was performed on TableA (such as querying a field that does not exist), and the transaction did not start, but the lock acquired by the failed statement was still valid and not released. Failed statements can be found in the performance_schema.events_statements_ current table.

This is explained in the official manual 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.

That is, except for syntax errors, locks acquired by other error statements are still not released until the transaction is committed or rolled back. Because the failed statement is written to the binary log and the locks protect log consistency but the reason for this behavior is hard to understand because incorrect statements are not logged to the binary log at all.

How to deal with it: find its sid through performance_schema.events_statements_current, and kill drop the session. You can also kill the session where DDL is located.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.