Simultaneous Data Queue Polling Request Processing in SQL Server

In My last post, we learned how to use the SQL server paging effectively in real word application. This post is another SQL Server tip to implement simultaneous data queue processing in SQL Server. In many of our enterprise applications, we have the design to handle incoming request comes from the multiple sources by putting into queue (MSMQ or SQL Server table) and process it later by processing engine.

This article explains about how to process the stored requests from SQL table by mutiple engine simultaneously running in app farm. We need an way to process those requests by multiple engine simultaneously but if one engine is picked the request, other engine should not be picking it and at the same time, table should not be locked for reading other records.

In SQL Server, we have the 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 are available but we are going to look into UPDLOCK, READPAST for this scenario.

UPDLOCK

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

READPAST

Specifies that the Database Engine 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 thes rows selected by the first engine and not to return those rows to other engine even if the request comes simultaneously.

In this example query below, we are performing the data queue operations to select 10 records at a time by each engine and once its picked, it need to update the record status to PICKED and also other engine should be able read the other records in parallel.

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 wont be available for other engine as well.

The below query will return 10 unprocessed rows for each request from engine and also updates those record 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!