如何使用相同的参数序列化存储过程的多个执行？

I have a couple hundred line stored procedure that takes a single parameter (@id) and is heavily simplified to something like:

我有几百行存储过程，它接受一个参数（@id），大大简化为：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

INSERT INTO #new_result
EXEC pr_do_a_ton_of_calculations

DELETE FROM result WHERE id = @id

INSERT INTO result
SELECT * FROM #new_result

Multiple processes may invoke this procedure concurrently, with the same parameters. I'm experiencing that both executions delete the rows one after the other, and then try to insert the same data one after the other. The result is that one errors out, because it's inserting duplicate data and violating a unique constraint.

多个进程可以使用相同的参数同时调用此过程。我遇到两个执行都一个接一个地删除行，然后尝试一个接一个地插入相同的数据。结果是一个错误输出，因为它插入了重复数据并违反了唯一约束。

Ideally, I'd like to ensure that two connections executing the procedure with the same @id parameter will execute both the DELETE and INSERT serially, without locking the entire table. It's also fine if the two procedures are completely serialized, as long as they aren't preventing the execution of other invocations with a different parameter.

理想情况下，我想确保执行具有相同@id参数的过程的两个连接将串行执行DELETE和INSERT，而不锁定整个表。如果两个过程完全序列化，只要它们不阻止使用不同参数执行其他调用，这也没关系。

Is there any way I can achieve this?

有什么办法可以实现吗？

4 个解决方案

#1

Add this to the beginning of your stored procedure:

将其添加到存储过程的开头：

DECLARE  @lid INT

SELECT  @lid = id
FROM    result WITH (UPDLOCK, ROWLOCK)
WHERE   id = @id

and get rid of the READ UNCOMMITTED above.

并摆脱上面的READ UNCOMMITTED。

Make sure your id is indexed. If it's a reference to another table where it is a PRIMARY KEY, use the lock on that table instead.

确保您的ID已编入索引。如果它是对另一个表是PRIMARY KEY的引用，请使用该表上的锁。

Better yet, use application locks (sp_getapplock).

更好的是，使用应用程序锁（sp_getapplock）。

#2

You can use application locks, for example:

您可以使用应用程序锁，例如：

DECLARE @ResourceName VARCHAR(200) = 'MyResource' + CONVERT(VARCHAR(20), @id)
EXEC sp_getapplock @Resource = @ResourceName, @LockMode = 'Exclusive'

---- Do your thing ----

DECLARE @ResourceName VARCHAR(200) = 'MyResource' + CONVERT(VARCHAR(20), @id)
EXEC sp_releaseapplock @Resource = @ResourceName, @LockMode = 'Exclusive'

#3

If you need these things to happen in a guaranteed order based on receipt of request, Service Broker will manage that for you and throw in a bunch of other benefits, too. Setting it up takes some doing, but "An Introduction to Asynchronous Processing with Service Broker" by Jonathan Kehayias is the best intro I've found. You would set your "pr_do_a_ton_of_calculations" as the activation procedure for the queue and add the additional commands for handling Service Broker conversations.

如果您需要根据收到的请求以保证的顺序发生这些事情，Service Broker将为您管理这些事情，并提供一系列其他好处。设置它需要一些工作，但Jonathan Kehayias的“使用Service Broker进行异步处理简介”是我发现的最佳介绍。您可以将“pr_do_a_ton_of_calculations”设置为队列的激活过程，并添加用于处理Service Broker对话的其他命令。

This WILL make the stored procedures operate asynchronously from the caller, so if the call is being made from another stored procedure, the processing would happen off of that thread. This can actually be to your advantage if waiting for this processing is slowing things down.

这将使存储过程从调用者异步操作，因此如果从另一个存储过程调用，则处理将在该线程之外发生。如果等待这种处理会减慢速度，那么这实际上对您有利。

#4

This may be just getting rid of the error and not fixing the problem, but

这可能只是摆脱错误而不是解决问题，但是

INSERT INTO result
SELECT * 
  FROM #new_result 
  left join result 
    on #new_result.PK = result.PK  
 where result.PK is null

Even without concurrency issues you would probably be better off breaking that up into delete, update, insert as an update is more efficient than a delete and insert and does not fragment index as much

即使没有并发问题，您最好还是将其分解为删除，更新，插入，因为更新比删除和插入更有效，并且不会将索引分段为多

delete d
  from result d
  left join #new_result 
    on #new_result.PK = d.PK  
 where #new_result.PK is null 
   and d.ID = @ID;

update result
SELECT result.colx = #new_result.colx, result.coly = #new_result.coly
  FROM #new_result 
  join result 
    on #new_result.PK = result.PK  

INSERT INTO result
SELECT * 
  FROM #new_result 
  left join result 
    on #new_result.PK = result.PK  
 where result.PK is null

#1