Mutex Stored Procedure

Question

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

Wait (UniqueIdentifier)

I initially thought about this, having a table of unique identifiers. The procedure call will wait until a unique identifier is found in the table. However, I'm not sure how to make the calling thread wake up when the specified unique identifier has been removed from the table.

Any ideas? If the database is not suitable for this, are there any third-party tools that will work (preferably open source)?

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

Answer 1

Take a look at the system stored procedure:

 sp_getapplock

This can help you accomplish what you are trying to do.

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

You can put it in proc, which parameterizes a unique identifier ...

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