顾客A店下单B店发货，库存却对不上：多门店共享库存的锁定、调拨在途与对账实战

# inventory_service.py —— 跨店下单的原子锁定（以履约门店为维度）
def lock_for_order(sku_id, fulfill_store_id, qty, order_no, ttl_sec=900):
    key = f"stock:store:{fulfill_store_id}:{sku_id}"
    lock_key = f"stock:lock:{order_no}"
    # 原子脚本：剩余可售 = 实物 - 已锁定，足够才占用并登记锁定流水
    lua = """
    local stock = tonumber(redis.call('hget', KEYS[1], 'onhand') or '0')
    local locked = tonumber(redis.call('hget', KEYS[1], 'locked') or '0')
    local qty = tonumber(ARGV[1])
    if stock - locked >= qty then
        redis.call('hincrby', KEYS[1], 'locked', qty)
        redis.call('set', KEYS[2], qty, 'EX', ARGV[2])
        return 1
    end
    return 0
    """
    ok = redis.eval(lua, 2, key, lock_key, qty, ttl_sec)
    if not ok:
        raise OutOfStockError("该门店可售库存不足")
    return "LOCKED"

-- 调拨单状态：DRAFT -> OUT(已出库在途) -> IN(到货待上架) -> DONE / ROLLBACK
CREATE TABLE stock_transfer (
  id           BIGINT PRIMARY KEY AUTO_INCREMENT,
  sku_id       BIGINT NOT NULL,
  from_store   BIGINT NOT NULL,
  to_store     BIGINT NOT NULL,
  qty          INT NOT NULL,
  status       VARCHAR(12) NOT NULL DEFAULT 'DRAFT',
  out_at       DATETIME NULL,
  in_at        DATETIME NULL,
  real_in_qty  INT NULL,          -- 实际到货数，可能与qty不符
  KEY idx_status (status)
);

-- B店出库：实物冻结为"在途"，B店可售立即减少，A店实物暂不增加
UPDATE store_stock
SET onhand = onhand - #{qty}, in_transit_out = in_transit_out + #{qty}
WHERE store_id = #{fromStore} AND sku_id = #{skuId} AND onhand >= #{qty};

def confirm_arrival(transfer_id, real_in_qty):
    t = db.get(stock_transfer, transfer_id)
    # A店按实收入库
    db.execute("UPDATE store_stock SET onhand=onhand+%s WHERE store_id=%s AND sku_id=%s",
               (real_in_qty, t.to_store, t.sku_id))
    # B店核销在途
    db.execute("UPDATE store_stock SET in_transit_out=in_transit_out-%s WHERE store_id=%s AND sku_id=%s",
               (t.qty, t.from_store, t.sku_id))
    if real_in_qty < t.qty:
        # 少货部分不进A店库存，登记差异待人工处理，状态置ROLLBACK而非DONE
        db.execute("UPDATE stock_transfer SET status='ROLLBACK', real_in_qty=%s WHERE id=%s",
                   (real_in_qty, transfer_id))
        raise TransferDiffError(t.qty - real_in_qty)
    db.execute("UPDATE stock_transfer SET status='DONE', real_in_qty=%s, in_at=NOW() WHERE id=%s",
               (real_in_qty, transfer_id))

def reconcile(store_id, sku_id):
    # 流水重放得到应有库存：期初 + 入库 + 调拨入 - 销售 - 调拨出 - 盘亏
    ledger = db.fetchone("""
      SELECT begin_qty
           + SUM(CASE WHEN type IN('SALE_IN','TRANSFER_IN') THEN qty ELSE 0 END)
           - SUM(CASE WHEN type IN('SALE_OUT','TRANSFER_OUT','LOSS') THEN qty ELSE 0 END) AS expect
      FROM stock_flow
      WHERE store_id=%s AND sku_id=%s
    """, (store_id, sku_id))
    actual = db.fetchone("SELECT onhand FROM store_stock WHERE store_id=%s AND sku_id=%s",
                         (store_id, sku_id))
    if ledger.expect != actual.onhand:
        # 差异不直接改库存，先落差异单并告警，由值班确认后补偿
        db.execute("INSERT INTO stock_diff(store_id,sku_id,expect_qty,actual_qty,created_at) "
                   "VALUES(%s,%s,%s,%s,NOW())",
                   (store_id, sku_id, ledger.expect, actual.onhand))
        alert(f"库存差异 门店{store_id} SKU{sku_id} 应{ledger.expect} 实{actual.onhand}")