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) MySQL 由于MDL讀鎖select被阻塞
申請(qǐng)MDL鎖的操作會(huì)形成一個(gè)隊(duì)列， 隊(duì)列中寫(xiě)鎖獲取優(yōu)先級(jí)高于讀鎖 。一旦出現(xiàn)寫(xiě)鎖等待，不但當(dāng)前操作會(huì)被阻塞，同時(shí)還會(huì)阻塞后續(xù)該表的所有操作。事務(wù)一旦申請(qǐng)到MDL鎖后，直到事務(wù)執(zhí)行完才會(huì)將鎖釋放。（這里有種特殊情況如果事務(wù)中包含DDL操作，mysql會(huì)在DDL操作語(yǔ)句執(zhí)行前，隱式提交commit，以保證該DDL語(yǔ)句操作作為一個(gè)單獨(dú)的事務(wù)存在，同時(shí)也保證元數(shù)據(jù)排他鎖的釋放，例如id 44的語(yǔ)句改為<begin;alter table testok add z varchar(10) not Null;select * from testok;>，此時(shí)一旦alter語(yǔ)句執(zhí)行完成會(huì)馬上提交事務(wù)（autocommit=1），后面的select就在本次事務(wù)之外，其執(zhí)行完成后不會(huì)持有讀鎖）
