多服务场景下更新数据时锁定两张表避免重复操作的最优方案

现有方案合理性 #

你提出的基于唯一键的分布式锁思路本质是数据库级乐观锁的经典实现，逻辑上可以满足多实例互斥的需求，且锁粒度为item_id级，不同物料的更新操作可以并行执行，不会出现全局锁的性能瓶颈，核心思路是合理的。

现有方案的潜在问题 #

• 查插竞态问题：如果你采用「先查询item_id是否存在、不存在再插入」的执行逻辑，两个并发请求可能同时查到同一个item_id不存在，同时发起插入请求，最终其中一个会触发唯一键冲突报错，需要额外处理异常逻辑，否则会导致正常请求执行失败。
• 锁永久残留风险：如果函数执行过程中服务器宕机、程序意外终止，没有执行最终的删除锁记录操作，对应的item_id会被永久锁住，后续所有更新操作都无法执行。
• 批量更新死锁问题：如果单次更新涉及多个item_id，不同实例插入锁记录的顺序不一致时会触发死锁，比如实例A按id1→id2的顺序插锁，实例B按id2→id1的顺序插锁，两边各持有一个锁等待对方释放，永远无法执行成功。
• 误删锁风险：如果锁没有归属标识，当你的函数执行时间超过预估时长，锁被其他请求抢占后，原执行完成的请求可能会误删其他实例持有的锁，导致互斥失效。

优化实现方案 #

方案1：优化现有锁表方案（兼容性最好） #

在现有思路基础上做少量改造即可解决所有问题：

1. 去掉查询锁是否存在的步骤，直接执行插入锁记录的操作，捕获唯一键冲突异常即代表抢锁失败，直接终止或等待重试即可，从根源避免查插竞态。
2. 锁表新增owner_id（字符串类型，插入时生成随机唯一值标识当前请求）、expire_time（时间类型，插入时设为当前时间+函数最大执行时长，比如60s）两个字段，解决锁残留和误删问题：即使程序异常终止，锁到期后自动失效，删除锁时仅删除owner_id匹配的记录即可。
3. 批量更新多个item_id时，先将所有item_id按数值大小排序，所有实例都按照统一顺序插入锁记录，彻底避免死锁。

参考锁表结构：

CREATE TABLE `item_update_lock` (
  `item_id` bigint NOT NULL PRIMARY KEY,
  `owner_id` varchar(64) NOT NULL,
  `expire_time` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

方案2：使用数据库行级悲观锁（更简单，无需额外维护锁表） #

如果你的数据库使用支持事务的引擎（比如InnoDB），可以直接利用数据库自带的行锁实现互斥，不需要额外建锁表：
在执行updateValues逻辑时，首先开启事务，执行语句SELECT * FROM total_value_table WHERE item_id = ? FOR UPDATE对对应item_id的行加排他锁，其他事务要修改该行时会自动阻塞，直到当前事务提交/回滚后自动释放锁，天然没有锁残留问题。

方案3：原子更新（最优，无需额外加锁） #

如果你的同步逻辑没有复杂的业务校验，可以直接将计算逻辑合并到UPDATE语句中，利用数据库单语句的原子性彻底避免并发问题：

UPDATE total_value_table t
SET t.total = t.total + (
  SELECT COALESCE(SUM(incr.value), 0) FROM increment_value_table incr WHERE incr.item_id = ? AND incr.sync_status = 0
) - (
  SELECT COALESCE(SUM(decr.value), 0) FROM decrement_value_table decr WHERE decr.item_id = ? AND decr.sync_status = 0
)
WHERE t.item_id = ?;

执行完更新后再批量标记增量/减量记录为已同步即可，单UPDATE语句是原子操作，数据库会自动加行锁，完全不需要额外实现互斥逻辑，性能和可靠性都是最高的。

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