Having problems with concurrency while trying to lock a record in custom messaging system (Queue)

Question

We are building our own custom messaging system and are having concurrency issues. Here are the rules:

1. A process (EXE) console application locks 3 records and returns them

2. No other process running (we have 5 EXEs running) can pick any record that the other processes have already taken.

That simple, but yet, I'm puzzled.

Summary of the SQL Server stored procedure doing a "Lock And Peek":

The idea behind this that we reserve three "NEW" records and change their status to "IN PROGRESS" with a ROWLOCK on the SELECT and UPDATE statements. So in theory these records should be locked for one process so that other processes can't update or even select them. Can someone tell me what I'm doing wrong please?

ALTER PROCEDURE [dbo].[LockAndPeek] 
 @Count INT,
 @QueueTypeId INT
AS
BEGIN
 SET NOCOUNT ON;
 DECLARE @ListofIDs TABLE(ID INT);
 DECLARE @StatusIDInProgress INT
 SELECT @StatusIDInProgress = ID 
 FROM QueueStatuses (NOLOCK)
 WHERE Name = 'In Progress'
 INSERT INTO @ListofIDs (ID)
 SELECT TOP (@Count) Q.ID 
 FROM Queues Q (ROWLOCK) 
 INNER JOIN QueueStatuses QS (ROWLOCK) ON Q.StatusID = QS.ID
 WHERE QS.Name IN ('New', 'Errored') 
 AND Q.TypeID = @QueueTypeID 
 AND Q.AvailableTime IS NOT NULL 
 AND Q.AvailableTime <= GETUTCDATE()
 ORDER BY Q.ID
 UPDATE Q WITH (ROWLOCK)
 SET STATUSID = @StatusIDInProgress,
 PROCESSED = GETUTCDATE()
 FROM Queues Q (ROWLOCK) 
 INNER JOIN QueueStatuses QS (ROWLOCK) ON Q.StatusID = QS.ID 
 INNER JOIN @ListofIDs LI ON Q.ID = LI.ID
 WHERE QS.Name IN ('New', 'Errored')
 SELECT 
 Q.ID, Q.AvailableTime, Q.NumberOfTries,
 Q.Created, Q.Processed, Q.ErrorData,
 QT.ID QueueTypeID, QT.Name QueueTypeName,
 QS.ID QueueStatusID, QS.Name QueueStatusName,
 Q.Message
 FROM 
 Queues Q (NOLOCK) 
 INNER JOIN
 QueueStatuses QS (NOLOCK) ON Q.StatusID = QS.ID 
 INNER JOIN
 QueueTypes QT (NOLOCK) ON Q.TypeId = QT.ID 
 INNER JOIN
 @ListofIDs LI ON Q.ID = LI.ID
END

• usr: I don't see a transaction. Is there one?
• usr: Also, lock level hints are missing such as UPDLOCK. Also HOLDLOCK is missing.

Answer 1

The query looks so much simpler. There's an output keyword in SQL Server that works perfectly!

Here we go:

ALTER PROCEDURE [dbo].[LockAndPeek] 
 @Count INT,
 @QueueTypeId INT
AS
BEGIN
SET XACT_ABORT ON; -- blow up the whole tran on any errors
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRAN
UPDATE Q
SET 
 StatusID = 2, -- In Progress
 Processed = GETUTCDATE()
OUTPUT Inserted.*
FROM (
 SELECT TOP (@Count) * 
 FROM
 Queues WITH (READPAST, ROWLOCK)
 WHERE
 StatusID = 1 AND -- New 
 TypeID = @QueueTypeID AND
 AvailableTime IS NOT NULL AND
 AvailableTime <= GETUTCDATE()
 ORDER BY ID
) Q;
COMMIT TRAN;
END

• Hannah Vernon: There you go! The OUTPUT clause is great!