SqlServer Queuing

Using a database as a queue is a common requirement. An example is sending emails within a website- it can be slow, error-prone, and you don't want to delay returning a page to the user. So the server processing just queues a request in the database, and a worker process picks it up and tries to execute it.

The real problem is there may be more than one worker process, perhaps running on different servers. By using the table as a queue, they can avoid deadlocks or processing records multiple times.

Comprehensive breakdown of queuing including heap queues, FIFO and LIFO.

Table

Let's have a Created column, and an IsProcessed column. Alternatively we could just delete the rows when they are processed.

CREATE TABLE [dbo].[EmailRequests](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[EmailAddress] [nvarchar](250) NOT NULL,
	[Subject] [nvarchar](50) NOT NULL,
	[Body] [nvarchar](500) NOT NULL,
	[IsProcessed] [bit] NOT NULL,
	[Created] [datetime] NOT NULL,
 CONSTRAINT [PK_EmailRequests] PRIMARY KEY CLUSTERED (	[Id] ASC )
)

The INSERT is just a normal INSERT.

INSERT INTO [EmailRequests]
           ([EmailAddress],[Subject],[Body],[IsProcessed],[Created])
     VALUES
           ('[email protected]','Hello','Spam spam spam',0,CURRENT_TIMESTAMP)

Dequeue

This is a FIFO queue, but the order isn't strict (see this explanation)

The lock hints mean lock the row (as normal), but skip any existing locks (so avoiding deadlocks)
The OUTPUT clause with the CTE makes it all a single atomic operation
The inserted identifier includes UPDATEs and INSERTs. For DELETEs, there is a deleted identifier,

with cte as (
 select top(1) [Id], [IsProcessed], [EmailAddress], [Subject], [Body]
 from [EmailRequests] with (ROWLOCK, READPAST)
 where [IsProcessed]= 0
 order by [Created]
)
update cte
	set [IsProcessed] = 1
	output inserted.[Id], inserted.[EmailAddress], inserted.[Subject], inserted.[Body]

To make this a bit more realistic, you could add a [IsEmailSent] column, updated when the emailing succeeds. Then you need a process for dealing with records that are IsProcessed but not IsEmailSent (dequeued, but the email failed). Remember resetting IsProcessed may poison the queue! (A [RetryCount] up to a maximum may help).

static void