如何在SQL Server中锁定尚未存在的特定行？（API限流表场景）

嘿，针对你这个多线程管理API限流表的问题，核心是要搞定竞态条件——比如两个线程同时检测到某UserId+EndPointId的行不存在，然后都往表里插，直接触发主键冲突；或者一个线程在更新时，另一个线程插了新行，导致数据乱掉。下面是几个SQL Server里靠谱的方案，专门解决“锁定尚未存在的特定行”这种需求：

1. 用UPDLOCK+HOLDLOCK实现原子检查与操作 #

这种方法通过事务结合锁提示，先尝试读取目标行（哪怕行还不存在），同时锁定对应的键范围，直接堵死其他线程插入相同主键行的可能。

示例代码（插入或更新限流规则） #

BEGIN TRANSACTION;

-- 先锁定目标行的键范围（哪怕行不存在也会锁）
SELECT * 
FROM [dbo].[RateLimit] WITH (UPDLOCK, HOLDLOCK)
WHERE [UserId] = @TargetUserId AND [EndPointId] = @TargetEndPointId;

-- 没查到行就插入，查到了就更新
IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO [dbo].[RateLimit] ([UserId], [EndPointId], [AllowedRequests], [ResetDateUtc])
    VALUES (@TargetUserId, @TargetEndPointId, @InitialAllowedCount, @ResetTimeUtc);
END
ELSE
BEGIN
    UPDATE [dbo].[RateLimit]
    SET [AllowedRequests] = @NewAllowedCount, [ResetDateUtc] = @NewResetTimeUtc
    WHERE [UserId] = @TargetUserId AND [EndPointId] = @TargetEndPointId;
END

COMMIT TRANSACTION;

锁提示为啥这么用？ #

• UPDLOCK：拿的是更新锁，不是共享锁，避免其他线程也拿共享锁后想升级成更新锁，最后搞出死锁。
• HOLDLOCK：相当于把隔离级别设成SERIALIZABLE，会锁定查询的键范围，其他线程根本插不进符合这个主键组合的新行——这正是你要的“锁定不存在的特定行”的核心逻辑。

2. 用MERGE语句一步搞定原子操作 #

MERGE是SQL Server原生的原子操作，一个语句就能完成“匹配到行就更新，没匹配到就插入”，底层自动处理锁的问题，代码还更简洁。

示例代码 #

MERGE INTO [dbo].[RateLimit] AS Target
-- 把要操作的参数当成临时数据源
USING (VALUES (@TargetUserId, @TargetEndPointId, @NewAllowedCount, @NewResetTimeUtc)) 
    AS Source ([UserId], [EndPointId], [AllowedRequests], [ResetDateUtc])
ON Target.[UserId] = Source.[UserId] AND Target.[EndPointId] = Source.[EndPointId]
-- 匹配到行就更新参数
WHEN MATCHED THEN
    UPDATE SET 
        Target.[AllowedRequests] = Source.[AllowedRequests],
        Target.[ResetDateUtc] = Source.[ResetDateUtc]
-- 没匹配到就插入新记录
WHEN NOT MATCHED THEN
    INSERT ([UserId], [EndPointId], [AllowedRequests], [ResetDateUtc])
    VALUES (Source.[UserId], Source.[EndPointId], Source.[AllowedRequests], Source.[ResetDateUtc]);

优势 #

• 不用手动处理@@ROWCOUNT的分支逻辑，代码更清爽。
• 原生支持原子性，多线程下不会出现重复插入的情况，SQL Server会自动帮你控好锁。

3. 针对限流扣减的特殊业务场景（常见需求） #

如果你的业务是每次请求要扣减AllowedRequests，还要检查是否过期（ResetDateUtc是否早于当前UTC时间），可以结合上面的锁机制，确保整个扣减流程是原子的：

BEGIN TRANSACTION;

-- 先锁定目标行的键范围
SELECT * 
FROM [dbo].[RateLimit] WITH (UPDLOCK, HOLDLOCK)
WHERE [UserId] = @CurrentUserId AND [EndPointId] = @CurrentEndPointId;

IF @@ROWCOUNT = 0
BEGIN
    -- 第一次请求，初始化限流记录
    INSERT INTO [dbo].[RateLimit] ([UserId], [EndPointId], [AllowedRequests], [ResetDateUtc])
    VALUES (@CurrentUserId, @CurrentEndPointId, @MaxAllowedRequests, DATEADD(MINUTE, @ResetMinutes, GETUTCDATE()));
    -- 返回扣减后的剩余请求数
    SELECT @MaxAllowedRequests - 1 AS RemainingRequests;
END
ELSE
BEGIN
    -- 过期就重置请求数+更新过期时间，没过期就直接扣减
    UPDATE [dbo].[RateLimit]
    SET 
        [AllowedRequests] = CASE 
            WHEN [ResetDateUtc] < GETUTCDATE() THEN @MaxAllowedRequests - 1
            ELSE [AllowedRequests] - 1
        END,
        [ResetDateUtc] = CASE 
            WHEN [ResetDateUtc] < GETUTCDATE() THEN DATEADD(MINUTE, @ResetMinutes, GETUTCDATE())
            ELSE [ResetDateUtc]
        END
    WHERE [UserId] = @CurrentUserId AND [EndPointId] = @CurrentEndPointId;

    -- 返回最新的剩余请求数
    SELECT [AllowedRequests] AS RemainingRequests
    FROM [dbo].[RateLimit]
    WHERE [UserId] = @CurrentUserId AND [EndPointId] = @CurrentEndPointId;
END

COMMIT TRANSACTION;

几个关键提醒 #

• 所有操作一定要放在显式事务里执行，哪怕用MERGE，显式事务能更好地控制锁的生命周期和隔离级别。
• 别用NOLOCK或者READ UNCOMMITTED隔离级别，会导致脏读，直接破坏数据一致性。
• 你已经设了复合主键(UserId, EndPointId)，这个主键本身就是高效的索引，能确保上面的查询不会全表扫描，锁的范围也不会过大，性能不会有问题。

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