sql - 使用 TOP 和 WHERE 更新一行

Question

我计划为仅使用 SQL Server 表的用户实现一种工作队列。一行将一次仅分配给一个用户。我打算去一个共同的 UPDATE SET .. WHERE .. 路线，就像这个问题中提出的那样:

https://stackoverflow.com/a/9241466

这里是引用的相关部分:

;WITH CTE AS 
( 
    SELECT TOP 100 * 
    FROM T1      
    ORDER BY F2 
) 
UPDATE CTE SET F1='foo'

为确保实际上只有一个用户可以编辑一行，我将包含一个附加属性 UserId，并检查 WHERE 子句中的 NULL。

;WITH CTE AS 
( 
    SELECT TOP 5 * 
    FROM T1
    WHERE UserId IS NULL      
    ORDER BY F2 
) 
UPDATE CTE SET UserId = @userid

语句完成后，我将从数据库中SELECT受影响的行。

但是，我可能想在我的 WHERE 子句中包括一些额外的属性，以及一些额外的排序。

如果包含 WHERE，

UPDATE 将锁定该行，但是当 WHERE 包含在 CTE？

我的问题是，换句话说，UPDATE 是否仍会锁定行，或者两个并发运行的语句是否有可能由于竞争问题而覆盖 UserId？

Answer 1

我创建了以下示例数据:

DROP TABLE IF EXISTS [dbo].[StackOverflow];

CREATE TABLE [dbo].[StackOverflow]
(
    [ID] INT IDENTITY(1, 1) PRIMARY KEY
   ,[UserID] VARCHAR(12) 
);

INSERT INTO [dbo].[StackOverflow]
VALUES (DEFAULT)
      ,(DEFAULT)
      ,(DEFAULT)
      ,(DEFAULT)
      ,(DEFAULT);

SELECT [ID]  
      ,[UserID]
FROM [dbo].[StackOverflow];

然后，在两个单独的查询窗口中，我运行了以下代码:

BEGIN TRANSACTION;

WITH CTE AS 
( 
    SELECT TOP 2 * 
    FROM [dbo].[StackOverflow]
    WHERE [UserID] IS NULL      
    ORDER BY [ID] 
) 
UPDATE CTE 
SET UserId = 1; -- in the second one change the `userid = 2`


--COMMIT TRANSACTION;

因此，我正在模拟两个尝试执行更新的连接。由于我们根据相同的标准进行排序，因此我们可以看到，第二个查询正在等待第一个查询结束，以便正确过滤数据。

SELECT [request_session_id]
      ,[resource_type]
      ,[resource_description]
      ,[resource_associated_entity_id]
      ,[request_mode]
      ,[request_type]
      ,[request_status]
FROM [sys].[dm_tran_locks]
WHERE [resource_database_id] = DB_ID()
ORDER BY [request_session_id]
        ,[resource_type];

第二个正在等待对第一行的授权，因为它正在更新。如果我们提交第一个查询，现在您可以看到第二个查询已读取数据并授予对其他行的锁定。

现在，在表中我们有:

因此，在这种情况下，您无需担心两个语句会覆盖数据。