Azure SQL中用UPDLOCK+HOLDLOCK仍存重复插入，如何解决？

你的问题核心在于当前的锁策略没有覆盖到尚未存在的新行，导致多进程场景下出现幻读，最终触发重复插入。

为什么原有方案失效？ #

你使用的UPDLOCK, HOLDLOCK仅对查询返回的现有行加锁，但当多个进程同时处理同一个col1值且当前表中无对应行时，查询不会返回任何行，也就不会加锁。此时其他进程可以插入相同col1的新行，等你的事务执行计数检查时，结果已经不准确，最终导致重复插入。

可行解决方案 #

1. 使用SERIALIZABLE隔离级别

SERIALIZABLE是SQL Server最严格的隔离级别，会在查询的范围上添加锁，阻止其他事务插入符合查询条件的新行，从根源上避免幻读。修改后的SQL代码如下：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION

IF ((SELECT COUNT(*) FROM TableA WHERE col1 = @col1) > @count)
BEGIN
    SELECT 0;
    ROLLBACK TRANSACTION;
    RETURN;
END

INSERT INTO TableA (col1, col2) VALUES (@col1, @col2);
SELECT 1;

COMMIT TRANSACTION;

2. 添加唯一约束作为最后防线

即使锁策略出现疏漏，数据库层面的唯一约束可以直接阻止重复数据插入。由于你的场景中col1可对应多个col2，但同一col1+col2组合应该唯一，因此创建联合唯一索引：

CREATE UNIQUE NONCLUSTERED INDEX IX_TableA_col1_col2 ON TableA (col1, col2);

在C#代码中捕获唯一约束冲突的异常（错误码2601或2627），然后进行重试或返回失败结果，确保数据一致性。

3. 优化锁提示（替代SERIALIZABLE）

如果你不想全局设置隔离级别，可以在查询中使用HOLDLOCK, ROWLOCK, UPDLOCK组合，但这种方式在无现有行时仍可能存在漏洞，仅作为备选方案：

BEGIN TRANSACTION

IF ((SELECT COUNT(*) FROM TableA WITH (HOLDLOCK, ROWLOCK, UPDLOCK) WHERE col1 = @col1) > @count)
BEGIN
    SELECT 0;
    ROLLBACK TRANSACTION;
    RETURN;
END

INSERT INTO TableA (col1, col2) VALUES (@col1, @col2);
SELECT 1;

COMMIT TRANSACTION;

总结 #

• 优先用SERIALIZABLE隔离级别避免幻读，确保计数检查的准确性；
• 添加联合唯一约束作为兜底，从数据库层面杜绝重复；
• 两者结合使用可以最大化保证多进程场景下的数据一致性。

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