互斥存储过程 [英] Mutex Stored Procedure

问题描述

我要创建使用数据库表中的一些分布式互斥。这将是很好，有一个存储过程如下界面：

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:

等待（唯一标识符）

我是原本想依靠其独特的标识符的表实现这一点。该过程的调用会等到的唯一标识符不存在于表中。不过，我不知道我怎么会让调用线程醒来时指定的唯一标识符从表中删除。

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)?

（为了避免死锁，我不是想包括在等待操作超时或具有的SqlCommand有超时）

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

推荐答案

看看系统存储过程：

 sp_getapplock

它可以帮助你完成你正在尝试做的。

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

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

你可以把它放在parametrizes唯一标识符...

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

这篇关于互斥存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！