某天突然收到报警，数据库大量事务等待，进到数据库后发线大量的插入操作被阻塞，且都是同一个表的。
通过 show engine innodb status 发现插入操作都是在等待索引 idx_create_time(create_time) 的 insert intention lock（跟 gap 锁互斥），由于某些原因数据库是 RR 隔离级别。
|
1 2 3 |
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;; |
当时查了半天，也没有发现跟这个表相关的其他操作。后面解决方案很狗血，下面再说。
后来查故障问题的时候，首先看了这个故障时间点跟这个表相关的操作。先发现了大量了 insert 操作（达到了数据库默认的 50s 锁超时），如下图。
所以确定了 insert 肯定都是在等待锁，到底在等待什么锁呢？当时把跟这个表相关的所有语句都 kill 了，但是有遗漏，就是已经执行完但事务还没有提交的 SQL。
然后从第一条 insert 报错的时间往后查找记录，此时就发现了重要线索：
在同一个线程 ID 下先开启了事务，然后对 DELEET 操作做了一个执行计划，并且事务没有提交（另外一个同事在 workbench 工具做的，忘记提交事务）。并且从时间上来看，这个操作刚刚执行完，insert 就开始报错了，所以基本肯定是事务没有提交导致的了。
接着就是复现问题了，发现对 DELETE 语句做 EXPLAIN 含有子查询时，子查询是加锁的（没有子查询的没有锁），我们看一下这条语句具体加什么锁：
|
1 2 3 4 5 6 7 8 9 |
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) |
看子查询可以知道，由于 create_time 字段是有索引的（上面锁等待里面 idx_create_time 索引），并且条件是查大于当前时间减去 10 天的时间，由于是 RR 隔离级别，所以对大于这个时间的记录都加了记录锁 + Gap锁，且 Gap 锁住了 (表最大记录, supremum]。所以导致其他记录都插入时，由于时间字段时单调递增的，所以都被这个 (表最大记录, supremum] 区间锁住了。如果插入的记录时间是小于 (UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL – 10 DAY)))) 这个区间的话，就不会有问题了，因为没有被 Gap 锁住。
对于上面的 EXPLAIN 来说，锁的信息并不是那么明显。如下所示：
|
1 2 3 |
---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 |
可以看到非常多的行锁。
知道了这些信息之后，再来说当时是怎么解决的。我同事在开启的哪个事务里面执行了一个 rename table 操作，把表重命名了，然后重新创建了一张一样的表就好了。因为是在同一个事务里面执行的 EXPLAIN DELETE，所以 rename table 不需要等待锁。如果在其他事务的话，rename table 也应该被阻塞了。
