• 进入"运维那点事"后,希望您第一件事就是阅读“关于”栏目,仔细阅读“关于Ctrl+c问题”,不希望误会!

MySQL一次数据插入故障记录

MySQL FAQ 彭东稳 5年前 (2020-05-08) 19670次浏览 已收录 0个评论

某天突然收到报警,数据库大量事务等待,进到数据库后发线大量的插入操作被阻塞,且都是同一个表的。

通过 show engine innodb status 发现插入操作都是在等待索引 idx_create_time(create_time) 的 insert intention lock(跟 gap 锁互斥),由于某些原因数据库是 RR 隔离级别。

当时查了半天,也没有发现跟这个表相关的其他操作。后面解决方案很狗血,下面再说。

后来查故障问题的时候,首先看了这个故障时间点跟这个表相关的操作。先发现了大量了 insert 操作(达到了数据库默认的 50s 锁超时),如下图。

MySQL一次数据插入故障记录

所以确定了 insert 肯定都是在等待锁,到底在等待什么锁呢?当时把跟这个表相关的所有语句都 kill 了,但是有遗漏,就是已经执行完但事务还没有提交的 SQL。

然后从第一条 insert 报错的时间往后查找记录,此时就发现了重要线索:

MySQL一次数据插入故障记录

在同一个线程 ID 下先开启了事务,然后对 DELEET 操作做了一个执行计划,并且事务没有提交(另外一个同事在 workbench 工具做的,忘记提交事务)。并且从时间上来看,这个操作刚刚执行完,insert 就开始报错了,所以基本肯定是事务没有提交导致的了。

接着就是复现问题了,发现对 DELETE 语句做 EXPLAIN 含有子查询时,子查询是加锁的(没有子查询的没有锁),我们看一下这条语句具体加什么锁:

看子查询可以知道,由于 create_time 字段是有索引的(上面锁等待里面 idx_create_time 索引),并且条件是查大于当前时间减去 10 天的时间,由于是 RR 隔离级别,所以对大于这个时间的记录都加了记录锁 + Gap锁,且 Gap 锁住了 (表最大记录, supremum]。所以导致其他记录都插入时,由于时间字段时单调递增的,所以都被这个 (表最大记录, supremum] 区间锁住了。如果插入的记录时间是小于 (UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL – 10 DAY)))) 这个区间的话,就不会有问题了,因为没有被 Gap 锁住。

对于上面的 EXPLAIN 来说,锁的信息并不是那么明显。如下所示:

可以看到非常多的行锁。

知道了这些信息之后,再来说当时是怎么解决的。我同事在开启的哪个事务里面执行了一个 rename table 操作,把表重命名了,然后重新创建了一张一样的表就好了。因为是在同一个事务里面执行的 EXPLAIN DELETE,所以 rename table 不需要等待锁。如果在其他事务的话,rename table 也应该被阻塞了。


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (0)
[资助本站您就扫码 谢谢]
分享 (0)

您必须 登录 才能发表评论!