WITH (UPDLOCK, SERIALIZABLE)防重复插入原理？为何第二条INSERT静默失败？

表结构回顾 #

你的Test表定义如下（注意缺少UniqueId字段的唯一约束）：

CREATE TABLE [dbo].[Test](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [UniqueId] [uniqueidentifier] NOT NULL,
    [Code] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

UPDLOCK + SERIALIZABLE的锁机制原理 #

要理解它如何避免重复插入，得拆解两个选项的核心作用：

• SERIALIZABLE隔离级别：这是SQL Server的最高隔离级别，会对查询覆盖的范围加范围锁（Range Lock）。当执行SELECT ... WHERE UniqueId = @value且无匹配记录时，它会锁定“不存在该UniqueId的间隙”，阻止其他事务在这个间隙插入符合条件的记录，彻底杜绝幻读问题。
• UPDLOCK（更新锁）：默认SELECT语句加共享锁（S锁），多个事务可同时持有。UPDLOCK会将共享锁替换为更新锁（U锁），U锁的特性是同一时间仅能有一个事务持有同一资源的U锁，其他事务只能加S锁，无法加U锁或排他锁（X锁），直接避免了多事务同时执行“检查-插入”的竞态问题。

两者结合后，第一个执行“检查-插入”的事务会在目标UniqueId的间隙加RangeS-U锁，阻塞其他事务执行相同的SELECT检查，直到自身提交或回滚。

第二段INSERT“静默失败”的原因 #

你遇到的情况本质上不是INSERT执行报错，而是第二个事务的INSERT根本没被触发，具体流程如下：

1. 第一个事务启动后，执行IF NOT EXISTS (SELECT 1 FROM Test WITH (UPDLOCK, SERIALIZABLE) WHERE UniqueId = @A)，因无匹配记录，在UniqueId=@A的间隙加RangeS-U锁，随后插入记录A并提交事务。
2. 第二个事务几乎同时执行相同检查，但第一个事务的RangeS-U锁会阻塞它的SELECT操作，使其无法继续执行后续逻辑。
3. 直到第一个事务提交、锁释放后，第二个事务的SELECT才会继续执行——此时表中已存在UniqueId=@A的记录，IF NOT EXISTS条件不成立，因此跳过INSERT操作，最终表现为“静默失败”。

关键补充：必须添加UniqueId唯一约束 #

虽然UPDLOCK+SERIALIZABLE能避免竞态下的重复插入，但这依赖事务的执行顺序。如果业务要求UniqueId必须唯一，一定要给该字段添加唯一约束：

ALTER TABLE [dbo].[Test] ADD CONSTRAINT [UQ_Test_UniqueId] UNIQUE NONCLUSTERED ([UniqueId] ASC);

这样即使锁机制出现意外（比如事务逻辑写错），数据库层面会直接抛出唯一键冲突错误，从根源杜绝重复记录，同时还能优化锁的粒度（唯一约束会让锁精准定位到具体值，而非范围）。

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