一例MDL锁等待引起的表锁等待导致的应用连接池耗尽问题排查

目录

现象

分析、紧急处理

深度调研

总结&改进&规避

---

• 现象

1. 11:16分 线上服务依赖方 大量504告警 
2. 业务流量看不到
3. SQL监控查询时间很长 大量慢查询 很简单的SQL查询也很耗时 
4. 其他依赖该库的业务应用是正常的

• 分析、紧急处理

1. cat监控已经发现没有流量进来了，似乎所有请求线程 均被卡主。
2. 观察服务无gc，但是httpthread（50->600左右） 和 activateThread陡增至最大值（->2000左右） 典型的http线程卡柱，导致Tomcat线程打满，请求进不来。
3. 
4. 通过cat SQL 耗时监控 基本锁定问题出在SQL执行上。
5. 
6. 因为其它依赖该库的业务方是正常的 断定是自己业务依赖的表存在问题
7. 11:15左右有同事提了两个修改同一个表的DDL（累计4条语句 加了4了字段）工单，反馈是工单执行卡主了，无法回退工单。
8. 紧急处理 先重启业务服务。
9. 服务11:30分重启完，业务逐渐恢复，工单也执行完了。

• 深度调研

1. 初步怀疑是DDL中表锁导致的 业务连接池占用耗尽，但是流程工具使用的是gh-ost 支持在线online DDL的工具 理论只会在rename阶段 短暂锁表。但是通过和DBA反复确认后，DBA翻看binlog日志 查找到流程工具并未使用gh-ost ，流程工具在osc_min_table_size <100 m 时会直接执行DDL 甚至连MYSQL5.6之后支持的online DDL也不用 （此处是问题点），但是DDL要操作的表只有2w行数据，即使是直接alter 也不至于DDL 15分钟才执行完 锁表时间十几分钟。
2. MDL锁 （metaData lock）又称元数据锁，alter时是要获取MDL锁，MDL是排他锁 比表锁的（表写锁、表读锁）粒度更高，一但尝试获取后，没有设置锁等待时间的话，后面所有的读写请求都会被block，表现就是尝试获取MDL锁。然而MDL又要等待一个持有该表的大事务（执行时间长达50分钟的事务）的读锁，导致等待了十几分钟 直至重启业务后，该事物断掉才回复。MDL锁会阻塞一切查询 写入 所有涉及到该MDL锁的表读写 均被BLOCK，业务c3p0链接池耗尽导致。
3. 后面使用测试表 重现该场景 先开启A事务 不提交事务 ，B session 此刻执行alter rename等 ，C session 去查询该表 此刻B C全部会被block。至此 问题定位。
4. 再谈下如果使用gh-ost是否可以避免，仔细看了下gh-ost执行细节，gh-ost在cut-over切换表的时候会设置seesion lock 的锁等待时间 1s ，所以不会存在长时间MDL锁表的问题

• 总结&改进&规避

1. 杜绝大事务 安全隐患 非常多。
2. 杜绝慢查询 
3. DBA流程工具完善，使用online DDL 增加session 锁获取等待时间等