事务嵌套导致的 SQL 超时

[Bpazy]

最近发生了个有趣的事情，事务的模型大概是这样：

问题描述：

1. 外层事务更新的数据和内部事务新增的数据不重叠；
2. 内部事务执行 insert 超时；

接下来就是调查，在执行 insert 时，查看 MySQL 的锁情况：

select * from information_schema.innodb_trx;
select * from information_schema.innodb_locks;

得到结果:

可以看到 insert 语句处于 LOCK WAIT 状态直到 SQL 超时终止，再看看代码实现：

1. 外部事务注解：@Transactional(timeout = 10, rollbackFor = Exception.class)
2. 内部事务注解：@Transactional(propagation = Propagation.REQUIRES_NEW, timeout = 10)

可以看到，内部事务的传播级别被设置为 REQUIRES_NEW，这意味着当代码从外部走入内部时，外部事务会被挂起，直到内部事务提交后才会继续执行。

再结合上面的 information_schema.innodb_trx 反映的事务情况，可以得到结论：外部事物锁住了部分数据，内部事务尝试获取锁，典型的死锁场景。

现在来分析问题，首先介绍下 information_schema.innodb_locks 中的一些概念：

innodb_locks 提供有关 InnoDB 事务已请求但尚未获取的每个锁的信息，以及事务持有的阻止另一个事务的每个锁。 下面对 innodb_locks 表的每个字段进行解释： lock_id：锁 ID。 lock_trx_id：拥有锁的事务 ID。可以和 INNODB_TRX 表 JOIN 得到事务的详细信息。 lock_mode：锁的模式。有如下锁类型：行级锁包括：S、X、IS、IX，分别代表：共享锁、排它锁、意向共享锁、意向排它锁。表级锁包括：S_GAP、X_GAP、IS_GAP、IX_GAP 和 AUTO_INC，分别代表共享间隙锁、排它间隙锁、意向共享间隙锁、意向排它间隙锁和自动递增锁。 lock_type：锁的类型。RECORD 代表行级锁，TABLE 代表表级锁。 lock_table：被锁定的或者包含锁定记录的表的名称。 lock_index：当 LOCK_TYPE=’RECORD’ 时，表示索引的名称；否则为 NULL。 lock_space：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的表空间 ID；否则为 NULL。 lock_page：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的页号；否则为 NULL。 lock_rec：当 LOCK_TYPE=’RECORD’ 时，表示一堆页面中锁定行的数量，亦即被锁定的记录号；否则为 NULL。 lock_data：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的主键；否则为NULL。

再结合上文中的 information_schema.innodb_locks 查询结果，可以看到内外部事务持有的都是间隙锁。恍然大悟，外部事务是利用二级索引而非主键索引更新数据的。

这里又有一个知识点：对二级非唯一索引加锁时，会锁定间隙而并不只是锁定精确的数据，详细的可以看我这里的介绍: 为什么需要间隙锁 #209

现在问题清晰了，如下表：	外部事务	内部事务
start transaction
持有间隙锁: update ts_stock_return_detail set a = 1 where id_stockreturn = ?
transaction suspend(Spring 声明式事务意义上的)
	start transaction
	尝试获取间隙锁: insert into ts_stock_return_detail
死锁发生：等待内部事务结束	死锁发生：等待外部事务释放锁

所以这个问题就很好解决了，外部事务改为主键更新即可：update ts_stock_return_detail set a = 1 where pk_id in (?, ?, ?);

也许有人会疑惑 MySQL 为什么没有报死锁异常而是超时呢？因为这是代码逻辑上的死锁，并不是 MySQL 内部真实发生的。