MySQL表锁阻塞读写问题案例分享

一、问题背景

6月底听朋友介绍，有款DBdoctor数据库性能诊断的工具可以免费使用，在他们官网下载以后，对接到了公司的一个业务MySQL实例上进行试用。

差不多两周后，回头想起来这款工具，登上它的管理后台看了看，还真发现了一个潜在问题：查看性能数据的统计时发现，这个实例上存在有大量的Waiting for table metadata lock异常报出，该事件表示部分表在持续一段时间内无法进行读写。

根据DBdoctor的操作手册的介绍，通过Average Action Session（AAS）曲线图可以查看数据库活跃会话。

首先查看24小时的活跃会话情况，如下图橘色所示（7月13日16:00～7月14日16:00），发现全天都有Waiting for table metadata lock出现，基本每小时一次。

然后，选取了两个比较有代表的时间段，一个是7月14日零点前后的，如下图绿色区域（从图例上看到，绿色区域代表的是Waiting for table metadata lock）所示。说明零点时段的锁事件持续了20分钟左右。

• 

又查看了一个其他时段的，如下图蓝色区域（Waiting for table metadata lock）所示，发现非零点时段锁事件持续时间不到1分钟。

二、问题分析过程

1. 由于凌晨时间段MetaData Lock持续时间最久，首先该段时间场景入手，同时怀疑凌晨可能有备份任务，通过运维同事确认该库备份时间段0:15-0:45，与Lock时间吻合，猜测跟备份有关，但是需要找出哪个SQL受备份导致了其他业务SQL的MetaData Lock，比较常见的如DDL。
2. 通过DBdoctor查看凌晨Lock时段AAS活跃会话情况，观察其间是否有哪些可疑SQL

3. 如上图，通过DBdoctor AAS，可以看到

（1）等待Metadata Lock的SQL（上图绿色）有两类，一类是业务查询语句，一类是Lock Tables语句。

（2）同时发现有全表SELECT的备份语句：SELECT /*!40001 SQL_NO_CACHE */ * FROM `ad_rule`

（3）业务查询语句、Lock语句、备份语句都涉及到ad_rule表或者ad_allocation表。

4. 问题直接原因：

Lock Tables语句受备份影响，造成了业务阻塞。

三、问题根因定位

与业务开发同事确认上述锁表SQL是特意设计的，每小时锁一次表，在写数据期间不允许读，预期1-2分钟完成写入并解锁；但是备份导致不可读写的时间变长，超出了预期时间。

分析引发MedataLock 时间变长的根因：

• mysqldump期间，从表中select全表数据，为表添加Metadata共享锁，共享锁不影响正常业务读写操作，但是加锁时间较长。
• 此时业务下发LOCK TABLES xx Write语句，该SQL需要Metadata排他锁，需要等待该表备份完成后才能拿到排他锁，同时也会阻塞其他SQL申请新的metadta锁。
• 此时新的业务SQL发出该表的读写请求，都不再允许申请Metadata共享锁，进而不能操作该表，需等待LOCK TABLES XX Write语句完成，释放排他锁。

锁等待关系总结：

业务SQL需要MD共享锁，需等待LOCK TABLES xx Write执行完成后释放MD排他锁；

LOCK TABLES xx Write需要MD排他锁，需等待该表备份完成后释放MD共享锁。

四、优化建议

• • • 开发优化建议：考虑凌晨时间段锁问题对的业务的影响大小，考虑整张表加写锁的是否必须，表加写锁可能造成阻塞业务、导致死锁的问题。
    • 运维优化建议：与开发确认锁表时间是否固定，不会因服务重启改变；在锁表时间固定的前提下可以通过修改备份时间来缓解问题。

回顾这个问题的发现和解决过程，DBdoctor这个工具对分析的提效还是挺有帮助的，也帮助业务提前发现了一个潜在问题。

• • • 能直观看出Metadata Lock的分布情况，无DBdoctor时无法统计metadatalock出现规律。
    • 方便排查可疑SQL，无DBdoctor时需要从慢查询SQL中检索与梳理。此时的slow query log里已经是满屏都是慢SQL了，会干扰我们查找真正导致慢的根因SQL，而DBdoctor可以快速展示出消耗最大的SQL来，这一功能还是很便利的。