mysql排插问题_MySQL一次数据插入故障记录-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_28803105/article/details/113610786

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

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

RECORD LOCKS space id 764 page no 471030 n bits 968 index idx_create_time of table `fbs_fdc`.`fbs_sync_logs_taojj_goods` trx id 7007107746 lock_mode X insert intention waiting

Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

0: len 8; hex 73757072656d756d; asc supremum;;

RECORDLOCKSspaceid764pageno471030nbits968indexidx_create_timeoftable`fbs_fdc`.`fbs_sync_logs_taojj_goods`trxid7007107746lock_modeXinsertintentionwaiting

Recordlock,heapno1PHYSICALRECORD:n_fields1;compactformat;infobits0

0:len8;hex73757072656d756d;ascsupremum;;

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

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

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

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

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

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

explain

DELETE

FROM fbs_fdc.fbs_sync_logs_taojj_goods

WHERE id<(

SELECT min_id

FROM(

SELECT MIN(id) min_id

FROM fbs_fdc.fbs_sync_logs_taojj_goods

WHERE create_time>=(UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL - 10 DAY)))) tmp)

explain

DELETE

FROMfbs_fdc.fbs_sync_logs_taojj_goods

WHEREid<(

SELECTmin_id

FROM(

SELECTMIN(id)min_id

FROMfbs_fdc.fbs_sync_logs_taojj_goods

WHEREcreate_time>=(UNIX_TIMESTAMP(DATE_ADD(NOW(),INTERVAL-10DAY))))tmp)

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

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

---TRANSACTION 7063564148, ACTIVE 22 sec

962 lock struct(s), heap size 221392, 1076484 row lock(s)

MySQL thread id 151743704, OS thread handle 47149846046464, query id 16175417511 10.16.3.169 user_admin

---TRANSACTION7063564148,ACTIVE22sec

962lockstruct(s),heapsize221392,1076484rowlock(s)

MySQLthreadid151743704,OSthreadhandle47149846046464,queryid1617541751110.16.3.169user_admin

可以看到非常多的行锁。

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

如果您觉得本站对你有帮助，那么可以支付宝扫码捐助以帮助本站更好地发展，在此谢过。