Mutex Stored Procedure

Question

Hi All,

I want to create some distributed mutual exclusion using a database table. It would be nice to have the following interface on a stored procedure:

Wait(uniqueidentifier)

I was originally thinking of implementing this by having a table of unique identifiers. A call to the procedure would wait until the unique identifier does not exist in the table. However, I'm not sure how I would make the calling thread wake up when the specified unique identifier was removed from the table.

Any ideas? If the database is not the right place to do this, are there any third party tools that would work (open source preferably)?

(To avoid deadlocks, I either want to include a timeout in the wait operation or have the SqlCommand have a timeout)

Answer 1

Take a look at the system stored procedure:

 sp_getapplock

It may help you accomplish what you are trying to do.

http://msdn.microsoft.com/en-us/library/ms189823.aspx

You can put it in a proc that parametrizes the uniqueidentifier...

BEGIN TRAN

DECLARE @result int

EXEC @result = sp_getapplock @Resource = 'YOUR_uniqueidentifier_HERE', 
                             @LockMode = 'Exclusive',
                             @LockTimeout = 90

IF @result NOT IN ( 0, 1 )   -- Only successful return codes
BEGIN
  PRINT @result
  RAISERROR ( 'Lock failed to acquire...', 16, 1 )
END 
ELSE
BEGIN
    -- DO STUFF HERE 
END
EXEC @result = sp_releaseapplock @Resource = 'YOUR_uniqueidentifier_HERE'  

COMMIT TRAN