Waiting for table metadata lock

• Interpretation of WFTML in MySQL Manual (excerpt)

MySQL uses metadata locking to manage concurrent access to database objects and to ensure data consistency. Metadata locking applies not just to tables, but also to schemas and stored programs (procedures, functions, triggers, and scheduled events)
To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted explicitly or implicitly started transaction in another session. The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends. A metadata lock on a table prevents changes to the table's structure. This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.

The problem I encountered is actually more exotic. Why? Because the table I'm dealing with is another table. I define it as table A, while table B has a long-term transaction impact. The two tables are not directly related and have no foreign key relationship. They are very independent. But when DDL is applied to table A, the MDL lock is generated.

Queries on transactions

SELECT * FROM information_schema.innodb_trx;

As you can see, there is a very long transaction running (more than five hours from the fault), in fact, this is a bug program, but because the data taken is less, 50 tables out of millions run once, so the transaction has always existed, in fact, it is problematic. Since the transaction is not painful, kill it directly.
Need attention

• Determine the corresponding pid, that is, the corresponding trx_mysql_thread_id.
• Determine in the process list that this SQL is generated by that account and kill it using that account (without a super privileged account)

summary

In this case, it is not the same table, and the official website said that the MDL locks for the same table will actually produce this situation, and the failure situation, bold speculation should be raised to the metadata level of the database locks, that is, the first sentence in the official website manual.

MySQL uses metadata locking to manage concurrent access to database objects and to ensure data consistency. Metadata locking applies not just to tables, ***but also to schemas and stored programs*** (procedures, functions, triggers, and scheduled events)

but also to schemas and stored programs: Reverse from this statement should be the most fundamental cause of this failure, Library-level MDL locks.
Approximate solutions

1. Find out if there are overlong transactions
2. Determine whether transactions have associated libraries and accounts
3. Log on to the corresponding account and kill it.