MYSQL 超时问题排查过程

发现疑点

通过IT的监控发现存在负载(Load)最高了两个SQL如下：

SELECT * FROM `wobshiporder` WHERE `WOBShipOrderID` = ? FOR UPDATE

INSERT INTO `WOBShipOrder` ( `OAID` , `IsDecryptReceiver` …

语句耗时10秒以上。

问题定位

开始分析第一个SQL语句时：选举了一个example

Select * from wobshiporder where WOBShipOrderID = 29430691077040274 for Update

查看explain结果，发现没有查询到数据。

接着根据MYSQL的加锁规则，推导出Insert into WOBShipOrder语句会被前一个For update语句锁住，因为ID基本上是自增的（根据时间生成的），后续插入就可能被间隙锁锁住。

原理分析

这里可以通过实验验证间隙锁对插入语句影响：

首先创建一个Test表，开启两个终端：

CREATE TABLE `test` (

`ID` int NOT NULL AUTO_INCREMENT,

`C` varchar(255) DEFAULT NULL,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB;

问题排查

接着就排查系统使用到select  *from  wobshiporder where WOBShipOrderID = XXX for Update的所有业务代码（其中涉及到复核、取消、修改发货人、解密）等。

通过接口日志、操作日志进一步排除使用这个SQL语句的无关操作，只剩下复核，根据复核代码逻辑，以及验证单条SQL语句执行耗时，发现不是复核本身引起锁超时。一定存在其他操作导致了wobshiporder表被锁住（推测原因：for update间隙锁不会被其他锁住）。

故继续查看出现锁超时前后一分钟的所有业务操作（接口日志、操作日志），发现订单分配操作存在可疑，再结合业务报错的几个时间段，排查前后的操作日志，都存在批量分配的操作，故进一步查看批量分配的逻辑代码，发现此操作是一个大事务（涉及操作几千个订单、生成几千条记录，批量更改几千个订单的状态，故存在锁表）。

问题处理

通过以上步骤明确了具体的业务操作方法，通过分析得知客户勾选批量的波次提交到后台统一分配，故将批量波次按照单个波次进行循环操作（由于每个波次是独立的操作，可以不在一个事务中处理），处理问题的理论是：大事务拆分小事务。