UPDATING a ROW with TOP and WHERE

Question

I am planning to implement a kind-of work queue for users using only a SQL Server table. A row will be assigned to only one user at a time. I was planning to go a common UPDATE SET .. WHERE .. route, like has been proposed in this question:

https://stackoverflow.com/a/9241466

Here is the relevant part, quoted:

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

To ensure only one user actually gets to edit a row, I will include an additional property, UserId, and check for NULL in a WHERE clause.

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

After statement completion, I will SELECT the affected rows from the database.

However, I might want to include some additional properties in my WHERE clause, and also some additional ordering.

UPDATE will lock the row if the WHERE is included, but is this still the case for when the WHERE is included in the CTE?

My question is, differently phrased, will UPDATE still lock the rows or is there a chance that two concurrently run statements will overwrite the UserId because of a racing issue?

Possibly relevant: [UPDATE + WITH (ROWLOCK) + CTE](https://stackoverflow.com/questions/27177451/update-with-rowlock-cte) — Tim Biegeleisen, Oct 10 '19 at 05:56

score 0 · Accepted Answer · answered Oct 10 '19 at 06:18

I have create the following sample data:

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];

Then, in two separates query windows I have run the following code:

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;

So, I am simulating two connections trying to perform the updates. Since, we are ordering on the same criteria, we can see, that the second query is waiting the first to be over in order to correctly filter the data.

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];

The second one is waiting a grant to the first row as it is being updated. If we commit the first query, now you can see that the second one has read the data and granted locks to other rows.

Now, in the table we have:

So, in such case you do not need to worry about two statements overwriting the data.

UPDATING a ROW with TOP and WHERE

1 Answers1