solving sql server problems for millions of dbas and developers since 2006

Top SQL Server Tools

SQL safe backup

SQL compliance manager

SQL diagnostic manager

SQL Sentry Performance Advisor

SQL Source Control

Processing Data Queues in SQL Server with READPAST and UPDLOCK

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

Free SQL Learning - Backup compression and storage deduplication: A perfect match?

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 IDENTITY( 1  , 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 1 @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 1 @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 1 @queueid = QUEUEID 
FROM DBO.QUEUE WITH (updlock, readpast) 

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 (updlock, readpast)) >= 1 

BEGIN 

   BEGIN TRAN TRAN1  

   SELECT TOP 1 @queueid = QUEUEID  
   FROM DBO.QUEUE WITH (updlock, readpast)  

   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: 6/4/2007

About the author

Become a paid author

Comments and Feedback:

Tuesday, May 26, 2009 - 2:42:17 AM - sisvis	Read The Tip
Nice Tip. After reading this post I got some idea about locking hints. Solution for Data Queue is explained very nicely.

Thursday, June 25, 2009 - 9:28:20 PM - cakvichu	Read The Tip
Nice tips. Can I select multiple rows like TOP 10 rows?

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

Read The Tip

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, August 20, 2009 - 6:11:14 PM - aprato	Read The Tip
It just sounds like the transaction time is too long. Preferably, you'd want to the transaction itself to be as short as possible.

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

Read The Tip

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.

Friday, August 21, 2009 - 6:23:34 AM - aprato	Read The Tip
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.

Tuesday, August 31, 2010 - 2:33:27 PM - Tommy Johnson

Read The Tip

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, June 17, 2011 - 3:03:12 PM - Serge Mirault

Read The Tip

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

[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.

CCScheduleID

,a.

CCRecipientID

,a.Frequency

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 =

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) >=

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 =

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

Can anyone help me out here?

Sponsor Information

Find and fix SQL Server problems before they happen - SQL diagnostic manager now with predictive analysis!

Get your SQL Server database under version control now! Find out why...

What grade do you think your SQL Servers get? Find out with a SQL Server Health Check consultant in the USA.

Unlock the power of the Transaction log to discover unauthorized changes and recover lost data

Optimizing SQL Server performance can be a daunting task. Or is it?