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被阻塞
申请MDL锁的操作会形成一个队列， 队列中写锁获取优先级高于读锁 。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作。事务一旦申请到MDL锁后，直到事务执行完才会将锁释放。（这里有种特殊情况如果事务中包含DDL操作，mysql会在DDL操作语句执行前，隐式提交commit，以保证该DDL语句操作作为一个单独的事务存在，同时也保证元数据排他锁的释放，例如id 44的语句改为<begin;alter table testok add z varchar(10) not Null;select * from testok;>，此时一旦alter语句执行完成会马上提交事务（autocommit=1），后面的select就在本次事务之外，其执行完成后不会持有读锁）
