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

Thread 1 、 begin

Update the table; no commit, no rollback operation

Thread2 and create index are on this table.

At this time, the client timeout is interrupted.

Connect session query again this table is blocked and cannot be queried

Thread3, query select * from test

Root@localhost: yaochong 17:08:27 > select id,user,host,db,command,time,state,info from information_schema.processlist where user' system user' and info not like'% system user%' + -- + | id | user | host | db | command | time | state | info | +- -+- -+ | 10161 | root | localhost | yaochong | Query | 3386 | Waiting for table metadata lock | select * from test | | 10092 | root | localhost | yaochong | Query | 6375 | Waiting for table metadata lock | alter table test add key (name) ALGORITHM=INPLACE LOCK=NONE | +-- -- + 2 rows in set (0.00 sec)

For the reasons, please refer to the read-write lock mutual exclusion of MDL as follows

Why does D block after C waits for the lock? In fact, there is no clear explanation here. Because if you understand it in terms of concurrency

Cminute D should be of the same level, and it is possible to get the lock. However, C read-write locks are mutually exclusive, while D reads are not mutually exclusive, which is contrary to the above picture.

Just, check it out.

(thank you for the article provided by YFL.)

The first is the concept of MDL (metaData Lock). Metadata lock is the lock of server layer and table level lock, which is mainly used to isolate the interference between DML (Data Manipulation Language, data manipulation language, such as select) and DDL (Data Definition Language, data definition language, such as adding a column to the header). MDL locks are applied for every time a DML or DDL statement is executed. MDL read locks are required for DML operations, and MDL write locks are required for DDL operations. (MDL locking process is automatically controlled by the system and cannot be intervened directly. Read sharing, read-write mutual exclusion, write-write mutual exclusion)

The operation of applying for MDL locks forms a queue in which the acquisition of write locks takes precedence over read locks. Once a write lock wait occurs, not only the current operation will be blocked, but also all subsequent operations on the table will be blocked. Once the transaction has applied for the MDL lock, the lock will not be released until the transaction finishes execution. (in a special case, if the transaction contains a DDL operation, mysql implicitly commits the commit before the execution of the DDL operation statement to ensure that the DDL statement operation exists as a separate transaction and to ensure the release of the metadata exclusive lock. For example, if the statement of id 44 is changed, the transaction (autocommit=1) will be committed as soon as the alter statement is completed, and the subsequent select is outside this transaction, and the read lock will not be held after its execution.)

This explains why session D does not work after session C is blocked.

In short, MDL locks are mutually exclusive, and select also has to apply for MDL read locks, which is a bit disgusting.

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.