SQL Server中如何保障JSON列数据一致性，规避并发更新丢失

这不是使用JSON列存储数据的固有缺陷。你遇到的更新丢失本质是无并发保护的「读-改-写」全量覆盖流程的通用问题，和字段存储类型没有关系：哪怕你用普通字符串列存逗号分隔的ID列表、甚至用独立关联表存订单和商品的发货关系，只要走「读取旧值→内存修改→全量覆盖写回」且不加任何并发控制的逻辑，一样会出现更新丢失。

根据业务场景的冲突概率、逻辑复杂度，可以选以下三种成熟方案规避问题：

• 原子JSON操作（优先推荐）
  主流关系型数据库（MySQL 5.7+、PostgreSQL 9.5+等）都提供了原生JSON修改函数，支持在数据库侧对JSON列做原子修改，完全跳过应用层全量读、全量写的流程，单条更新语句自带行锁保护，执行时串行处理，根本不会出现互相覆盖的问题。
  以追加单个已发货Item ID为例，不需要提前读取旧值，直接执行更新SQL即可：
  MySQL环境写法：

  -- 直接在现有JSON数组尾部追加ID，原子执行
  UPDATE `order` 
  SET sent_items = JSON_ARRAY_APPEND(sent_items, '$', #{itemId})
  WHERE id = #{orderId}
  -- 可选追加判断，避免重复写入同一个Item ID
  AND NOT JSON_CONTAINS(sent_items, CAST(#{itemId} AS JSON))
  

  PostgreSQL环境写法：

  UPDATE "order"
  SET sent_items = sent_items || to_jsonb(#{itemId})
  WHERE id = #{orderId}
  AND NOT sent_items @> to_jsonb(#{itemId})
  

  这个方案性能最好，不需要额外加字段，也不需要写重试逻辑，能覆盖绝大多数简单JSON修改场景。
• 乐观并发校验
  如果你的JSON修改逻辑比较复杂（比如要根据多个业务字段做判断、批量修改JSON内多个节点），没法直接用数据库内置函数完成修改，可以给Order表加version整型版本字段，更新时必须匹配之前读取到的版本号才允许写入。
  操作流程：
  1. 读取订单记录时，同步拿到当前version值和sent_items的内容
  2. 在应用层完成JSON内容的业务修改
  3. 执行更新时带上版本匹配条件：

  UPDATE `order`
  SET sent_items = #{newSentItemsJson}, version = version + 1
  WHERE id = #{orderId} AND version = #{preReadVersion}
  

  4. 检查SQL影响行数，如果返回0说明期间已经有其他请求修改过这条记录，重新读取最新值重复上述流程即可。
     这个方案适合写冲突概率不高、修改逻辑复杂的场景，不会长时间占用数据库锁。
• 悲观排他锁
  如果业务场景写冲突概率极高，用乐观锁会导致大量重试消耗资源，可以在读取待修改记录时直接加排他行锁，保证同一时间只有一个事务能修改同一条订单记录：

  -- 开启事务后先执行加锁查询，其他事务执行相同语句会被阻塞直到当前事务提交
  SELECT * FROM `order` WHERE id = #{orderId} FOR UPDATE
  

  拿到锁之后再完成读值、修改JSON、写回的流程，事务提交后锁会自动释放。注意使用时要尽量缩短事务长度，避免长事务阻塞正常业务请求。

不要把并发更新丢失的问题归因为JSON列本身的设计缺陷。只要放弃无保护的全量覆盖写逻辑，不管用原子操作、乐观锁还是悲观锁做并发控制，JSON列的数据一致性和普通数据列没有任何区别。反过来如果不加任何并发控制，就算用独立关联表存储发货关系，一样会出现数据覆盖导致的不一致。

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