互斥存储过程 [英] 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:

Wait（uniqueidentifier）

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

（为了避免死锁，我想要在等待操作中包含超时或使SqlCommand有超时）

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

推荐答案

查看系统存储过程：

 sp_getapplock

$ b

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

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

你可以把它放入一个proc参数化的uniqueidentifier ...

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屋！