Technical Article

Implementing Queuing Mechanism

,

Insert rows in to the queue table and run this script in the queue processor job.

 

 

Table Hints do the trick ,otherwise there will be locking issues.

 

UPDLOCK : Grabs an update lock until transaction is completed and prevents another process from picking up same queue item.

 

READPAST : If a process encounters a row that is currently locked by another, this hint will make it skip that locked row and moves on to find the next one.

 

http://msdn.microsoft.com/en-us/library/ms187373.aspx

 

DECLARE @nextMergeID INT ;
DECLARE @customerID INT;
DECLARE @mergeDate DATETIME;
DECLARE @reset BIT;
 
BEGIN TRAN 
      --Get Next MergeID 
      SELECT TOP 1 
@nextMergeID = merge_id
      FROM dbo.merge_standard_queue WITH(UPDLOCK,READPAST)
      WHERE [picked] = 
0
      ORDER BY merge_date 
ASC;
      
      --if found, flag it to prevent being picked up again 

      IF(@nextMergeID 
IS NOT NULL)
      BEGIN
            UPDATE dbo.merge_standard_queue WITH(ROWLOCK) 
            SET [picked] = 
1
            WHERE merge_id = 
@nextMergeID;
      END
      
COMMIT TRAN
 
IF(@nextMergeID IS NOT NULL)
BEGIN 
---Do Processing Here 
    
    
END

Rate

3.75 (4)

You rated this post out of 5. Change rating

Share

Share

Rate

3.75 (4)

You rated this post out of 5. Change rating