mysql大量的waiting for table level lock怎么办

导读

遇到经典的MDL等待场景时怎么分析和处理。 本文节选自叶金荣有赞《乱弹MySQL》专栏。

MySQL从5.5版本开始，新增元数据锁，也就是metadata lock，简称MDL锁。

MDL锁的主要目是保护表元数据并行请求过程中不被修改，例如会话1在执行SELECT查询数据，会话2要新增一列，这时第二个会话会被阻塞，以免造成会话1前后看到的表结构不一致。

当然了，MDL后来被扩展了，并不只是针对表（TABLE）对象，也包含库（SCHEMA）、表空间（TABLESPACE）、存储程序（过程、函数、触发器、定时器）等对象，也受到MDL的保护。此外，MDL锁也会阻塞全局 COMMIT 行为，比如加了FTWRL（FLUSH TABLE WITH READ LOCK）后，就会持有一个全局的 COMMIT 共享锁，此时会阻止其他会话的事务提交。

我们从 performance_schema.metadata_lock 就可以看到MDL的详细情况，从MySQL 5.7版本开始，还可以从 sys.schema_table_lock_waits 查看MDL阻塞等待的情况。要特别注意的是，MDL锁等待超时阈值由选项 lock_wait_timeout 控制，该选项默认值是 31536000秒，也就是 一年、一年、一年（重要的话重复三遍），建议调低，比如改成5-10分钟，建议最长不超过1小时（想想，这种MDL等待超过1小时还不报警的话，DBA也该下岗了吧）。

另外，想要在PFS（performance_schema）和 sys schema中能看到MDL详情的话，需要先启用相关的设置：（横屏观看）

[root@yejr.me]> use performance_schema;[root@yejr.me]> UPDATE setup_consumers    SET ENABLED = 'YES' WHERE NAME ='global_instrumentation';[root@yejr.me]> UPDATE setup_instruments    SET ENABLED = 'YES' WHERE NAME =‘wait/lock/metadata/sql/mdl';

然后就能查看当前是否有MDL锁了：（横屏观看）

# session1执行一个未结束的只读事务[root@yejr.me]> begin;