线上SQL超时场景分析-MySQL超时之间隙锁

前言

之前遇到过一个由MySQL间隙锁引发线上sql执行超时的场景，记录一下。

背景说明

分布式事务消息表：业务上使用消息表的方式，依赖本地事务，实现了一套分布式事务方案

消息表名：mq_messages

数据量：3000多万

索引：create_time 和 status

status：有两个值，1 和 2， 其中99%以上的状态都是2，表示分布式事务全部已经执行完成，可以删除。

消息表处理逻辑：

1. 启动一个独立的定时任务，删除status=2的历史数据，具体的sql如下：

    delete from mq_messages where create_time<xxx and status=2 limit 200

2. 定时任务执行频率：3分钟跑一次任务，一个任务执行200次 删除。这个条件基本上筛选出了90%以上的数据

业务逻辑：线上业务在执行时，不断的往表里插入status=1的数据，主键id随着时间是递增的

sql超时产生的场景

一次大型促销活动流量峰值的时候，出现了一次数据库连接被打满的情况，初步定位是数据量太大了导致锁表导致的。为了防止数据库连接被再次打满，需要尽快的删除状态为2的数据，手动执行定时任务，删除数据，具体sql为：

delete from mq_messages where status=2 limit 2000

三分钟执行一次任务，一个任务执行200次删除。

然后，数据库连接马上被打满，数据库挂了。

复盘分析

线上是否存在表锁？

初始化表结构（简化后的表结构）

CREATE TABLE `my_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `state` int(11) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `state` (`state`) USING BTREE
) 
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

存储过程准备测试数据

DELIMITER $$
  CREATE PROCEDURE pro_copy_date()
  BEGIN
  SET @i=1;
  WHILE @i<=100000 DO
  INSERT INTO my_test VALUES(@i,@i,@i,1); 
  SET @i=@i+1;
  END WHILE;
END $$
call pro_copy_date();
UPDATE my_test SET state =2 WHERE id <= 99990;

验证

1. 数据基本情况

表中一共有10万条数据，只有后10条的state=1(id>99990)

2. 事务隔离级别可重复读

3. 开启一个事务A，并且不提交

执行 DELETE FROM my_test WHERE state =2 LIMIT 2000;