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

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

 

ALTER TABLE `sc_stockout_order` ADD `route_remarks` VARCHAR(1024)  CHARACTER SET utf8mb4  NULL  DEFAULT

   

 

     mysql配置如下：

 

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

 

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

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

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

 

Waiting for table metadata lock

从图中也可以看到活跃连接数到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、后续可现在预发环境或测试环境先行模拟，评估风险