Processing Data Queues in SQL Server with READPAST and UPDLOCK

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!

Foreign key Hierarchy of all tables in a Database

/*==========================================================

 

NAME:                Get foreign key hierarchy of all DB tables

                     (to determine tables INSERT or DROP order, for example)

 

DESCRIPTION:         This is a short script that returns all table names

                     in the current database, together with their foreign key (FK)

                     hierarchy level, and the table(s) that they reference (when

                     applicable). The value of the FK hierarchy associated with

                     each table is determined as follows: If a table does not

                     have a FK constraint (i.e., it does not reference any other

                     tables via a FK, or in other words - the table is not a foreign

                     table in any FK relationship), then it is of level 0 in the

                     hierarchy. If the table references one or more tables,

                     which do not reference any other tables, then the current

                     table is of level 1, and so on. The tables referenced by

                     each FK (i.e., primary tables) are returned by the script

                     as well, for each FK relationship found. Moreover, if a table

                     references itself (and no other tables), then it is

                     considered as a level 0 table.

 

                     The script is useful when one wishes to INSERT data into

                     several tables, or DROP tables, and needs to determine the

                     table order to follow - tables of hierarchy 0 must be

                     inserted into first, then those of hierarchy 1, and so on.

                     Similarly, tables with the highest hierarchy should be dropped

                     first, and those with hierarchy 0 should be dropped last.

 

                     To return the table FK hierarchy info, the script uses the

                     following algorithm: First, get all DB tables that do not

                     have any FK constraints. Then get all tables that have a

                     FK that only reference one or more of the tables that don't

                     have any FKs. Then, get the tables that have FKs mapped

                     to the already collected tables, and so on. The entire

                     algorithm is run in a simple WHILE loop.

 

USER PARAMETERS:     NA

 

RESULTSET:           TableName, HierarchyLevel, FKName, FKReference (the primary

                     table in the FK relationship, where applicable)

 

RESULTSET SORT:      NA

 

USING TABLES/VIEWS:  INFORMATION_SCHEMA.TABLES

                     sysreferences

 

REVISIONS

 

DATE         DEVELOPER          DESCRIPTION OF REVISION             VERSION

======    ===========    ==========================   ===========

05/05/2005   Omri Bahat         Initial release                     1.00

 

==============================================================

Copyright © SQL Farms Solutions, www.sqlfarms.com. All rights reserved.

This code may be used at no charge as long as this copyright notice is not removed.

===============================================================*/

 

-- Get FK hierarchy of all DB tables

 

SET NOCOUNT ON

 

DECLARE @i INT

DECLARE @Cnt INT

 

-- The variable @i is the hierarchy level.

-- The variable @Cnt hold the number of tables returned in the

-- last run of the loop, which tells when the loop should exist.

 

SET @i = 0

SET @Cnt = 1

 

IF OBJECT_ID('tempdb..#tblFKTableOrder', 'U') IS NOT NULL

        DROP TABLE #tblFKTableOrder

 

CREATE TABLE #tblFKTableOrder (

        TableName NVARCHAR(128),

        HierarchyLevel INT,

        FKName NVARCHAR(128),

        FKReference NVARCHAR(128))

       

 

-- First, grab all the tables that don't have any FK constraints, as hierarchy level 0.

 

INSERT INTO #tblFKTableOrder (TableName, HierarchyLevel, FKName, FKReference)

SELECT TABLE_NAME, @i, N'', N''

FROM INFORMATION_SCHEMA.TABLES WITH (NOLOCK)

WHERE TABLE_TYPE = 'BASE TABLE'

        AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasForeignKey') = 0

 

 

-- Second, get all tables that only have self-referencing (and no other) FKs.

-- In the query below - RS1 contains all table names that references themselves

-- (and possible other tables), and RS2 contains all tables that reference other tables.

-- The desired tables are all those in RS1 that are not in RS2.

 

INSERT INTO #tblFKTableOrder (TableName, HierarchyLevel, FKName, FKReference)

SELECT OBJECT_NAME(RS1.fkeyid), @i, OBJECT_NAME(RS1.constid),OBJECT_NAME(RS1.rkeyid)

FROM    (SELECT fkeyid, constid, rkeyid

        FROM sysreferences WITH (NOLOCK)

        WHERE rkeyid = fkeyid ) RS1

        LEFT OUTER JOIN

        (SELECT DISTINCT fkeyid

        FROM sysreferences WITH (NOLOCK)

        WHERE fkeyid <> rkeyid ) RS2

        ON RS1.fkeyid = RS2.fkeyid

WHERE RS2.fkeyid IS NULL

 

 

-- Now, drill down in the FK hierarchy. Get all tables

-- that have a FK that references one or more tables in #tblFKTableOrder,

-- yet only references tables that are in #tblFKTableOrder(!), and that have not yet

-- been recorded in #tblFKTableOrder. Tables that reference themselves, as well

-- as tables in #tblFKTableOrder, are considered as well.

-- This is done in a loop, and the loop terminates when we reach the lowest level

-- in the hierarchy (i.e., when no more tables meet the listed condition).

 

WHILE @Cnt > 0

BEGIN

        -- Analyze the next level in the hierarchy.

        SET @i = @i + 1

 

 

        -- Get all tables that reference tables that are recorded

        -- in #tblFKTableOrder (can also reference themselves),

        -- and do not references tables that

        -- were not yet recorded.

        -- This is done by as follows:

        -- RS1 conatains the tables that have FK constraints

        -- that reference tables in #tblFKTableOrder (and possibly have

        -- a self-reference). RS2 contains all tables that reference tables

        -- that are not yet in #tblFKTableOrder (excluding self-refences).

        -- We write into #tblFKTableOrder the tables in RS1, which are

        -- not in RS2.

 

        INSERT INTO #tblFKTableOrder (TableName, HierarchyLevel, FKName,FKReference)

        SELECT OBJECT_NAME(a.fkeyid), @i, OBJECT_NAME(a.constid), OBJECT_NAME(a.rkeyid)

        FROM sysreferences a

                INNER JOIN

                (SELECT DISTINCT z.fkeyid

                FROM sysreferences z WITH (NOLOCK)

                        INNER JOIN #tblFKTableOrder y WITH (NOLOCK)

                        ON OBJECT_NAME(z.rkeyid) = y.TableName

                        LEFT OUTER JOIN #tblFKTableOrder v WITH (NOLOCK)

                        ON OBJECT_NAME(z.fkeyid) = v.TableName

                WHERE v.TableName IS NULL) RS1

                ON a.fkeyid = RS1.fkeyid

                LEFT OUTER JOIN

                (SELECT DISTINCT x.fkeyid

                FROM sysreferences x WITH (NOLOCK)

                        LEFT OUTER JOIN #tblFKTableOrder w WITH (NOLOCK)

                        ON OBJECT_NAME(x.rkeyid) = w.TableName

                WHERE x.fkeyid <> x.rkeyid

                        AND w.TableName IS NULL) RS2

                ON RS1.fkeyid = RS2.fkeyid

        WHERE RS2.fkeyid IS NULL

 

 

        SET @Cnt = @@ROWCOUNT

END

 

SET NOCOUNT OFF

 

SELECT * FROM #tblFKTableOrder

ORDER BY HierarchyLevel ASC, TableName ASC, FKName ASC

GO