数据库死锁问题排查：两事务关联逻辑及SQL优化咨询

死锁场景还原 #

事务1错误与SQL #

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock;
执行SQL：

UPDATE job_instruction ji                
INNER JOIN ji_temp2 ON ji.id = ji_temp2.id                
SET ji.job_instruction_group_id = ji_temp2.job_instruction_group_id

事务2错误与SQL #

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded;
执行SQL：

UPDATE inventory SET                                    
on_hand_quantity = ifnull(on_hand_quantity,0) + (1)
,allocated_quantity = ifnull(allocated_quantity,0) + (1)                                    
,in_transit_quantity = ifnull(in_transit_quantity,0) + (-1)                                    
,suspended_quantity = ifnull(suspended_quantity,0) + (0)                                    
,updated_at = 1737630861                                    
WHERE id = 2474210

事务1核心代码 #

// 批量插入job_instruction_group
\Yii::$app->db->createCommand()->batchInsert(
    JobInstructionGroup::tableName(),
    ['job_id', 'unit_of_measure', 'inventory_id', 'units', 'quantity', 'out_bound_status_flow_id', 'created_at', 'updated_at', 'created_by', 'updated_by'],
    $job_instruction_groups
)->execute();

// 补充job_instruction_groups数据（循环逻辑省略）

// 创建临时表获取job_instruction数据
\Yii::$app->db->createCommand("CREATE TEMPORARY TABLE ji_temp1 AS SELECT * FROM job_instruction WHERE job_instruction.job_id IN (".implode(',',array_unique($jobIds)).")")->execute();

// 关联查询生成ji_temp2
\Yii::$app->db->createCommand("
CREATE TEMPORARY TABLE ji_temp2 AS SELECT ji_temp1.id, 
    job_instruction_group.id AS job_instruction_group_id 
from ji_temp1
INNER JOIN shipment_detail_child 
ON shipment_detail_child.job_instruction_id = ji_temp1.id
INNER JOIN shipment_detail 
ON shipment_detail.id = shipment_detail_child.shipment_detail_id
INNER JOIN shipment_header 
ON shipment_header.id = shipment_detail.shipment_header_id
INNER JOIN job_instruction_group 
ON job_instruction_group.job_id = ji_temp1.job_id 
AND job_instruction_group.inventory_id = ji_temp1.inventory_id 
AND job_instruction_group.out_bound_status_flow_id = shipment_header.out_bound_status_flow_id")->execute();

// 更新job_instruction
\Yii::$app->db->createCommand("
UPDATE job_instruction ji
INNER JOIN ji_temp2 ON ji.id = ji_temp2.id
SET ji.job_instruction_group_id = ji_temp2.job_instruction_group_id")->execute();

事务2核心代码 #

Yii::$app->db->createCommand('UPDATE inventory SET 
    on_hand_quantity = ifnull(on_hand_quantity,0) + (:on_hand_quantity)
    ,allocated_quantity = ifnull(allocated_quantity,0) + (:allocated_quantity)
    ,in_transit_quantity = ifnull(in_transit_quantity,0) + (:in_transit_quantity)
    ,suspended_quantity = ifnull(suspended_quantity,0) + (:suspended_quantity)
    ,updated_at = :updated_at
    WHERE id = :id',
    [ ':id' => $id, 
      ':on_hand_quantity'=>$on_hand_quantity,
      ':allocated_quantity'=>$allocated_quantity,
      ':suspended_quantity'=>$suspended_quantity,
      ':in_transit_quantity'=>$in_transit_quantity,
      ':updated_at'=>$time])->execute();

死锁成因分析 #

这不是时间巧合，两个事务的锁依赖形成了循环等待：

1. 隐式外键锁关联：事务1批量插入job_instruction_group时，由于该表inventory_id是指向inventory.id的外键，数据库会自动对关联的inventory记录（包括事务2操作的id=2474210）加共享锁（S锁），用于验证外键合法性。
2. 锁等待循环：事务2执行UPDATE inventory需要对目标记录加排他锁（X锁），但该记录被事务1的S锁占用，因此事务2进入等待。同时，事务1后续更新job_instruction时，若事务2所在的完整事务（你提供的代码可能只是片段）持有了job_instruction相关记录的锁，会导致事务1也进入等待，最终触发死锁。
3. 间隙锁扩大范围：如果job_instruction或inventory的查询/更新涉及范围条件，InnoDB的间隙锁会扩大锁覆盖范围，进一步提升死锁概率。

优化解决方案 #

1. 缩短事务锁持有时间 #

• 拆分事务：将job_instruction_group的插入操作单独提交事务，再执行后续临时表创建和job_instruction更新，减少锁的持有时长。
• 前置查询操作：在事务启动前完成所有临时表的数据查询与准备，避免在事务内长时间占用锁资源。

2. 优化SQL与索引 #

• 精简临时表查询：创建ji_temp1时只查询所需字段（如id）而非*，同时确保job_instruction.job_id有索引，加速查询。
• 添加组合索引：为job_instruction_group创建(job_id, inventory_id, out_bound_status_flow_id)组合索引，为shipment_detail_child.job_instruction_id、shipment_detail.id、shipment_header.id单独添加索引，提升关联查询速度，缩短锁持有时间。

3. 控制锁类型与顺序 #

• 显式锁控制：若插入job_instruction_group前已确认inventory记录存在，可通过SELECT id FROM inventory WHERE id = ? FOR UPDATE提前加X锁，避免后续外键检查的S锁与其他事务的X锁冲突。
• 统一锁顺序：所有涉及inventory和job_instruction相关表的事务，都按照先操作inventory，再操作job_instruction及其关联表的顺序执行，避免循环等待。

4. 应用层重试机制 #

捕获死锁（1213错误）和锁超时（1205错误），在应用层实现自动重试逻辑，死锁属于偶发性冲突，重试通常可以解决问题。

内容的提问来源于stack exchange，提问作者Sheryar Khan