mysql ddl 事务_Mysql DML操作事务未提交情况下进行DDL操作会造成Meatadata 锁

问题描述

3月29日至3月30日早上8点，报表订阅发送队列阻塞，造成大量业务报表没有及时发送事后分析发现执行Dnsmasq报表任务时间过长，造成队列消费者worker程序block(由于每天报表任务不多<100，固采用单线程wroker执行) 我们具体查看了计算结果表mc_stat_dnsmasq 和这个时段指标历史数据，发现metadata lock表锁(图1). dnsmasq表被锁造成报表订阅程序无法正常select查询该表数据(29号被锁是在05:00-10:00, 30号是05:00–09:00 ,其中9点是将执行中计算脚本kill掉)，.  我们继续对Dnsmasq计算分析脚本代码(见附件)逐层进行分析mc_stat_dnsmasq  为什么会 被锁表情况. 通过问题复现发现在其它会话DML操作没提交事务情况下执行了create table if not exists造成Metadata lock，这种情况锁表select查询也无法操作. 以下是问题复现.

问题复现

连接会话1 做DML操作没有提交事物;

Connection1

mysql> create table mc_stat_dnsmasq_test(ip varchar(100));

Query OK,0 rows affected (0.01 sec)

mysql> start transaction;

Query OK,0 rows affected (0.00 sec)

mysql> insert into mc_stat_dnsmasq_test values('127.0.0.1');

Query OK,1 row affected (0.00 sec)

mysql>

连接会话2: 执行 create table if not exit 语句操作，被block了, show processlist 发现该表已经被锁.

Connection2:

mysql> create tableif not exists mc_stat_dnsmasq_test(ip varchar(100));

... 执行block ...

连接会话3:  执行查询语句无法进行操作

Connection3:

mysql> select * from mc_stat_dnsmasq;

... 执行block ...

处理办法

Dmsmasq计算脚本处理办法：

1.程序代码逻辑做规避，采用select from mc_stat_dnsmasq判断,如果表已经存在，就不执行create table. 放弃使用create table if not exists

2. 查找程序中DML操作，开启事务，未提交事务代码;

3.后期对计算分析结果表不采用程序判断自动创建结果表，采用预先创建

对报表订阅优化：

1、调整消费者执行策略多线程的方式对队列进行消费；

2、优化报表监控日志，增加消费时间，处理状态等信息