记一次Mysql锁问题

一、问题现象

1.现象:

页面上所有对数据库的操作都无法进行,程序连接池跑满。

2.应用侧报错如下:

Caused by: java.sql.SQLException: [1388d8fe714001-1][10.0.*.*:3306][saas]ERR-CODE: [TDDL-4614][ERR_EXECUTE_ON_MYSQL] Error occurs when execute on GROUP 'SAAS_DB_*_WXET_0*' ATOM 'rm-*_saas_db_qac2_0000': wait millis 5000, active 100, maxActive 100, creating 0 
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3835)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3771)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)

3.数据库现象:

出现大量 Waiting for table metadata lock  锁等待

二、执行操作

ALTER TABLE dj_fwqxx ADD COLUMN skply varchar(10) CHARACTER SET gbk COLLATE gbk_chinese_ci comment '税控盘来源'

三、问题复现

1、同一个数据库中,开启三个会话。

2、在第一个会话中开启一个显示事务,并且执行一条dml语句,模拟应用侧操作。

begin;
INSERT INTO `sk_lock`.`dj_fwqxx` (`id`, `jqbh`, `ipdz`, `dkh`, `fwqlx`, `fwqxh`, `fwqrl`, `fplxdm`, `qybz`, `fwqbm`, `fwqbbh`, `kpdwdm`, `kpdwmc`, `swjgdm`, `swjgmc`, `sldz`, `sldkh`, `kpjh`, `zcm`, `zcm_qsrq`, `zcm_zzrq`, `zcm_num`, `app_url`, `app_state`, `prov`, `ORG_ID`, `TENANT_ID`, `dqsz`, `qysj`, `qylx`, `blxx`, `qtkzxx`, `gzzt`, `jkkzzt`, `sbzskl`, `cpyxgsl`, `locations`, `lasttimes`, `czfs`, `pzid`, `bsipdz`, `bsdkh`, `tqm`, `ptbs`, `dxfwqybz`, `mac`, `bz`, `falg`, `zs`, `usb`, `xfbs`) VALUES ('0000724c-1a06-4ce9-969e-7449827f9b36', '53990****', 'j****.org', '60**', '1', '', '0', '004,007,', 'Y', '5399****', '', '913****', '常州******', '132******', '常州************', 'fpsc.j******', '******', '0', NULL, NULL, NULL, NULL, 'http://******', 'Y', '江******', NULL, NULL, '202******', '201******', '00', '00******', '', '', NULL, '12345678', NULL, NULL, NULL, NULL, '084ffd51-b714-48d******3', 'bswj.j******', '9001', NULL, NULL, NULL, '', NULL, '是', '是', NULL, NULL);

3、在第二个会话中中进行ddl操作

ALTER TABLE dj_fwqxx ADD COLUMN skply varchar(10) CHARACTER SET gbk COLLATE gbk_chinese_ci comment '税控盘来源'

4、在第三个会话中查看线程状态

出现源数据锁

四、问题分析

1、Meta Lock锁出现的三种情况。

1、大查询或mysqldump导致alter等待MDL
2、表上存在未提交的事务，导致alter等待MDL
3、存在一个查询失败的语句，比如查询不存在的列，语句失败返回，但是事务没有提交，此时alter仍然会被堵住。

2、问题定位

在对该表执行DDL语句时,正好有DML语句对该表进行操作,由于DDl语句已经进行了表结构修改,导致DML语句处于一个即未提交,也未回滚的状态(猜测应用侧对这种情况没有进行处理
所以就会一直出现meta lock,后续所有进来的sql，也就会出现waiting lock ,而由于这个表使用比较频繁，很快就导致 Proxy 到实际 db的连接池被打满,
也就出现了 "wait millis 5000, active 100, maxActive 100",而应用侧整体连接数大于 Db,也就出现了程序重启后无法连接到数据库。

五、问题解决

方案一、查找到导致阻塞的事务,杀掉对应线程。

select * from information_schema.innodb_trx

kill 对应线程号

方案二、终止DDL语句，选择业务低峰期或其他时间段执行。

#
show PROCESSLIST #找到对应的ddl语句,kill掉 对应的进程id


#注意:
数据库在执行该操作时,需要考虑表是否进行分库分表,如果表进行了分库分表,可能会导致出现数据不一致情况,
如分库分表出现元数据库锁，切忌不可在实际rds进行kill操作。

参考: https://www.cnblogs.com/cchust/p/3826398.html