使用内存优化表时如何避免更新操作的数据丢失

一、重新启用SNAPSHOT隔离（内存优化表支持该级别） #

内存优化表并非完全不支持SNAPSHOT隔离，只需额外配置即可恢复原有行为：

• 确保数据库已开启ALLOW_SNAPSHOT_ISOLATION（此前使用SNAPSHOT时应该已经配置）
• 为内存优化表显式启用SNAPSHOT隔离：

  -- 修改现有内存优化表
  ALTER TABLE Warehouse.LocationEntity 
  SET (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
  WITH (SNAPSHOT_ISOLATION = ON);
  
  -- 或者创建新表时直接声明
  CREATE TABLE Warehouse.LocationEntity (
      Id BIGINT PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),
      -- 其他字段定义
  ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA, SNAPSHOT_ISOLATION = ON);
  

• 在事务开始前设置隔离级别：SET TRANSACTION ISOLATION LEVEL SNAPSHOT;，此时当事务尝试更新已被其他事务修改的记录时，会直接抛出更新冲突异常，阻止脏更新，和之前的行为完全一致。

二、避免重复代码的条件校验方案 #

如果不想切换全局隔离级别，可以通过封装逻辑或锁定机制解决：

1. 封装筛选逻辑为内联表值函数（TVF） #

把筛选Id的复杂条件封装成可重用的函数，所有需要校验的地方直接调用即可：

CREATE FUNCTION Warehouse.GetLocationIdsToUpdate()
RETURNS TABLE
AS
RETURN (
    SELECT Id FROM Warehouse.LocationEntity 
    WHERE ..... -- 原复杂条件、关联逻辑
);

后续更新时同时关联表变量和这个函数，确保只有当前仍满足条件的记录才会被更新：

DECLARE @IdLocationsToUpdate TABLE (Id BIGINT PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000));

INSERT INTO @IdLocationsToUpdate (Id)
SELECT Id FROM Warehouse.GetLocationIdsToUpdate();

UPDATE l
SET -- 待更新字段
FROM Warehouse.LocationEntity l
INNER JOIN @IdLocationsToUpdate lu ON lu.Id = l.Id
INNER JOIN Warehouse.GetLocationIdsToUpdate() f ON f.Id = l.Id;

2. 查询阶段锁定目标记录 #

在首次筛选Id时添加UPDLOCK, HOLDLOCK提示，直接锁定目标记录，直到当前事务结束，避免其他事务修改：

DECLARE @IdLocationsToUpdate TABLE (Id BIGINT);

INSERT INTO @IdLocationsToUpdate (Id)
SELECT Id FROM Warehouse.LocationEntity WITH (UPDLOCK, HOLDLOCK)
WHERE ..... -- 原复杂条件、关联逻辑

-- 后续更新直接使用表变量即可，锁已持有
UPDATE l
SET -- 待更新字段
FROM Warehouse.LocationEntity l
INNER JOIN @IdLocationsToUpdate lu ON lu.Id = l.Id;

这种方式适合并发吞吐量要求不是极高的场景，能彻底避免并发修改问题。

三、利用内存优化表原生乐观并发控制 #

内存优化表自带版本跟踪机制，可在更新语句中添加WITH (SNAPSHOT)提示，强制该语句使用SNAPSHOT隔离：

UPDATE l WITH (SNAPSHOT)
SET -- 待更新字段
FROM Warehouse.LocationEntity l
INNER JOIN @IdLocationsToUpdate lu ON lu.Id = l.Id;

如果记录已被其他事务修改，该语句会返回0行受影响，此时可捕获结果并抛出异常或回滚事务，模拟原SNAPSHOT隔离的冲突阻止行为。

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