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. Only one worker de-queued the record and has the [Id] so this is straightforward. Then you need a process for dealing with records that are [IsProcessed] but not [IsEmailSent] (dequeued, but the email failed). You might retry (in which case, add a [RetryCount] counter up to a maximum), or have a manual alert (the email address is bogus, etc etc).

Remember resetting [IsProcessed] to 0 or infinitely retrying may poison the queue!

static void