记一次由于DDL语句导致的mysql满CPU线上事故

事情是这样子的，由于公司要推行降本增效，尽量使得服务器能满负载的去工作，我负责的项目由于对数据库的使用比较轻度，所以就降低配置去使用。而一个新的需求，需要稍微复杂一点的业务逻辑，所以需要对数据库增加一个字段，且增加一个索引，也就是做一点DDL语句的操作，但是由于表的数据量也不小(最大的一张表差不多800多万行，最少也有几百万条数据)，所以在此之前，对大表加字段，加索引做了一个比较深入的学习。

在极客时间的mysql45讲中有这么一章：全局锁和表锁，给小表加个字段怎么这么难？ 这一篇正好讲述到我遇到的问题。其中讲到了表锁和MDL锁（Meta Data Lock元数据锁，当做增删改查的时候获取的是MDL读锁，当做表结构变更的时候获取的是MDL写锁），又提到了一个概念叫做Online DDL的操作，该操作从mysql5.5后引入，大意是：mysql做DDL操作的时候会去获取MDL写锁，如果获取到MDL写锁后，会暂时将MDL写锁退化成MDL读锁，然后其他的业务增删改查的操作就不会被阻塞住，mysql会真正做DDL操作，做完操作后再升级成MDL写锁，然后释放MDL写锁。其中最耗时的DDL操作时是MDL读锁，所以不会阻塞到业务的增删改查。看到这里我就放心的提单申请做DDL操作了，但是事故就紧接着而来，数据库的CPU立马暴涨到100%，并且有不少报错提示获取不到锁，但是提示是trying to get lock but found deadlock，这个提示不太准确，其实就是获取MDL读锁失败了。由于项目中有不少批量更新的语句，但是事务执行的条数比较多，一般批量的sql最多可以达到200条，也就导致了大事务的存在，进而导致了Alter做DDL操作的时候需要获取到MDL写锁，这时候阻塞住，而其他的增删改查的操作虽然是获取MDL读锁，但是由于被前面Alter语句获取的MDL写锁阻塞住，导致业务无法正常执行，进而导致一系列的数据库错误。这里借用mysql45讲中这一章节的一张图来表示这个过程：

这个图表示了sessionB是可以正常读写，但是SessionC由于获取的是MDL写锁，阻塞了后面sessionD的MDL读锁的操作，进而导致该表不可读写的状态。理解了这个过程之后就好办了，下次执行Alter语句之前应该首先查看数据库中是否存在正在执行的大事务，若存在，则可以等待执行完毕再执行，然后执行Alter语句的时候加个超时时间的限制，在一定的时间内没有获取到MDL写锁后自动放弃执行该操作，等待重新执行。

本次事故发生的主要因素就是因为我没有彻底深入理解MDL锁的概念，进而忽略了取锁也有可能发生阻塞的情况。第二个原因就是此次ddl语句是运维设置的定时脚本自动执行的，所以没有人工处理的那么迅速，定时脚本也是我提的工单中设置的时间设置错误的原因，才导致定时脚本直接执行了。

题外话：下面展示一下onlineDDL的流程，本次事故的主要原因就是DDL操作阻塞在了第一步，也就是获取MDL写锁的过程中，也就自然无法发挥OnlineDDL的优势了。

Online DDL的过程是这样的： 1. 拿MDL写锁 2. 降级成MDL读锁 3. 真正做DDL 4. 升级成MDL写锁 5. 释放MDL锁 1、2、4、5如果没有锁冲突，执行时间非常短。第3步占用了DDL绝大部分时间，这期间这个表可以正常读写数据，是因此称为“online ” 我们文中的例子，是在第一步就堵住了