Sql-server – Commit Insert but Hold Lock

sql serversql-server-2012transaction

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.

Best 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:

Related Question