Use (UPDLOCK, READPAST) to read a SQL Server data queue

The problem
A useful business scenario involves reading data from a "queue", which would be a table consisting of rows generated by one process, but read by a separate process that runs independently.  The second process (queue reader) may be running with multiple threads, or having multiple instances (such as a multi-user desktop or web application).  This can often lead to unexpected results, such as duplicate data retrieval, deadlocks, or other concurrency issues.  The SQL Server stored procedure called by such an application may involve a set of steps similar to:

1. SELECT the ID of a specific row (meeting some criteria) into a temporary variable
2. UPDATE a flag on that row to mark it as "processed"
3. SELECT the data from that row along with rows from any related tables

Enclosing the above logic inside a BEGIN TRANSACTION - COMMIT TRANSACTION segment does not prevent any parallel processes from reading the same ID in step-1 above. This is because while the transaction prevents both processes from trying to update the same row, but the SELECT in step-1 of one transaction does not block the other transaction from also doing the same SELECT.

The solution
An efficient approach to solving this is to use the WITH (UPDLOCK, READPAST) table hint in the SELECT statement. UPDLOCK specifies that update locks are to be taken and held until the transaction completes, and READPAST specifies that SQL Server not read rows (i.e. skip them) that are locked by other transactions.

The combined effect of these table hints is that the first transaction to get to the SELECT statement establishes a lock on the row (or page), and the second transaction simply skips the locked rows and reads the next available row.  Without the READPAST, the second transation would get blocked by the first one until the locks are released.

Here's a sample code segment to use these table hints in a stored procedure:

 

BEGIN TRANSACTION

SELECT TOP 1 @rowid = a.RowID
FROM TestTable a WITH (UPDLOCK, READPAST)
ORDER BY a.ProcessedOnUtc, a.RowID


UPDATE TestTable SET ProcessedOnUtc = GETUTCDATE()
WHERE RowID = @rowid


-- (rest of the data retrieval and processing statements)
 
COMMIT TRANSACTION

A very important Gotcha!
To enable this to work correctly, there must be an index on the column(s) being used to identify the selected row(s), in this case the RowID column.  Without that index, SQL Server will need to look through the table to find the correct row for the second transaction, which will then get blocked since it won't be able to get past the row that's locked by the first transaction.  The result in such a case will be that duplicates will be avoided but deadlocks will still occur - the same as using UPDLOCK without READPAST.