Mysql新增字段到大数据表导致锁表

电玩女神 2022-05-22 05:08 278阅读 0赞

昨天晚上7点左右，对一张表进行加字段，大概200多万条记录，字段90多个的大表，结果造成mysql锁表，进而导致服务不可用。执行语句如下：

\[sql\]  [view plain][]   [copy][view plain]

1.  **ALTER** **TABLE** \`sc\_stockout\_order\` **ADD** \`route\_remarks\` **VARCHAR**(1024)  **CHARACTER** **SET** utf8mb4  NULL  **DEFAULT**

mysql配置如下：

![Center][]

打开服务期日志，发现有如下报错：

\[java\]  [view plain][]   [copy][view plain]

1.  Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection;   
2.          nested exception is org.apache.tomcat.jdbc.pool.PoolExhaustedException:   
3.          \[DubboServerHandler-10.162.99.129:20880\-thread-105\] Timeout: Pool empty.   
4.          Unable to fetch a connection in 50 seconds, none available\[size:80; busy:79; idle:0; lastwait:50000\].  
5.          at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:26) ~\[mybatis-3.2.8.

我们发现数据库jdbc拿不到链接，虽然没有到300最大数据库连接数，但是两台服务器80\*2=160个链接数已经达到配置的客户端最大连接数。这边也说明我们客户端配置的链接数不太合理，可以再稍微调大一点。

发现这个问题后，为了尽快恢复线上服务，用show processlist发现ALTER TABLE这条语句导致大量查询语句处于等待状态，赶紧kill 掉修改表格语句的进程，此时系统恢复正常。在这个当中，发现了一条语句如下：

\[sql\]  [view plain][]   [copy][view plain]

1.  Waiting **for** **table** metadata lock

![Center 1][]

从图中也可以看到活跃连接数到160之后就不变了，kill掉进程后恢复。

事后查找资料：

Mysql在5.6版本之前，直接修改表结构的过程中会锁表，具体的操作步骤如下：  
（1）首先创建新的临时表，表结构通过命令ALTAR TABLE新定义的结构  
（2）然后把原表中数据导入到临时表  
（3）删除原表  
（4）最后把临时表重命名为原来的表名

具体ddl如何工作

参考：http://www.cnblogs.com/cchust/p/4639397.html

Mysql 5.6 虽然引入了Online DDL，但是并不是修改表结构的时候，一定不会导致锁表，在一些场景下还是会锁表的，比如  
①某个慢SQL或者比较大的结果集的SQL在运行，执行ALTER TABLE时将会导致锁表发生；  
②存在一个事务在操作表的时候，执行ALTER TABLE也会导致修改等待；  
查看我们mysql的版本：SELECT VERSION();  给出：5.6.16-log  
我们通过Mysql的慢SQL控制台，也在发生问题的时间段内没有出现慢SQL，所以需要排除第一种可能性；  
由于当时没有保留现场，所以当时是不是由于事物导致的锁表，现在也无从查起，这只能下次查看分析了。

根据这次教训，得到注意项：

1、尽量选择流量小的事后执行。当天20：00要大促，所以19：00大量供应商在操作。当我们选择在22：00左右再次执行时，就没再出现这个问题

2、执行时先看一下有没有未提交的事务，注意查看事物information\_schema.innodb\_trx表

3、随时关注服务器日志状况，已有问题要先行解决。

4、后续可现在预发环境或测试环境先行模拟，评估风险

[view plain]: https://blog.csdn.net/woyantianyi/article/details/51689932#
[Center]: /images/20220522/40779f543e9a4d24b46f75f469ece607.png
[Center 1]: /images/20220522/a9cb1c51705a419d83c0d20e73066844.png