Mysql新增字段到大数据表导致锁表
昨天晚上7点左右，对一张表进行加字段，大概200多万条记录，字段90多个的大表，结果造成mysql锁表，进而导致服务不可用。执行语句如下：
[sql] view plain copy- ALTER TABLE `sc_stockout_order` ADD `route_remarks` VARCHAR(1024) CHARACTER SET utf8mb4 NULL DEFAULT
mysql配置如下：
打开服务期日志，发现有如下报错：
[java] view plain copy- 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 掉修改表格语句的进程，此时系统恢复正常。在这个当中，发现了一条语句如下：
[sql] view plain copy- 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、后续可现在预发环境或测试环境先行模拟，评估风险
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！