We are building our own custom messaging system and are having concurrency issues. Here are the rules:
A process (EXE) console application locks 3 records and returns them
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