[MYSQL] select导致主从延迟增大 (原因:autocommit=off)

导读

之前也有遇到从库select导致从库延迟的, 那是因为从库的select数据量太大, 锁还没释放. 本次又遇到个类似的案例, 也是select导致从库延迟太大.

现象

客户环境不方便截图, 就用文字描述了. 或者直接看复现过程也行.

主库执行insert数据, 从库select验证, 主库再执行drop table操作, 从库验证时发现未能drop掉, 且延迟在增大, show processlist能看到 Waiting for table metadata lock.

分析

其实我们查询元数据锁相关的表, 就能找到相关的会话了. 但我show processlist看到的会话就只有 我的连接 和 主从相关的进程, 难道是我阻塞了(select未释放相关资源)? 但我select已经结束了啊. 这就奇怪了.

我们还是查询一下元数据锁相关的表/视图吧, select * from sys.schema_table_lock_waits\G 查询发现确实是在等一个连接, 而那个连接确实是我的连接. 查询information_schema.innodb_trx找到我们的事务是RUNNING状态, 再查询performance_schema.events_statements_current 发现我们最近跑的一条SQL确实是查询那个表的... 也就是确实是我们的select阻塞了.

复现/模拟

本次就不适用主从模拟了, 而使用两个会话模拟, 只要是阻塞了就行

-- session 1
set session autocommit = 0;
select * from db1.t2 limit 1;

-- session 2
drop table db1.t2; -- 将会被阻塞

show processlist

光看这个图还是比较有迷惑性的(如果把上面的autocommit遮住的话)

查询元数据锁 sys.schema_table_lock_waits

这里就已经能看到阻塞的会话就是咱自己....是谁阻塞了我, 是我阻塞了我

也可以查询performanceschema.events_statements* 看看具体的SQL.

总结

基础不牢, 地动山摇.

生产环境还是不容易见到的. 条件: 某事务在查询将要删除的表, 但未提交.

同事在oracle上也测试了下, 发现select未提交 不影响drop操作.