vendredi 2 janvier 2015

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


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 SPROC 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




Aucun commentaire:

Enregistrer un commentaire