Pessimistic Locking
time to read 12 min | 2212 words

I need to be able to ask the database for some rows to work on, and I need to be sure that I am the only one that works on those rows. After thinking about it, I came up with this SQL:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

 

      INSERT INTO Locks(LockedBy, LockedAt, LcokedItemId, LockedItemType)

      OUTPUT inserted.Id

      SELECT TOP 100 @lockedBy, getdate(), item.Id, @itemType

      FROM Items item

      WHERE NOT EXISTS (SELECT 1 FROM Locks lock

            WHERE lock.LcokedItemId = item.id AND lock.LockedItemType = @itemType)

      ORDER BY item.Id

 

COMMIT TRANSACTION

This is the first time I need to implement pessimistic locking, and I am not sure if this is the right way to go. The work I am doing is disconnected, so I can't just lock the rows and continue to work.

Any suggestions?

Update: This is what I have now:

CREATE PROCEDURE lockAndGetItems AS

 

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

 

      DECLARE @lockedBy uniqueIdentifier, @countOfItemsToLock int

      SET @lockedBy  = '68A6D54C-557D-428D-8B82-5D68C9C1B33E'

 

      -- Get number of rows already locked by me

      SELECT @countOfItemsToLock = 100 - COUNT(Id) From Locks where LockedBy = @lockedBy

 

      -- Lock rows to complete to an even 100

      INSERT INTO Locks(LockedBy, LockedAt, LockedItemId)

      SELECT TOP (@countOfItemsToLock)  @lockedBy, getdate(), item.Id

      FROM Items item

      WHERE NOT EXISTS (SELECT 1 FROM Locks lock

            WHERE lock.LockedItemId = item.id and lock.LockedBy = @lockedBy)

      ORDER BY item.Id

 

      -- Return row data

      SELECT Items.* FROM Items JOIN Locks ON Items.Id = Locks.LockedItemId AND LockedBy = @lockedBy

     

COMMIT TRANSACTION

Basically it fetch the first 100 items designated for this SP. Each SP has a single caller, so I don't worry about expiring the lock. I will always get the locked rows. In the case of a crash when the rows are locked, when the client is restarted, it will get the same rows that it already locked, and can start working on them. The reason that I implement is as a SP with hard coded Guid is that each SP locks a different table.

Tweet Share Share 0 comments
Tags: