Lock row for reading on SQL Server?

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 tem开发者_StackOverflow社区p 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

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

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