MySQL关联列更新库存字段避死锁：更新结果为0的异常排查

多表更新后字段值为0的原因 #

• 关联逻辑不满足行级唯一匹配：之前改写的多表更新语句，主表和子查询仅通过STORE_EXT_ID、INTEGRATION_PARTNER、ORG_ID三个字段关联，这三个字段没有唯一约束，同一筛选条件下存在大量不同产品、不同仓库的独立记录，关联时会形成一对多的笛卡尔积匹配，MySQL不会逐行匹配自身的入库、出库值，反而会随机取交叉行的字段值做赋值，根本无法实现原本的计算逻辑。
• 缺失主键关联维度：子查询没有提取表的主键字段（ORG_ID、PRODUCT_ID、WAREHOUSE_ID）作为关联依据，完全没有办法把当前行的两个库存值对应起来，很容易出现A产品的入库值配到B产品的出库值的混乱情况。
• 隐式转换导致值为0：两个库存字段是varchar类型，当交叉匹配到空值、非数字格式的字符串时，MySQL隐式转换为数值类型会默认得到0，最终把目标入库字段覆盖为0。
• 计算逻辑冗余：原更新公式INBOUND_STOCK_LVL = INBOUND_STOCK_LVL - (INBOUND_STOCK_LVL - OUTBOUND_STOCK_LVL)经过代数化简完全等价于INBOUND_STOCK_LVL = OUTBOUND_STOCK_LVL，多写的减法运算不仅没有意义，还增加了不必要的计算开销。

原简单更新触发死锁的原因 #

• 筛选条件无索引支撑：原表仅在主键(ORG_ID, PRODUCT_ID, WAREHOUSE_ID)上建有索引，原UPDATE语句的筛选条件STORE_EXT_ID、INTEGRATION_PARTNER没有对应索引，执行时会触发全表扫描，对扫描到的所有行加记录锁和间隙锁，锁的作用范围远大于实际需要更新的行，大幅提升锁冲突的概率。
• 加锁顺序不固定：全表扫描时的加锁顺序受数据页分布、并发写入的影响，不同事务的加锁顺序无法保持一致，容易出现事务A等事务B的锁、事务B等事务A的锁的交叉等待场景，最终触发死锁。

前置优化动作 #

先为筛选条件创建覆盖索引，避免全表扫描：

CREATE INDEX idx_stock_filter ON tbl_stock_data (
    STORE_EXT_ID, 
    INTEGRATION_PARTNER, 
    ORG_ID, 
    PRODUCT_ID, 
    WAREHOUSE_ID
);

该索引可以让查询直接通过索引定位到所有需要更新的行，不需要遍历全表，能把锁的作用范围缩小到实际待更新的行，大幅降低锁冲突概率。

千级数据量适用的简单更新写法 #

依托上面创建的索引，直接使用化简后的逻辑更新，不需要多余计算：

UPDATE tbl_stock_data 
SET INBOUND_STOCK_LVL = OUTBOUND_STOCK_LVL
WHERE STORE_EXT_ID = 'STOCK1' 
  AND INTEGRATION_PARTNER = 'fortnox' 
  AND ORG_ID = 'asdsg-23ewfsd-2342'
  -- 可选保护条件：跳过空值记录，减少无效更新
  AND INBOUND_STOCK_LVL IS NOT NULL 
  AND OUTBOUND_STOCK_LVL IS NOT NULL;

万级以上大数据量适用的分批更新写法 #

如果待更新的行超过1万，上面的单条语句还是会因为持锁时间太长出现锁等待，这时候可以采用按主键固定顺序分批更新的方案，从根源上避免死锁：

-- 应用层循环执行该语句，每次处理1000行，直到返回的影响行数为0即可退出
UPDATE tbl_stock_data main_tbl
INNER JOIN (
    -- 按主键升序提取一批待更新的行，控制单批大小
    SELECT ORG_ID, PRODUCT_ID, WAREHOUSE_ID
    FROM tbl_stock_data
    WHERE STORE_EXT_ID = 'STOCK1' 
      AND INTEGRATION_PARTNER = 'fortnox' 
      AND ORG_ID = 'asdsg-23ewfsd-2342'
      -- 用游标记录上一批处理到的最后一个主键值，避免重复扫描已处理的数据
      AND (ORG_ID, PRODUCT_ID, WAREHOUSE_ID) > ('上一批最后一行的ORG_ID', '上一批最后一行的PRODUCT_ID', '上一批最后一行的WAREHOUSE_ID')
    ORDER BY ORG_ID, PRODUCT_ID, WAREHOUSE_ID
    LIMIT 1000
) batch_tbl 
    ON main_tbl.ORG_ID = batch_tbl.ORG_ID 
    AND main_tbl.PRODUCT_ID = batch_tbl.PRODUCT_ID 
    AND main_tbl.WAREHOUSE_ID = batch_tbl.WAREHOUSE_ID
SET main_tbl.INBOUND_STOCK_LVL = main_tbl.OUTBOUND_STOCK_LVL;

该方案的死锁规避逻辑 #

• 所有更新操作严格按照ORG_ID、PRODUCT_ID、WAREHOUSE_ID的固定升序加锁，不同并发事务的加锁顺序完全一致，不会出现交叉等待的死锁场景。
• 每次仅更新不超过1000行数据，锁的持有时间极短，即使有并发请求也不会出现长时间锁等待。
• 关联条件完全基于表的主键字段，实现行级一一匹配，不会出现之前一对多关联导致的字段值错乱、错写为0的问题。

内容的提问来源于stack exchange，提问作者Yasir Janjua