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

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