共计 3519 个字符，预计需要花费 9 分钟才能阅读完成。
本文由云 + 社区发表
一、问题是这样来的
2018 年某个周末，接到连续数据库的告警，告警信息如下：
二、苦逼的探索过程
1、总体的思路
看到 too many connection 的报错信息，基本上可以把问题定位在：
（1）机器负载飙升，导致 SQL 执行效率下降，导致连接推积
（2）业务访问量突增（或者有 SQL 注入现象），导致连接数打满
（3）出现“死锁”或者锁竞争严重，导致大量 SQL 堆积
2、排查过程
（1）机器的各项性能指标都显示正常，没有出现高负载现象，暂时先排除了这种原因
（2）查看监控信息，发现在连接数打满的时间点前并没有访问量突增的趋势，同时通过检查告警信息并没有发现有注入工单
（3）最后上到服务器上查看下 SQL 的执行情况
①查看 show full processlist；
大量的请求都是在“Waiting for table metadata lock”，可以分成三类请求：
Select 请求
Rename 请求
Sleep 请求②分析 Waiting for table metadata lock
一般来说常见的“Waiting for table metadata lock”会出现在 DDL 操作或者是有未提交的事务上，从 information_schema.processlist 表中，没有发现有 DDL 操作，而能够产生 MDL 锁的操作也只剩下 rename，但是根据 SQL 执行的状态，rename 操作也是在等待 MDL 锁，所以 rename 操作应该是被阻塞的操作，而不是产生 MDL 锁的操作。
接着我们来查看下死锁和事务的相关指标：
show engine innodb status; 中没有任何死锁的信息
information_schema.innodb_trx、information_schema.innodb_locks、information_schema.innodb_lock_waits 的也没有任何形式的锁信息。
现在基本又排除了显示的死锁问题，那是从 show full processlist 中也抓不出任何请求，这里就比较疑惑了，当看了下表的结构式，发现这个表是 myisam 引擎的，所以上面的两种统计信息里面没有任何值就可以解释了。
那么其实问题就集中在有未结束的事务上了，这里其实有一个误区，当时跟开发沟通存在未关闭的事务时，开发一直认为不可能，因为 myisam 表是不支持事务的，只有 innodb 支持事务。但是对于 MDL 锁来说，5.5 之后引入 MDL 事务级别的锁不论对 myisam 还是 innodb 都是生效的。
③查看未提交的事务
之后查看了下系统的事务自动提交的变量，autocommit 的值是 ON，那说明如果是事务未提交的话只可能是业务主动的开启一个事务，而没有 commit。
为了验证这个猜想，打开了 general log，在 log 中果然发现，业务在开启事务后，把 autocommit 的值设为 0 了，导致必须要显示的 commit 才能提交事务。
这时候我们反过头来看一下 host 为 10.49.84.70 的连接请求，由于 select 的执行速度很快，而且访问并不频繁，所以在抽样的 show processlist 中，状态值大部分时间是“Sleep”, 给问题的定位带来了一些迷惑性的干扰。接着我们 kill 掉了这个进程，果然推积的请求瞬间就执行完成了，也之间印证了刚刚上述推论。
2、问题解决
在与开发同学沟通过程中，开发同学说库中是 myisam 表所以不会主动开启事务，在代码里也没有设置 autocommit= 0 的代码，那么根本原因在哪？
当我们定位到这台服务器上的请求都是来自 python 的定时脚本，使用 python 操作 mysql 的时候，使用了其 pymysql 模块，但是在进行插入操作的时候，必须使用受到提交事务。Python 的 pymysql 模块默认是会设置 autocommit= 0 的。
让我们来对比一下其他同样使用 python 访问的正常连接请求，再断开前都会手动的 commit。
找到原因后有思考了下，是不是可以在建连后就设置 autocommit= 1 呢？这样对于之后新变更的 SQL 就不要再考虑到手动 commit 的事情了，可以通过在初始化连接池的时候，对每一个连接进行设置，即
三、延伸的一些思考
1、metadata lock
（1）MDL 简述
为了在并发环境下维护表元数据的数据一致性，在表上有活动事务（显式或隐式）的时候，不可以对元数据进行写入操作。因此从 MySQL5.5 版本开始引入了 MDL 锁（metadata lock），来保护表的元数据信息，用于解决或者保证 DDL 操作与 DML 操作之间的一致性。
对于引入 MDL，其主要解决了 2 个问题，一个是事务隔离问题，比如在可重复隔离级别下，会话 A 在 2 次查询期间，会话 B 对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求；另外一个是数据复制的问题，比如会话 A 执行了多条更新语句期间，另外一个会话 B 做了表结构变更并且先提交，就会导致 slave 在重做时，先重做 alter，再重做 update 时就会出现复制错误的现象。所以在对表进行上述操作时，如果表上有活动事务（未提交或回滚），请求写入的会话会等待在 Metadata lock wait。
支持事务的 InnoDB 引擎表和不支持事务的 MyISAM 引擎表，都会出现 Metadata Lock Wait 等待现象。一旦出现 Metadata Lock Wait 等待现象，后续所有对该表的访问都会阻塞在该等待上，导致连接堆积，业务受影响。
（2）常见 MDL 锁场景
①当前有执行 DML 操作时执行 DDL 操作
② 当前有对表的长时间查询或使用 mysqldump/mysqlpump 时，使用 alter 会被堵住
③ 显示或者隐式开启事务后未提交或回滚，比如查询完成后未提交或者回滚，DDL 会被堵住
④ 表上有失败的查询事务，比如查询不存在的列，语句失败返回，但是事务没有提交，此时 DDL 仍然会被堵住
2、myisam、innodb 对事务的支持
Myisam 是不支持事务的，innodb 是支持事务的，这个概念其实没有任何问题，但是这里只的都是对于数据的事务性操作的支持，通过如下简单的实验可以很清楚的理解（关于事务的相关概念和解释就不再赘述了，只是想区别一下 mysiam 不支持事务，但是主动开始事务中对 Myisam 的操作仍然会产生 MDL 锁）：
在隔离级别为 RC 的情况下：
（1）myisam 表
① CREATE TABLE tb2 (a int(11) DEFAULT NULL ) ENGINE=MyISAM;
② Session 1：
mysql> begin ;
mysql> insert into tb2(a) value(1);
（在 session2 的 update 之后）
mysql> select * from tb2;
+——–+
| a |
+——–+
| 3 |
+——–+
Session 2：
mysql> select * from tb2;
+———+
| a |
+———+
| 1 |
+———+
mysql> update tb2 set a=3 where a=1;
mysql> select * from tb2;
+——–+
| a |
+——–+
| 3 |
+——–+
mysql> alter table tb2 add b int(11);
… hangs …
（2）innodb 表
①CREATE TABLE tb3 (a int(11) DEFAULT NULL ) ENGINE=INNODB;
② Session 1：
mysql> begin ;
mysql> insert into tb3(a) value(1);
Session 2：
mysql> select * from tb3;
Empty set (0.00 sec)
3、myisam 表的另一个 BUG
（1）场景
① CREATE TABLE tb2 (a int(11) DEFAULT NULL ) ENGINE=MyISAM;
② Session 1：
mysql> begin ;
mysql> select * from tb2;
Session 2：
mysql> create table if not exists tb2(a int);
… hangs …
③查看 show processlist
Session 1：Sleep
Session 2：Waiting for table metadata lock
（2）解决方式
①session 1 上 commit 或者 rollback
②另外再开一个 session3，kill 掉可疑连接
此文已由作者授权腾讯云 + 社区发布
搜索关注公众号「云加社区」，第一时间获取技术干货，关注后回复 1024 送你一份技术课程大礼包！
