Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Processing Data Queues in SQL Server with READPAST and UPDLOCK


By:   |   Read Comments (10)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More > Locking and Blocking

Problem
One common processing problem that a DBA can encounter is processing rows from a table used as a data queue. Queue processing is functionality where one or more processes INSERTs rows into a database table representing a work queue with each row representing a business action that must be performed. At the same time, one or more processes SELECTs records from the same queue table in order to execute the business action required by the application while later deleting the processed row so it is not processed again. Typically, the reading processes use polling to interrogate the queuing table for any new rows that require execution of a business action. If done incorrectly, processing data queues can produce unexpected results and/or performance issues.

Solution
The following examples set up a process queue to be processed by two separate processes reading the same queue.  This can be extended beyond two processes, but for this example we want to show you how two processes can work against one work queue. We'll start with examples to illustrate issues that can be encountered.

First, let's create a sample table and populate it with 10 records to be processed.

-- create an example queue table
CREATE TABLE DBO.QUEUE (
  
QUEUEID    INT IDENTITY1  1  ) NOT NULL PRIMARY KEY,
  
SOMEACTION VARCHAR(100))

GO

-- seed the queue table with 10 rows
DECLARE  @counter INT

SELECT 
@counter 1

WHILE (@counter <= 10)
  
BEGIN
    INSERT INTO 
DBO.QUEUE
               
(SOMEACTION)
    
SELECT 'some action ' CAST(@counter AS VARCHAR)
    
    
SELECT @counter @counter 1
  
END

Encountering unexpected results
Open 2 separate query windows and issue the following statements in each session:

DECLARE @queueid INT

BEGIN TRAN 
TRAN1

SELECT TOP @queueid QUEUEID
FROM DBO.QUEUE

PRINT 'processing queueid # ' CAST(@queueid AS VARCHAR)

-- account for delay in processing time
WAITFOR DELAY '00:00:10'

DELETE FROM DBO.QUEUE
WHERE QUEUEID @queueid

COMMIT

As you will see, each session processed the same row! This is obviously unacceptable processing behavior, but what can we do about it?

We can eliminate this behavior by adding the UPDLOCK hint to the SELECT statement. The UPDLOCK hint tells the SQL Server query engine "Don't allow any other reader of this row to acquire an UPDLOCK ("U" lock) because I will be promoting this lock to an exclusive "X" lock later in my processing". It effectively reserves the row for your processing. However, as you will see, this can cause a new problem to arise.

Encountering blocking
The SELECT statement has been modified to use the UPDLOCK hint.

Open 2 separate query windows and issue the following statements again.

DECLARE @queueid INT

BEGIN TRAN 
TRAN1

SELECT TOP @queueid QUEUEID
FROM DBO.QUEUE WITH (updlock)

PRINT 'processing queueid # ' CAST(@queueid AS VARCHAR)

-- account for delay in processing time
WAITFOR DELAY '00:00:10'

DELETE FROM DBO.QUEUE
WHERE QUEUEID @queueid

COMMIT

As you can see from the modified example, each session now processes separate rows. Good so far. However, the 2nd session took longer to execute than it did in the first example even though it now processes a separate row. Why is this? It's because an UPDLOCK ("U") lock has been placed on the row processed by the first session and the 2nd session is forced to wait on this lock to be released before it is allowed to retrieve the next row for processing. This is highly inefficient since multiple consumers of the queue must all wait until any locks are released. So, how do we get around this?

To get around the blocking encountered in the previous example, a READPAST hint can be used in conjunction with the UPDLOCK hint. The READPAST hint tells the SQL Server query engine "If you encounter any rows that are locked, just skip them... I want whatever is not currently being processed by anyone".

Incorporating the READPAST query hint
The SELECT statement has been modified to use the READPAST hint in addition to the UPDLOCK hint.

Open 2 separate query windows and issue the following statements again.

DECLARE @queueid INT

BEGIN TRAN 
TRAN1

SELECT TOP @queueid QUEUEID
FROM DBO.QUEUE WITH (updlockreadpast)

PRINT 'processing queueid # ' CAST(@queueid AS VARCHAR)

-- account for delay in processing time
WAITFOR DELAY '00:00:10'

DELETE FROM DBO.QUEUE
WHERE QUEUEID @queueid

COMMIT


As you can see from this latest example, each session now processes separate rows and the 2nd session is no longer blocked as evidenced by the execution time (both sessions should complete at roughly the same time).

Using the UPDLOCK hint in conjunction with the READPAST hint gives the best performance for processing queues while eliminating unexpected results and blocking.

Putting it all together
Here is an example of the above code that takes it a step further and processes all of the records in the queue.  To run this drop table dbo.queue and then recreate it by running the code in the very first code block above that creates the table and loads the data.

Open 2 separate query windows and issue the following statements again.

SET NOCOUNT ON
DECLARE 
@queueid INT 

WHILE 
(SELECT COUNT(*) FROM DBO.QUEUE WITH (updlockreadpast)) >= 1

BEGIN

   BEGIN TRAN 
TRAN1 

   
SELECT TOP @queueid QUEUEID 
   
FROM DBO.QUEUE WITH (updlockreadpast

   
PRINT 'processing queueid # ' CAST(@queueid AS VARCHAR

   
-- account for delay in processing time 
   
WAITFOR DELAY '00:00:05' 

   
DELETE FROM DBO.QUEUE 
   
WHERE QUEUEID @queueid
   
COMMIT
END

Next Steps

  • When processing data queues, use the UPDLOCK hint along with the READPAST hint to get maximum throughput of your data queues.
  • Read more information about UPDLOCK and READPAST in the SQL Server 2000 and 2005 Books Online under Locking Hints.
  • Read more about Lock Compatibility in the SQL Server 2000 and 2005 Books Online
  • Thank you to Armando Prato for providing this tip!


Last Update:






About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, November 25, 2015 - 11:55:30 AM - Mike Heydon Back To Top

Hi Armando,

Article - "Processing Data Queues in SQL Server with READPAST and UPDLOCK"

 

Wonderful article, solved my problem with a multi-threaded Win32 Service that processes incoming Packets from a

MS-SQL Server table. I have also been in the IT development games since the late 1980's and have mainly focused on Oracle

databases, but in this project the database was MS-SQL Sever. I have previously done a lot of work with MS-SQL Servers as the

underlying database, but this was the first time I encountered the need for a multi-threaded solution that processed a

queue table. Typically in these threads I would have used the Oracle "SELECT FOR UPDATE" construct to prevent duplicates

and deadlocks, MS-SQL Server does not have this construct, and my search for alternative solution in MS-SQL lead me to your article.

 

The article is Clear, Easily Understood and was a HUGE help to me.

 


Friday, November 15, 2013 - 12:24:56 AM - K_Kill Back To Top

Even after 3 yrs this tip is very useful .I have developed a similar application ,however due to business need we have to specify the ORDER BY clause in select statement (Note: column in order by clause is not a part of clustered index) . In this case the example given by you creates a contention problem

 

SELECT TOP 1

FROM [TABLE] WITH (ROWLOCK,UPDLOCK,READPAST)

ORDER BY [Priority1],[Priority2]

 

Could you please suggest something to solve it?


Friday, June 17, 2011 - 3:03:12 PM - Serge Mirault Back To Top

When trying the examples you've provided I get the expected results however, when attempting to use it with a more applicable busines scenario I run into issues.  Consider that I populate a reports "queue" table with the current list of reporting for the day.  I then start a procedure that checks to see how many reports exist, selecting the TOP 1 row from the main reports table so it can be processed accordingly.   The main point of this is to have multiple instances running the same procedure, however NOT selecting the same row twice! The issue is, it appears to randomly select the same record.

Code for loading the queue table:
INSERT INTO Reports

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SELECT

 

 

a.

[ReportID]

 

 

,a.

[ATID]

 

 

,a.

[AID]

 

 

,a.

[Source]

 

 

,CONVERT(VARCHAR(40),a.NextDateTime,121

)

 

 

,b.

[ReportFileName]

 

 

,b.

Name

 

 

,REPLACE(b.Name,' ','') + '_' + Convert (VARCHAR(10),a.[AID]) + '_' + Convert (VARCHAR(10),DATEADD(DAY,-1,a.[NextDateTime]),121) + '_' + REPLACE(Convert (VARCHAR(5),a.[NextDateTime],8),':','') as

reportname

 

 

,a.

Frequency

 

 

,a.

ID

 

 

,a.

CCScheduleID

 

 

,a.

CCRecipientID

 

 

,a.Frequency

,

a

 

.

running

FROM

 

 

dbo.CC_Report_Schedules as a WITH(UPDLOCK, READPAST

)

 

 

JOIN [dbo].[CC_ReportList] as b WITH(UPDLOCK, READPAST

)

 

 

ON b.ReportId=a.

ReportId

WHERE

 

 

 

 

CONVERT(VARCHAR(10),a.NextDateTime,121) <= CONVERT(VARCHAR(10),@RUNTIME,121

)

 

 

AND a.Frequency not in (2,99)

 

 

AND running =

0

ORDER

 

 

BY a.NextDateTime asc,a.[AID],a.

reportid

Code used to start the multiple processes (this will be run several times simultaneously):

WHILE

 

(SELECT COUNT(1) FROM dbo.Reports WITH(NOLOCK) WHERE Running = 0) >= 1

 

BEGIN

 

 

 

EXEC dbo.utl_ApplicationReOccurring

 

 

END

Finally the code that SHOULD be working (utl_ApplicationReOccurring):

SET

 

NOCOUNT ON

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

DECLARE

 

 

@Err1 int, @Err2 int, @Err3

int

DECLARE

 

 

@reports

TABLE

(

 

 

idnt int IDENTITY (1,1

)

 

 

,ReportID

INT

 

 

,ATID varchar(4

)

 

 

,AcctID varchar(10

)

 

 

,Source varchar (20

)

 

 

,NextDateTime VARCHAR(40

)

 

 

,ReportFileName VARCHAR(100

)

 

 

,[Name] VARCHAR (60

)

 

 

,ReportName VARCHAR (500

)

 

 

,DeliveryFrequency char(4

)

 

 

,RID VARCHAR(40

)

 

 

,ScheduleID

bigint

 

 

,RecipientID bigint

)

WHILE

 

 

(SELECT COUNT(1) FROM dbo.Reports WITH(NOLOCK) WHERE Running = 0) >=

1

BEGIN

 

 

BEGIN TRANSACTION

AppReport

 

 

INSERT INTO

@reports

 

 

SELECT TOP

1 ReportID

 

 

,

ATID

 

 

,

AcctID

 

 

,

[Source]

 

 

,CONVERT(VARCHAR(40),NextDateTime,121

)

 

 

,

ReportFileName

 

 

,

Name

 

 

,REPLACE(Name,' ','') + '_'

 

 

+ Convert (VARCHAR(10),AcctID) + '_'

 

 

+ Convert (VARCHAR(10),DATEADD(DAY,-1,NextDateTime),121) + '_'

 

 

+ REPLACE(Convert (VARCHAR(5),NextDateTime,8),':','') as

reportname

 

 

,

Frequency

 

 

,

RID

 

 

,

ScheduleID

 

 

,

RecipientID

 

 

FROM dbo.Reports WITH(ROWLOCK, READPAST

)

 

 

WHERE running =

0

 

 

ORDER BY NextDateTime ASC, AcctID,

reportid

 

 

SET @Err1 =

@@ERROR

 

 

--UPDATE dbo.Reports SET running = 1

 

 

DELETE FROM dbo.

Reports

 

 

WHERE ScheduleID in (SELECT ScheduleID FROM @Reports

)

 

 

SET @Err2 =

@@ERROR

 

 

 

INSERT INTO dbo.

Results

 

 

SELECT @Instance, ReportID, ScheduleID, GETDATE() FROM

@Reports

 

 

SET @Err3 =

@@ERROR

 

 

 

DELETE FROM

@Reports

 

 

IF (@Err1 = 0) AND (@Err2 = 0) AND (@Err3 = 0

)

 

 

BEGIN

 

 

/* Added to mimic that the system is runnign something */

 

 

WAITFOR DELAY

'00:00:01'

 

 

COMMIT

TRANSACTION

 

 

END

 

 

ELSE

 

 

BEGIN

 

 

ROLLBACK

TRANSACTION

 

 

END

END

 

Can anyone help me out here?

 

 


Tuesday, August 31, 2010 - 2:33:27 PM - Tommy Johnson Back To Top
A more typical approach would be to have a status field in the queue table:

CREATE TABLE dbo.Queue(QueueId int identity primary key, StatusId varchar(10), ItemId int)

ItemId represents the identifier for something that you want to process, like some long-running calculation.

When queueing a process, add it with a status of 'WAITING', like so:

INSERT dbo.Queue(StatusId, ItemId) VALUES('WAITING', 123456)

Then when you want to check the queue:

BEGIN TRAN

SELECT TOP 1 @QueueId = Q.QueueId, @ItemIdToProcess = Q.ItemId FROM dbo.Queue Q WHERE Q.StatusId = 'WAITING'

UPDATE dbo.Queue SET StatusId = 'PROCESSING' WHERE StatusId = 'WAITING'

COMMIT TRAN

exec ProcessItem @ItemIdToProcess

UPDATE dbo.Queue SET StatusId = 'DONE'

Of course, there is still the possibility that processing fails, but that can be handled by another transaction or transactions to limit the extent of any single transaction.

 


Friday, August 21, 2009 - 6:23:34 AM - aprato Back To Top

Well, you can also look at Service Broker.  And yes, long transactions are problematic not just for this but just about any other type of processing you can think of.   You should typically keep your transactions as short as possible.


Friday, August 21, 2009 - 3:16:34 AM - Doctor Jools Back To Top

So this technique is only practical if the processing you have to do for each item in the queue doesn't take too long? This seems like a pretty major limitation!

I'm currently looking at the option of having an additional table which is filled from DBO.QUEUE and which is subject to the transaction in place of DBO.QUEUE. This means that other processes may continue to add to the queue even while a (potentially very long) transaction is open on this additional table.


Thursday, August 20, 2009 - 6:11:14 PM - aprato Back To Top

It just sounds like the transaction time is too long.  Preferably, you'd want to the transaction itself to be as short as possible.


Wednesday, August 19, 2009 - 2:51:49 AM - Doctor Jools Back To Top

I'm trying to implement something very similar to this in a working system. However, I'm having problems with deadlocks which I think are related to the use of transaction "TRAN1" in this example. You have an open transaction for the duration of your processing, which may be a long time in practice. (You've done a 5 second delay to mimic it here.)

 I'm finding that this long open transaction leads to deadlock errors in other processes when they try to add more records to the queue table "DBO.QUEUE".

Any thoughts about this?


Thursday, June 25, 2009 - 9:28:20 PM - cakvichu Back To Top

Nice tips. Can I select multiple rows like TOP 10 rows?


Tuesday, May 26, 2009 - 2:42:17 AM - sisvis Back To Top

Nice Tip. After reading this post I got some idea about locking hints. Solution for Data Queue is explained very nicely.


Learn more about SQL Server tools