如何用UPDLOCK替代TABLOCKX实现用户记录的原子性检查与新增？

我的数据表如下：

输入参数为用户ID列表：[ 3, 4, 5 ]，需检查这些用户ID在最近10分钟内是否存在对应记录：

• 若存在，返回这些在近10分钟内有记录的用户ID；
• 若不存在，将整个输入列表的用户记录新增到数据表中。

以下代码可正常运行：

public IEnumerable<int> CheckAndAddUsers(IEnumerable<int> userIds)
{
    var now = DateTime.Now;
    var lastAttemptDateThreshold = now.AddMinutes(-10);

    using (var context = new dbContext())
    {
        using (var transaction = context.Database.BeginTransaction(System.Data.IsolationLevel.Serializable))
        {
            try
            {
                var userIdsString = string.Join(",", userIds);

                var usersThatCannotBeSent = context.Database.SqlQuery<int>($@"
                        SELECT user_id
                        FROM UserSentAttempts WITH (TABLOCKX, HOLDLOCK)
                        WHERE 
                            user_id IN ({userIdsString})
                            AND last_activity_time > @LastAttemptDateThreshold", new SqlParameter("@LastAttemptDateThreshold", lastAttemptDateThreshold)).ToList();

                if (usersThatCannotBeSent.Any())
                {
                    transaction.Rollback();
                    return usersThatCannotBeSent;
                }
                else
                {
                    var usersToAdd = userIds.Select(x => new UserSentAttempt()
                    {
                        UserId = x,
                        LastActivityTime = now
                    });
    
                    context.UserSentAttempts.AddRange(usersToAdd);
                    context.SaveChanges();
                        
                    transaction.Commit();
    
                    return new List<int>();
                }
            }
            catch (Exception)
            {
                transaction.Rollback();
                throw;
            }
        }
    }
}

（注：原代码缺失方法名，此处补充合理名称CheckAndAddUsers）

上述代码使用了TABLOCKX锁，我想了解是否可以改用UPDLOCK实现相同功能。测试发现当查询的用户不存在时，UPDLOCK无法锁定任何资源，无法保证操作的原子性。请问是否存在比TABLOCKX更优的实现方案？

为什么UPDLOCK不可行？ #

UPDLOCK仅锁定查询返回的现有行，当输入的用户ID不存在时，没有行被锁定，此时其他并发请求可同时插入相同用户ID，导致重复插入或业务逻辑冲突，无法保证原子性。

更优替代方案 #

方案1：虚拟行+UPDLOCK/HOLDLOCK

在表中预先插入一条虚拟行（比如user_id = 0），查询时将其纳入IN条件，即使目标用户ID不存在，也会锁定这条虚拟行，避免并发插入：

SELECT user_id
FROM UserSentAttempts WITH (UPDLOCK, HOLDLOCK)
WHERE 
    user_id IN ({userIdsString}, 0)
    AND last_activity_time > @LastAttemptDateThreshold

该方式仅锁定单条虚拟行，锁粒度远小于TABLOCKX的全表锁，并发性能更好。

方案2：MERGE原子化操作

使用数据库原生MERGE语句，在数据库层面完成检查与插入的原子操作，无需手动管理事务和锁提示，同时解决原代码的SQL注入风险：

public IEnumerable<int> CheckAndAddUsers(IEnumerable<int> userIds)
{
    var now = DateTime.Now;
    var lastAttemptDateThreshold = now.AddMinutes(-10);

    using (var context = new dbContext())
    {
        // 构造表值参数
        var tableParam = new DataTable();
        tableParam.Columns.Add("UserId", typeof(int));
        foreach (var id in userIds)
        {
            tableParam.Rows.Add(id);
        }

        var tvp = new SqlParameter("@UserIds", SqlDbType.Structured)
        {
            TypeName = "dbo.UserIdList",
            Value = tableParam
        };

        var thresholdParam = new SqlParameter("@LastAttemptDateThreshold", lastAttemptDateThreshold);

        // 执行MERGE并返回存在的用户ID
        var existingUsers = context.Database.SqlQuery<int>(@"
            MERGE INTO UserSentAttempts AS Target
            USING @UserIds AS Source ON Target.user_id = Source.UserId
            WHEN NOT MATCHED AND NOT EXISTS (
                SELECT 1 FROM UserSentAttempts 
                WHERE user_id = Source.UserId AND last_activity_time > @LastAttemptDateThreshold
            )
            THEN INSERT (user_id, last_activity_time) VALUES (Source.UserId, GETDATE())
            OUTPUT CASE WHEN Target.user_id IS NOT NULL AND Target.last_activity_time > @LastAttemptDateThreshold 
                        THEN Source.UserId ELSE NULL END;
        ", tvp, thresholdParam).Where(id => id != null).ToList();

        return existingUsers;
    }
}

需先创建对应的表值类型：

CREATE TYPE dbo.UserIdList AS TABLE (UserId int);

此方案将检查与插入合并为原子操作，性能与并发表现最优，同时规避SQL注入风险。

方案3：范围锁+ROWLOCK/UPDLOCK/HOLDLOCK

若不想使用虚拟行，可利用范围锁。假设user_id是主键，构造范围条件确保无匹配行时锁定对应键范围：

SELECT user_id
FROM UserSentAttempts WITH (ROWLOCK, UPDLOCK, HOLDLOCK)
WHERE 
    user_id IN ({userIdsString})
    AND last_activity_time > @LastAttemptDateThreshold
UNION ALL
SELECT 0 WHERE NOT EXISTS (SELECT 1 FROM UserSentAttempts WHERE user_id IN ({userIdsString}))

该查询在无匹配行时返回虚拟记录，锁定对应键范围，阻止其他会话插入相同用户ID。

总结 #

TABLOCKX是粗暴的全表锁，严重影响并发性能。优先推荐方案2的MERGE语句，原子性有保障，还解决了原代码的SQL注入问题；若无法使用表值参数，方案1的虚拟行锁定是次优选择，锁粒度远小于表锁。

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