Waiting for table metadata lock 这个mdl锁,我们最常见,这篇先拿它开刀。
1 长查询/mysqldump 阻塞DDL
session1 执行大的查询时候(select * from sbtest10 order by k desc;).
session2提交了一个对session中一张表的alter操作(alter table sbtest10 add column dd1111d int not null;).这时候session2会一直被阻塞,直到session1的查询完成。
session2未完成的这个时间段内, 执行show processlist 就会显示 Waiting for table metadata lock
session3 这时候又发起了一个非常简单的查询(select * from sbtest10 limit 3;),可以看到也是被阻塞的状态的。
> show full processlist;
+--------+---------+------------+--------------------+---------+------+---------------------------------+------------------------------------------------------+-----------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent |
+--------+---------+------------+--------------------+---------+------+---------------------------------+------------------------------------------------------+-----------+
| 115950 | root | localhost | performance_schema | Query | 0 | starting | show full processlist | 0 |
| 117002 | root | localhost | test | Query | 2 | Waiting for table metadata lock | alter table sbtest10 add column dd1111d int not null | 0 |
| 117027 | root | localhost | test | Query | 2 | executing | select * from sbtest10 order by k desc | 0 |
| 115950 | root | localhost | test | Query | 6 | Waiting for table metadata lock | select * from sbtest10 limit 3 | 0 |
+--------+---------+------------+--------------------+---------+------+---------------------------------+------------------------------------------------------+-----------+
会话1先执行select , 会话2后执行alter。
在会话1执行完毕前,会话2拿不到MDL锁,从表格上面来看,主要阻塞在rename阶段。会话1在执行完毕后,会话2拿到MDL锁,变为rename table状态,这个操作持续时间非常短,会话1再次执行查询,当会话2执行完后,此时会话1正常执行。这说明对于MDL锁而言,select会阻塞alter,而alter不会阻塞select。在rename的瞬间,alter是会阻塞select的.
当执行select语句时,只要select语句在获取MDL_SHARED_READ锁之前,alter没有执行到rename阶段,那么select获取MDL_SHARED_READ锁成功,后续有alter执行到rename阶段,请求MDL_EXCLUSIVE锁时,就会被阻塞。rename阶段会持有MDL_EXCLUSIVE锁,但由于这个过程时间非常短(大头都在copy数据阶段),并且是alter的最后一个阶段,所以基本感觉不到alter会阻塞select语句。由于MDL锁在事务提交后才释放,若线上存在大查询,或者存在未提交的事务,则会出现ddl卡住的现象。这里要注意的是,ddl卡住后,若再有select查询或DML进来,都会被堵住,就会出现thread running飙高的情况。
#### 注意: 对于MDL锁而言,select会阻塞alter,而alter加锁后DDL运行起来后会立马释放掉锁(这个加/释放锁的时间开销非常短),这时候是不会阻塞select。
2 未提交的事务阻塞 DDL
此时执行 show processlist; 如下:+-----+------+-----------+---------+---------+------+---------------------------------&#