MS SQL Server如何锁定指定OwnerId的记录完成数据复制避免并发冲突

解决方案 #

核心思路是利用现有的ix_foo_OwnerId索引，通过行级范围锁仅锁定OwnerId=57的相关数据，无需锁全表，同时规避逐行查询最大值的性能问题：

BEGIN TRANSACTION;

-- 先获取当前OwnerId=57的最大RecordId，同时加范围锁，阻止其他事务修改/插入OwnerId=57的记录
-- 锁只会作用于OwnerId=57的相关行，不影响其他OwnerId的操作
DECLARE @MaxRecordId INT;
SELECT @MaxRecordId = ISNULL(MAX(OwnerRecordId), 0)
FROM foo WITH (UPDLOCK, HOLDLOCK)
WHERE OwnerId = 57;

-- 批量计算新的OwnerRecordId插入
INSERT INTO foo (OwnerId, SomeColumn, OwnerRecordId)
SELECT 
    57,
    SomeColumn,
    @MaxRecordId + ROW_NUMBER() OVER(ORDER BY Id)
FROM foo
WHERE OwnerId = 16;

COMMIT TRANSACTION;

方案说明 #

• UPDLOCK：给查询到的行加上更新锁，其他事务可以读这些行，但不能获取更新锁、也不能修改/删除这些行，避免多个事务同时计算最大ID的冲突
• HOLDLOCK：会在索引范围上加锁，阻止其他事务插入OwnerId=57的新记录，避免幻读导致的ID重复
• 现有ix_foo_OwnerId索引会让锁只作用于OwnerId=57的相关范围，不会锁全表，其他OwnerId的读写操作完全不受影响
• 用ROW_NUMBER批量计算新ID，避免了每行子查询的性能损耗，适合复制大量记录的场景

适配说明 #

如果使用MySQL数据库，将查询最大ID的语句替换为SELECT @MaxRecordId := IFNULL(MAX(OwnerRecordId), 0) FROM foo WHERE OwnerId = 57 FOR UPDATE;即可实现相同的锁定效果。如果担心现有脏数据导致插入冲突，可以给事务加简单的重试逻辑，出现重复值错误时重试1-2次即可。后续清理完重复数据后，建议补上(OwnerId, OwnerRecordId)的唯一索引，从数据库层面避免数据冲突的可能。

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