ROWLOCK in Stored Procedure with Composite Key in SQL Server

Question

编辑：我有一个带有复合键的表，该表正被部署在多台服务器上的多个Windows服务使用。

列：

UserId (int) [CompositeKey],
CheckinTimestamp (bigint) [CompositeKey],
Status (tinyint)

此表中将连续插入。我希望我的 Windows 服务选择前 10000 行并执行一些处理，同时仅锁定这 10000 行。我使用以下存储过程为此使用 ROWLOCK：

ALTER PROCEDURE LockMonitoringSession
AS
BEGIN
    BEGIN TRANSACTION
    SELECT TOP 10000 * INTO #TempMonitoringSession FROM dbo.MonitoringSession WITH (ROWLOCK) WHERE [Status] = 0 ORDER BY UserId
    DECLARE @UserId INT
    DECLARE @CheckinTimestamp BIGINT
    DECLARE SessionCursor CURSOR FOR SELECT UserId, CheckinTimestamp FROM #TempMonitoringSession
    OPEN SessionCursor
    FETCH NEXT FROM SessionCursor INTO @UserId, @CheckinTimestamp
    WHILE @@FETCH_STATUS = 0 
    BEGIN
        UPDATE dbo.MonitoringSession SET [Status] = 1 WHERE UserId = @UserId AND CheckinTimestamp = @CheckinTimestamp
        FETCH NEXT FROM SessionCursor INTO @UserId, @CheckinTimestamp
    END
    CLOSE SessionCursor
    DEALLOCATE SessionCursor
    SELECT * FROM #TempMonitoringSession
    DROP TABLE #TempMonitoringSession
    COMMIT TRANSACTION  
END

但是，通过这样做，dbo.MonitoringSession将被永久锁定，直到存储过程结束。我不确定我在这里做错了什么。

此存储过程的唯一用途是选择和更新 10000 个没有任何主键的最近行，并确保不会锁定整个表，因为多个 Windows 服务正在访问此表。

提前感谢任何帮助。

Answer 1

（不是答案，但太长而无法评论）

目的描述应该是关于为什么/为什么更新整个表。您的 SP 用于更新所有行，Status=0以设置Status=1 。因此，当您的某个服务决定运行此 SP 时，所有行都将变得不相关。我的意思是，从逻辑上讲，导致状态更改的事件已经发生，您只需要一些时间来物理更改数据库中的它。那么，为什么要让其他服务读取不相关的行呢？好的，可能需要读取可读取的行（未更改） - 但再次不清楚，因为您正在更新整个表。

您可以使用READPAST提示跳过锁定的行，为此您需要行锁。好的，但是即使处理前 N 行，用一个语句更新这 N 行也会比循环遍历这么多行要快得多。您正在手动执行相同的工作。

查看组合 UPDLOCK + READPAST 以处理具有并行进程的同一队列的示例：https://www.mssqltips.com/sqlservertip/1257/processing-data-queues-in-sql-server-with-readpast-and-updlock/

小提示 - CURSOR STATIC, READONLY, FORWARD_ONLY会做与存储到临时表相同的事情。查看STATIC选项：https://msdn.microsoft.com/en-us/library/ms180169.aspx

另一件事是考虑RCSI的建议。这肯定会避免其他服务锁定，但这是一个数据库级别的选项，因此您必须测试所有功能。其中大部分工作原理与以前相同，但某些方案需要测试（并发事务在之前被锁定的情况下不会被锁定）。

我不清楚：

• 10000 占总行数的百分比是多少？
• 是否有聚集索引或这是一个堆？
• 选择和更新的实际执行计划是什么？
• 什么是并发事务：插入或选择？

顺便发现了类似的问题：为什么在更新语句中使用"with（rowlock）"时锁定整个表