I have implemented what is effectively a reliable queue through the following:
CREATE TABLE Queue (MessageID UNIQUEIDENTIFIER, Message varchar(255)) --Enqueue Command DECLARE @MessageID UNIQUEIDENTIFIER = NEWID() INSERT INTO Queue (MessageID, Message) VALUES (@MessageID, 'message data') --Process BEGIN TRAN -- actually done via ADO SELECT Message FROM Queue WITH (ROWLOCK, XLOCK, READPAST) WHERE MessageID = @MessageID -- at this point, the client does some computation, which could fail DELETE Message FROM Queue WHERE MessageID = @MessageID COMMIT TRAN --again, from ADO
Question being: how can I take a lock on the original insert, but still keep the insert committed if the processing transaction fails?
The queue is inserted and read from by multiple clients and there is also a recovery client which attempts to find any orphaned messages by:
SELECT MessageID FROM Queue WITH (READPAST)
and it is on rare occurances catching the message after it is inserted but before it is locked for processing.
The database is running on SQL Server 2012, the client is a .Net 4 client using ADO.NET.