Friday, September 06, 2013

Dispatcher with database deadlock (or using queue with multiple workers)

I have a web service with the following set of operations:
-insert event
-get all available events
-send the events
-mark as sended

It's easy to generate a deadlock with this sequence, so what we need to do is use the sql hints to handle this matter.

BEGIN TRAN


--INSERT A NEW ELEMENT LOCKING ONLY THE INSERTED ROW
INSERT INTO  [Tab1] WITH (ROWLOCK)
           ([objectTypeId]
           ,[objectId] )
     VALUES
           (8
           ,1)

--SELECT THE EVENTS AVOIDING SELECTING THE BLOCKED ROWS (READPAST)
SELECT * FROM [Tab1] WITH  (READPAST, ROWLOCK)
     WHERE objectTypeId = 8 AND objectId =1

--UPDATE ALL THE EVENTS THAT WE NEED, ALL THE UNLOCKED ONES
UPDATE [Tab1] with (READPAST, ROWLOCK)
     SET operationTms = GETDATE()
     WHERE objectTypeId = 8  AND objectId =1


COMMIT

"As a last note, the READPAST hint can only be specified in transactions operating at the READ COMMITTED or REPEATABLE READ isolation levels." in http://aartemiou.blogspot.pt/2011/08/updating-sql-server-tables-without.html

No comments: