Using SQL Server as a DB queue with multiple clients
I recommend you go over Using tables as Queues.Properly implemented queues can handle thousands of concurrent users and service as high as 1/2 Million enqueue/dequeue operations per minute. Until SQL Server 2005 the solution was cumbersome and involved a mixing a SELECT
and an UPDATE
in a single transaction and give just the right mix of lock hints, as in the article linked by gbn. Luckly since SQL Server 2005 with the advent of the OUTPUT clause, a much more elegant solution is available, and now MSDN recommends using the OUTPUT clause:
You can use OUTPUT in applicationsthat use tables as queues, or to holdintermediate result sets. That is, theapplication is constantly adding orremoving rows from the table
Basically there are 3 parts of the puzzle you need to get right in order for this to work in a highly concurrent manner:
- You need to dequeue automically. You have to find the row, skip any locked rows, and mark it as 'dequeued' in a single, atomic operation, and this is where the
OUTPUT
clause comes into play:
with CTE as ( SELECT TOP(1) COMMAND, PROCESSED FROM TABLE WITH (READPAST) WHERE PROCESSED = 0) UPDATE CTE SET PROCESSED = 1 OUTPUT INSERTED.*;
- You must structure your table with the leftmost clustered index key on the
PROCESSED
column. If theID
was used a primary key, then move it as the second column in the clustered key. The debate whether to keep a non-clustered key on theID
column is open, but I strongly favor not having any secondary non-clustered indexes over queues:
CREATE CLUSTERED INDEX cdxTable on TABLE(PROCESSED, ID);
- You must not query this table by any other means but by Dequeue. Trying to do Peek operations or trying to use the table both as a Queue and as a store will very likely lead to deadlocks and will slow down throughput dramatically.
The combination of atomic dequeue, READPAST hint at searching elements to dequeue and leftmost key on the clustered index based on the processing bit ensure a very high throughput under a highly concurrent load.
My answer here shows you how to use tables as queues... SQL Server Process Queue Race Condition
You basically need "ROWLOCK, READPAST, UPDLOCK" hints
If you want to serialize your operations for multiple clients, you can simply use application locks.
BEGIN TRANSACTIONEXEC sp_getapplock @resource = 'app_token', @lockMode = 'Exclusive'-- perform operationEXEC sp_releaseapplock @resource = 'app_token'COMMIT TRANSACTION