A simple queue using tables in Microsoft SQL Server
I needed a fast and reliable queue in SQL when running some distributed tests but I did not find anything that suited my needs so I put together the simple system below. My main requirements were:
- support hundreds of parallel readers and writers
- each request in the queue must only be returned to one reader
- make it easy to monitor the performance of my readers and writers.
It consists of two stored procedures:
- usp_Request_push adds one requst to the queue (RequestsQueue table)
- usp_Request_pop returns one request from the queue and moves it to the RequestsCompleted table
The RequestsCompleted table is optional but I added it for several reasons:
- Track all requests to verify that my client does not "loose" messages. Any record in RequestsCompleted with a "RemovedDate" but a NULL "CompletedDate" got lost somewhere.
- To calculate statistics like average time before the message was removed from the queue and average time before the request was completed by the client.
Tables
CREATE TABLE [dbo].[RequestsQueue](
[id] [uniqueidentifier] NOT NULL
CONSTRAINT [DF_RequestsQueue_id]
DEFAULT (newid()),
[request] [varchar](512) NOT NULL,
[InsertedDate] [datetime] NOT NULL
CONSTRAINT [DF_RequestsQueue_InsertedDate]
DEFAULT (getdate()),
CONSTRAINT [PK_RequestsQueue] PRIMARY KEY NONCLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[RequestsCompleted](
[id] [uniqueidentifier] NOT NULL
CONSTRAINT [DF_RequestsCompleted_id]
DEFAULT (newid()),
[request] [varchar](512) NOT NULL,
[InsertedDate] [datetime] NOT NULL
CONSTRAINT [DF_RequestsCompleted_InsertedDate]
DEFAULT (getdate()),
[RemovedDate] [datetime] NOT NULL
CONSTRAINT [DF_RequestsCompleted_RemovedDate]
DEFAULT (getdate()),
[CompletedDate] [datetime] NULL
) ON [PRIMARY
Stored procedures
CREATE PROCEDURE [dbo].[usp_Request_Pop]
AS
BEGIN
declare
@request varchar(512),
@id uniqueidentifier,
@InsertedDate datetime
/*
The queue works like this:
- Get the first record in the queue
- "Move" the recor to the RequestCompleted table
- return the request to the caller
We use a locking cursor so the same record cannot be
returned to two different clients
*/
SET NOCOUNT ON;
set @id=null
begin transaction
DECLARE Stack_Cursor CURSOR FOR
SELECT TOP 1 id, request, InsertedDate
FROM RequestsQueue with (ROWLOCK, FASTFIRSTROW, XLOCK)
OPEN Stack_Cursor;
FETCH NEXT FROM Stack_Cursor INTO @id, @request, @InsertedDate
IF @@FETCH_STATUS = 0
BEGIN
delete RequestsQueue where current of Stack_Cursor
END;
CLOSE Stack_Cursor;
DEALLOCATE Stack_Cursor;
commit transaction
if not @id is null
begin
-- OPTIONAL store to the Completed table.
insert into RequestsCompleted (id, request, insertedDate)
values (@id, @request, @InsertedDate)
-- return data to caller
select @id as id, @request as request, @InsertedDate as InsertedDate
end
END
CREATE PROCEDURE [dbo].[usp_Request_Push]
@request varchar(512)
AS
BEGIN
SET NOCOUNT ON;
insert into RequestsQueue(request) values (@request)
END
Note: The queue is not guaranteed to be FIFO as the select statement does not sort the records in any way. It is normally FIFO as SQL Server appends records in tables that do not have a clustered index.
