Simultaneous Data Queue Polling Request Processing In SQL Server

This article explains how to implement simultaneous data queue processing using SQL Server. In many of our enterprise applications, we have the design to handle incoming request coming from multiple sources by putting them into the queue (MSMQ or SQL Server table) and then processing those stored requests from SQL table by multiple app engines simultaneously running the in-app farm.

Introduction

This article explains how to implement simultaneous data queue processing using SQL Server. In many of our enterprise applications, we have the design to handle incoming requests that come from multiple sources by putting them into the queue (MSMQ or SQL Server table) and then processing those stored requests from SQL table by multiple app engines simultaneously running the in-app farm. We need a way to process those requests by multiple app engines simultaneously, but if one engine has picked the request, another engine should not be picking the same item and also at the same time, the table should not be locked for reading other records by other app engines.

How it works 

In SQL Server, we have a concept called Table Hints which are specified in the FROM clause of the DML statement and affect only the table or view referenced in that clause. There are various types of table hints available but we are going to look into UPDLOCK, and READPAST for this scenario.

UPDLOCK

Specifies that update locks are to be taken and held until the transaction completes. UPDLOCK takes update locks for reading operations only at the row-level or page-level.

READPAST

Specifies that the Database Engine did not read rows that are locked by other transactions. When READPAST is specified, row-level locks are skipped but page-level locks are not skipped.

So, by combining UPDLOCK, READPAST in our DML statement, it provides the locking for these rows selected by the first engine and doesn't return those rows to other engines even if the request comes simultaneously.

The design is to perform the data queue operations with multiple engines. We have to select 10 records at a time by each engine and once it is picked, it needs to update the status to PICKED so that it will not be processed again.

 

In SQL Server, we have OUTPUT clause which returns the information based on each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. So, we will use the output clause logic to return the rows that we are updating to PICKED status and we use UPDLOCK, READPAST table hints to select the rows so that it locks those rows and it won't be available for other engines as well.

The below query will return 10 unprocessed rows for each request from the engine and also updates those record's status to PICKED. This also takes care of handling requests simultaneously.

UPDATE incoming_request SET status_value = 'PICK' OUTPUT INSERTED.*
WHERE request_id in
(SELECT top 10 request_id
FROM incoming_request WITH (updlock, readpast)
WHERE status_value = 'UNPC'
ORDER BY request_id ASC)

Happy Coding!!