Lock row for reading on SQL Server?

Question

In the bit of SQL down I would like to get and delete the next row of a table matching specific criteria. However, I want to prevent other services executing the same bit of SQL to return the same row. I was thinking about transactions or row locks but can't see how either one would help me.

DECLARE @tblTempRow TABLE(intUserID int, intBlobID int)

-- Get the next match and remember in temp table. I want to prevent that other processes return the same row.
INSERT INTO @tblTempRow(intUserID, intBlobID)
SELECT TOP 1 intUserID, intBlobID FROM Schedule WHERE intScheduleType = @intScheduleType

-- Delete if requested.
IF(@intDeleteAfterGet = 1)
BEGIN
       DELETE FROM
        Schedule
    WHERE
        intUserID = (SELECT intUserID FROM @tblTempRow)
        AND intBlobID = (SELECT intBlobID FROM @tblTempRow)
                AND intScheduleType = @intScheduleType
END

-- Return the temp table.
SELECT intUserID, @intScheduleType, intBlobID FROM @tblTempRow

score 3 · Answer 1 · edited May 23 '17 at 11:51

You can use ROWLOCK, UPDLOCK, READPAST to use a table as a queue (which is what you are doing)

SQL Server Process Queue Race Condition

DECLARE @tblTempRow TABLE(intUserID int, intBlobID int)

--no rollback needed now. will automatically roll back
SET XACT_ABORT ON

-- to span select and delete
BEGIN TRANSACTION

-- Get the next match and remember in temp table. I want to prevent that other processes return the same row.
INSERT INTO @tblTempRow(intUserID, intBlobID)
SELECT TOP 1 intUserID, intBlobID
--for hints, see link above
FROM Schedule WITH (ROWLOCK, READPAST, UPDLOCK)
WHERE intScheduleType = @intScheduleType

-- Delete if requested.
IF(@intDeleteAfterGet = 1)
BEGIN
       DELETE 
        Schedule S 
    WHERE   --yes, more elegant
        EXISTS (SELECT * FROM
           @tblTempRow T
           WHERE
              S.intUserID  = T.intUserID AND
              S.intBlobID = T.intBlobID)
         AND
         S.intScheduleType = @intScheduleType
END
COMMIT TRANSACTION

-- Return the temp table.
SELECT intUserID, @intScheduleType, intBlobID FROM @tblTempRow

Thanks. Can you comment your changes a bit? WHat does "XACT_ABORT" and why exactly those three query hints (ROWLOCK, READPAST, UPDLOCK)? And why did you change the DELETE statement? Is it just a more elegant way to write what I did? — Krumelur, Mar 07 '11 at 13:57

score 3 · Accepted Answer · answered Mar 07 '11 at 13:56

3

You may be better in this case to issue a delete directly, and use the OUTPUT clause to populate your table. That should manage the locks for you automatically. TODO: deal with no matching rows, but you have that issue already.

DECLARE @tblTempRow TABLE(intUserID int, intBlobID int)

IF(@intDeleteAfterGet = 1)
begin
    DELETE TOP 1 FROM Schedule WITH (READPAST) WHERE intScheduleType = @intScheduleType
    OUTPUT deleted.intUserID,deleted.intBlobID INTO @tblTempRow
end
else
begin
    INSERT INTO @tblTempRow(intUserID, intBlobID)
    SELECT TOP 1 intUserID, intBlobID FROM Schedule WHERE intScheduleType = @intScheduleType
end

-- Return the temp table.
SELECT intUserID, @intScheduleType, intBlobID FROM @tblTempRow

answered Mar 07 '11 at 13:56

Damien_The_Unbeliever

234,701
27
340
448

What is the READPAST query hint? – Krumelur Mar 07 '11 at 14:00
This is the most elegant way to do it I think. Thank You! (btw: your syntax is alightly wrong. It needs to be in the order "DELETE TOP(1) ... WITH() ... OUTPUT ... WHERE". I also could ditch the temp table as OUTPUT generates a valid result set. – Krumelur Mar 07 '11 at 14:14
@Krumelur - READPAST says that if a row is locked, to ignore it. So if you have two connections running this code simultaneously, one will lock and return the "first" row, and the second will ignore that and select the "second" row - otherwise, the second connection would wait for the lock (and subsequent delete) to occur before it could get the "second" row. "first" and "second" in quotes because there's no ORDER BY clause in this query. – Damien_The_Unbeliever Mar 07 '11 at 14:43
For the delete, you can use OUTPUT to return a result set on its own, no @tblTempRow variable needed. just remove the `INTO @tblTempRow`. For the insert, just add an `OUTPUT INSERTED.intUserID, INSERTED.intBlobID` and you won't need the select inside the else block, or the one after the (which will cause an extra empty result set on insert). – RacerX Mar 07 '11 at 19:18

Lock row for reading on SQL Server?

2 Answers2