(mysql)table metadata lock原因及解决

旧城等待， 2024-03-30 15:46 56阅读 0赞

#### 背景 ####

在项目的一次需求中，需要对一个表增加字段，然而在执行增加字段的sql语句时，卡住了很久都没提交到Mysql完成，而此时对外接口服务请求也卡住了，这时中断卡住的alter table 语句，服务慢慢恢复正常，如果不搞清楚这个问题的根源，不敢增加字段，因为会直接影响到服务

#### 排查 ####

通过show processlist 查看到在alter table语句执行卡住过程中，累计了大量状态为 Waiting for table metadata lock 的记录

然后查看当前的事务状态 执行 select \* from information\_schema.innodb\_trx\\G

mysql> select * from information_schema.innodb_trx\G
    *************************** 1. row ***************************
                        trx_id: 421408771164000
                     trx_state: RUNNING
                   trx_started: 2019-07-02 14:27:09
         trx_requested_lock_id: NULL
              trx_wait_started: NULL
                    trx_weight: 0
           trx_mysql_thread_id: 11688
        ....

发现了其中一条已经运行了很久的事务，我怀疑跟这个运行很久的而且没有提交的事务有关。

#### 测试还原 ####

在本地mysql开多个终端测试

session 1： 开启事务，执行select 语句，但不提交事务

mysql> begin;Query OK,0 rows affected (0.00 sec)
    
    mysql>select*from t1;+------+| c1   |+------+|1|+------+1 row inset(0.00 sec)

session 2：执行增加字段sql

mysql> alter table t1 add c2 int;

执行被阻塞了

mysql> show processlist;+----+------+-----------+------+---------+------+---------------------------------+---------------------------+|Id|User|Host| db   |Command|Time|State|Info|+----+------+-----------+------+---------+------+---------------------------------+---------------------------+|27| root | localhost | test |Query|141|Waitingfor table metadata lock | alter table t1 add c2 int ||29| root | localhost | test |Query|0| starting                        | show processlist          ||30| root | localhost | test |Sleep|210||NULL|+----+------+-----------+------+---------+------+---------------------------------+---------------------------+

可以看到alter table语句的状态为Waiting for table metadata lock

session 3 ： 再次查询t1表

mysql>select*from t1;

也被阻塞了

mysql> show processlist;+----+------+-----------+------+---------+------+---------------------------------+---------------------------+|Id|User|Host| db   |Command|Time|State|Info|+----+------+-----------+------+---------+------+---------------------------------+---------------------------+|27| root | localhost | test |Query|141|Waitingfor table metadata lock | alter table t1 add c2 int ||28| root | localhost | test |Query|8|Waitingfor table metadata lock | select * from t1          ||29| root | localhost | test |Query|0| starting                        | show processlist          ||30| root | localhost | test |Sleep|210||NULL|+----+------+-----------+------+---------+------+---------------------------------+-------------------

select \* from t1 再次查询t1表也是 Waiting for table metadata lock状态，说明由于 metadata lock的存在，会导致后面正常的查询都会因为等待锁而阻塞

再查看当前事务运行状态：

mysql> select * from information_schema.innodb_trx\G
    *************************** 1. row ***************************
                   trx_id: 421408771166760
                   trx_state: RUNNING
                   trx_started: 2019-08-02 15:34:41
                   trx_mysql_thread_id: 30

可以看到，session1的事务由于还没提交，所以这里能看到它的状态还是running

这时我们commit session1的事务，看看效果

session 1：

mysql>select*from t1;+------+| c1   |+------+|1|+------+1 row inset(0.00 sec)
    
    mysql> commit;Query OK,0 rows affected (0.00 sec)

session 2：

mysql> alter table t1 add c2 int;Query OK,0 rows affected (30.51 sec)
    Records:0  Duplicates:0  Warnings:0

session 3：

mysql>select*from t1;+------+| c1   |+------+|1|+------+1 row inset(7.56 sec)

可以看到session1的事务提交后，session2 和session3 都正常执行了， 他们完成的时间分别是30秒和7秒

#### 项目 autocommit 的设置 ####

通过上面的还原测试，可以知道是由于事务没有提交而给表加了锁，导致后面alter语句因为等待锁而阻塞，从而影响后面的正常请求。

那说明我们的项目是默认开启了事务吗？

继续排查，项目是使用flask-sqlchemy的插件来管理mysql接入，然后查了下文档

在实例化sqlchemy的时候，会创建一个用于跟Mysql交互的session对象，看看源码

# db是这样使用的
    db =SQLAlchemy()
    db.__init__(app)....# 看看SQLAlchemy里面的session是怎么创建的classSQLAlchemy(object):def__init__(self, app=None, use_native_unicode=True, session_options=None,
                     metadata=None, query_class=BaseQuery, model_class=Model,
                     engine_options=None):...self.session =self.create_session(session_options)...defcreate_session(self, options):...return orm.sessionmaker(class_=SignallingSession, db=self,**options)# session 使用到是SignallingSession 这个类 classSignallingSession(SessionBase):...def__init__(self, db, autocommit=False, autoflush=True,**options):...

从 SignallingSession类的定义看来，autocommit=False，说明默认都给所有的sql执行开启事务，也就是说，哪怕是纯select语句，不需要加锁的select，我们的项目默认也需要开启事务，这对于Mysql MVCC的版本控制来说，是没必要的。

解决办法：就是在实例化SQLAlchemy的时候，给一个参数，修改的session的autocommit=True：

db =SQLAlchemy(session_options={"autocommit":True})
    db.__init__(app)

#### 关于 table metadata lock ####

来自官网的介绍：

> To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted explicitly or implicitly started transaction in another session. The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends.

意思就是为了保证事务的串行执行，而启用的一个锁，这个锁只会在事务结束的时候释放，因此在事务提交或回滚钱，任何对这个表做的DDL操作，都是会阻塞的

> If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.

这个 Metadata lock 是MySQL在5.5.3版本后引入了，为的是防止5.5.3以前的一个bug的出现：

当一个会话在主库执行DML操作还没提交时，另一个会话对同一个对象执行了DDL操作如drop table，而由于MySQL的binlog是基于事务提交的先后顺序进行记录的，因此在从库上应用时，就出现Q了先drop table，然后再向table中insert的情况，导致从库应用出错。

#### 总结 ####

*  为了事务的串行话，和数据一致性， Mysql会对打开事务进行DML的表加上table metadata lock, 在事务提交前，其他的DDL操作会阻塞

*  对于主要是查询数据的项目来说，默认不开启事务即可，如果确实需要，程序上手动开启事务

*  需要使用到事务时，也要尽量缩小事务的运行时间，一个事务中不要包含太多的语句

*  程序上对任何错误异常状况一定要捕捉后，回滚事务，否则事务脱离程序，只能等事务自己超时，手动关闭事务或者重启服务释放锁了