避免并发存储过程 [英] Avoiding concurrency of Stored Procedures
问题描述
我有以下场景：
我需要以固定的时间间隔触发更新过程。但是某些过程可能会花费比间隔更长的时间。我想避免堆积对过程的调用（以防万一要在其之前的执行尚未完成之前启动）。
I need to fire updating procedures in fixed time intervals. But some of the procedure might take longer than the interval. I want to avoid stacking calls to procedures (in case one want to start before its previous execution hasn't been finished).
我的同事建议我在数据库中另外创建一个该表包含两列：
其中一个具有过程名称，而 IsActive 列（ bit ）。因此，在执行任何过程之前，我都要检查 IsActive 的对应值。如果它是 1 ，则中止执行。
My colleague advised me to create in database additional table with two columns:
one with name of procedures and IsActive column (bit). So, before executing any procedure, I check the corresponding value of IsActive. If it's 1, then abort execution.
现在，问题是：
执行时，我需要将 IsActive 的值设置为 1 过程，我尝试这样做：
when I get to execution, I need to set the value of IsActive to 1 for the procedure, which I try to do like this:
UPDATE ProcActivity SET IsActive = 1 WHERE ProcedureName = 'proc_name'
EXEC proc_name
UPDATE ProcActivity SET IsActive = 0 WHERE ProcedureName = 'proc_name'
但是，SQL正在执行批处理，因此在执行以下步骤之前，不可见 1 的值（不会提交 UPDATE ）完成。
But, SQL is executing batches, so the value of 1 isn't visible (the UPDATE isn't commited) until the procedure is finished.
那么，如何提交此 UPDATE ？我尝试了 COMMIT ，但是没有用...我不能使用 GO ，因为它包装在 IF 语句...
So, how to commit this UPDATE? I tried with COMMIT, but didn't work... I can't use GO, because it's wrapped in IF statement...
推荐答案
不要以这种方式使用事务因为可见性，除非您想使用额外的提示。
Don't use transactions this way because of visibility, unless you want to use extra hints. WHich I would not do personally.
如果您希望当前仅存储一个存储的proc执行，那么我会考虑 sp_getapplock 和 sp_releaseapplock 存储过程。
If you want "only one stored proc execution current" then I would consider sp_getapplock and sp_releaseapplock in the stored procedure.
这将强制执行单线程执行。
This will enforce force "single threaded" execution.
然后，要中止其他呼叫，请设置 @ LockTimeout = 0 ，因此，如果结果代码不同于零，那么您知道您需要中止当前通话。
And, to abort other calls, set the @LockTimeout=0, so if the result code is different from zero, then you know that you need to abort current call.
这篇关于避免并发存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
