记录一次alter更改表结构 waiting for table metadatalock

情景描述

1. 在生产表执行alter table xxx add column column1 int(1) comment ‘xxxxxx’;
2. 等了几十秒后，仍未响应。

排查思路

1. 该生产表有几十万级别数据，所以推测是不是数据量太大了，执行时间无法估量
2. 是不是表锁了，或者某一行锁了，导致插入新列失败

1. 如果是表数据量太大的话

1. 复制一个表结构出来 create table tabl1 like table_xxx
2. 把复制出来的表结构加上新列 alter tabl1 add colimn xxxxxxxxx
3. 把原有数据分批插进来 insert into table1(x,x,x,x,x) select * from table_xxx order by id limit 0,5000
4. insert into table1(x,x,x,x,x) select * from table_xxx order by id limit 5000,n
5. 再执行drop table table_xxx 和 rename table1
6. 但是发现drop table table时仍未响应,继续向下排查

2. 最后排查表锁的问题

1. 查询锁占用情况
   select * from information_schema.INNODB_LOCKS;
   如下图例子，如果有两个操作互相要锁。且有一个占用不释放，会有如下两条记录，表明锁被占用。
   
   但是在生产上执行该sql后无任何记录，select * from information_schema.INNODB_LOCK_WAITS也没有发现等待其他锁释放，所以判断应该不是锁的问题。
2. 那么看一下到底线程都在干什么
   select * from information_schema.PROCESSLIST p where p.COMMAND != ‘sleep’
   
   这时发现alter的sql文的state为Waiting for table metadata lock状态，那么metadata lock是什么？

3. metadata lock

引用官文：

A metadata lock on a table prevents changes to the table’s structure.
This locking approach has the implication that a table that is being
used by a transaction within one session cannot be used in DDL
statements by other sessions until the transaction ends.

也就是说为了保护表结构的完整性、元数据的一致性。事务不提交，metadata lock不会释放。

4. 那么排查重点就到了事物状态上

1. 执行数据库事物状态sql文
   select * from information_schema.innodb_trx;
   
   发现有一个事物开启的特别早，而且重复执行select * from information_schema.innodb_trx;，trx_query的执行sql一直在变化。
2. 根据trx_query的sql文定位项目代码，发现是一段python代码，大致逻辑是：

delete fron table1
select xxxxxxx
select yyyyyyy
addAdd()
getSession()
update table1 set xxxxxxxxx
session.commit()
从逻辑上可以看出来，事物长期占用，直到所有update执行完才commit，所以一个事物长期占用

3. 由于定时任务每次启动都会跑，所以决定重启该项目
4. 重启后 执行drop table table1 成功执行

总结:
1 一个方法长期占用事物会导致更改表结构失败
2 更改表结构时 应加上set lock_wait_timeout = xx避免长时间等待meta锁 而无响应
3 发现事物长占用 根据trx_query定位问题 根据mysql_thread_id kill掉线程 执行本次上线的内容，后续优化代码再上线