Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

This isn't really a reliable queue pattern

  • You have a long running transaction that depends on the client processsing time
  • A client time out won't release locks because connection pooling and you don't have SET XACT_ABORT

Typically, you'd set a row as "In Processing" with an UPDATE using ROWLOCK, HOLDLOCK, READPAST hints. This allows multiple clients to not block each other and you can

Then, when the client has done it's stuff, you can DELETE the row in a separate transaction. Or, flag "Has Error" on the same row as needed.

See these for more:

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.