mysql metadata_记一次Mysql线上事故之metadata锁详解

背景

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

排查

通过显示进度列表查看到在更改表语句执行卡住过程中，逐步扩展状态为等待表元数据锁定的记录

然后查看信息的schema.innodb_trx \ G

的MySQL的> SELECT * FROM information_schema.innodb_trx \ G变变

*************************** 1.行*** ** ** ********************

trx_id：421408771164000

trx_state：运行中

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

.... ```发现了其中一条已经运行了很久的事务，我怀疑跟这个运行很久的而且没有提交的事务有关。会话1：正在开启事务，执行选择语句，但不提交事务`mysql>开始; 查询正常，影响的0行(0.00秒)mysql> select * from t1; + ------ + | c1 | + ------ + | 1 | + ------ + 1设置行(0.00秒)```第二节：执行增加变量SQL`”的MySQL> ALTER TABLE T1添加C2 INT; ```

执行被双重了了``的MySQL> SHOW PROCESSLIST; + ---- + ------ + ----------- + ------ + --------- + ------ +- -------------------------------- + ----------------- ---------- + | ID | 用户| 主持人db | 指令| 时间| 州| 信息| + ---- + ------ + ----------- + ------ + --------- + ------ +- -------------------------------- + ----------------- ---------- + | 27 | 根| 本地主机| 测试查询| 141 | 等待表元数据锁定更改表t1添加c2 int | | 29 | 根| 本地主机| 测试查询| 0 | 开始显示流程清单| | 30 | 根| 本地主机| 测试睡眠| 210 | | NULL | + ---- + ------ + ----------- + ------ + --------- + ------ +- -------------------------------- + ----------------- ---------- + ```可以看到alter table语句的状态为会议3：再次查询t1表`''mysql>从t1选择*; ```也被列入了''''的MySQL> SHOW PROCESSLIST;

+ ---- + ------ + ----------- + ------ + --------- + ------ +- -------------------------------- + ----------------- ---------- +

| ID | 用户| 主持人db | 指令| 时间| 州| 信息|

+ ---- + ------ + ----------- + ------ + --------- + ------ +- -------------------------------- + ----------------- ---------- +

| 27 | 根| 本地主机| 测试查询| 141 | 等待表元数据锁定更改表t1添加c2 int |

| 28 | 根| 本地主机| 测试查询| 8 | 等待表元数据锁定从t1选择*

| 29 | 根| 本地主机| 测试查询| 0 | 开始显示流程清单|

| 30 | 根| 本地主机| 测试 睡眠| 210 | | NULL |

+ ---- + ------ + ----------- + ------ + --------- + ------ +- -------------------------------- + ----------------- - ```

选择* from t1再次查询t1表也是等待表元数据锁定状态，说明由于元数据锁定的存在，会导致后面正常的查询都会因为等待锁而一段

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

的MySQL> SELECT * FROM information_schema.innodb_trx \ G变

************************** * 1.行******* * ******************* trx_id：421408771166760 TRX_STATE：运行 trx_started：2019年8月2日15时34分四十一秒 trx_mysql_thread_id：30 `

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

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

第一场：

mysql>从t1选择*;

+ ------ +

| c1 |

+ ------ +

| 1 |

+ ------ +

设置(0.00秒)mysql> commit中的1行; 查询OK，0行受到影响(0.00秒) ```

第二节：

的MySQL> ALTER TABLE T1附加C2 INT;

查询正常，受影响的0行(30.51秒)

记录：0重复：0警告：0```

第三节：

mysql>从t1选择*;

+ ------ +

| c1 |

+ ------ +

| 1 |

+ ------ +

1行中的集(7.56秒)```

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

### 项目autocommit的设置

通过上面的还原测试，可以知道是由于事务没有提交而给表加了锁，导致后面alter语句因为等待锁而同时，从而影响后面的正常请求。那说明我们的项目是替代开启了事务吗？继续排查，项目是使用flask-sqlchemy的插件来管理mysql连接，然后查到下文档在实例化sqlchemy的时候，会创建一个用于跟Mysql交互的会话对象，看看子系统

db是这样使用的

db = SQLAlchemy()

db.init(app)

…

看看SQLAlchemy里面的session是怎么创建的

class SQLAlchemy(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)

…

def create_session(self, options):

…

return orm.sessionmaker(class_=SignallingSession, db=self, **options)

session 使用到是SignallingSession 这个类

class SignallingSession(SessionBase):

…

def init(self, db, autocommit=False, autoflush=True, **options):

…

从SignallingSession类的定义看来，autocommit = False，说明替换都给所有的sql执行开启事务，从而，哪怕是纯select语句，不需要加锁的选择，我们的项目要么也需要开启事务，这对于Mysql的MVCC的版本控制来说，是没必要的。解决办法：就是在实例化的SQLAlchemy的时候，给一个参数，修改的会话的自动提交=真：

db = SQLAlchemy(session_options = {“ autocommit”：True})db。初始化(应用)

`###关于表的元数据锁定来自官网的介绍：>为了保证交易的串行化，服务器必须允许一个会议，就被以未完成使用显式或隐式表上执行数据定义语言(DDL )语句在另一个会话中开始交易。服务器通过获取事务中使用的表上的元数据锁并释放释放这些锁直到事务结束来达到此目的。就是为了保证事务的串行执行，而启用的一个锁，这个锁只会在事务结束的时候释放，因此在事务提交或回滚钱，任何对这个表做的DDL操作，都是会分割的

如果服务器获取语法上有效但在执行过程中失败的语句的元数据锁，则它不会提早释放锁。锁定释放仍被替换到事务的结尾，因为失败的语句被写入二进制日志和锁定保护了日志的一致性。这个元数据锁定是MySQL在5.5.3版本后发布了，为的是防止5.5.3以前的一个错误的出现：当一个会话在主库执行DML操作还没提交时，另一个会话对同一个对象执行了DDL操作如drop table，而由于MySQL的binlog是根据事务提交的先后顺序进行记录的，因此在从库上应用时，就出现Q了先降表，然后再向表＃ ##总结为了事务的串行话，和数据一致性时，MySQL临时打开事务进行DML的表加上表元数据锁，在事务提交前，其他的DDL操作会副本关于主要是查询数据的项目而言，而不是开启事务即可，如果确实需要，程序上手动开启事务需要使用到工作时，也要尽量缩小事务的运行时间，一，，回滚事务，否则事务中断程序，只能等事务自己超时，手动关闭事务或重启服务释放锁了