防止在Asp.net中与ado.net并发执行SQL Server存储过程 [英] Prevent concurrent execution of SQL Server stored procedure with ado.net in Asp.net
问题描述
我想防止两个用户同时执行相同的存储过程。如果有两个asp.net请求执行该存储过程，则这些请求应依次串行执行。
I want to prevent two users from executing the same stored procedure concurrently. If two asp.net requests come in to execute that stored procedure, then those should execute in serial manner one after another.
SQL Server数据库，执行由ado处理。
SQL Server database and execution is handled by ado.net.
以下任何方法都可以帮助实现这一目标吗？什么是最合适的方法？还有其他方法可以实现相同的目的吗？
Will any of below methods help to achieve this ? What is the most suitable method ? Is there any other ways to achieve the same ?
-
通过将隔离级别设置为Serializable，使用ado.net事务执行存储过程。
Execute the stored procedure with ado.net transaction by setting isolation level to Serializable
在存储过程中使用 sp_getapplock 并在最后释放
Use sp_getapplock inside stored procedure and release at the end
推荐答案
通过设置
与ado.net事务执行存储过程隔离级别为Serializable
Execute the stored procedure with ado.net transaction by setting isolation level to Serializable
是的，您可以在ado.net事务中使用Serializable隔离级别。它是最高的隔离级别，它依赖于悲观的并发控制和控制。通过假设两个事务可能尝试更新相同的数据并使用锁来确保一致性，从而确保一致性。 一个事务必须等待另一个事务完成，否则它们可能会死锁。
Yes you can you can use the Serializable isolation level with your ado.net transaction. It is the highest isolation level and it relies on pessimistic concurrency control & guarantees consistency by assuming that two transactions may try to update the same data and uses locks to ensure that they do not. One transaction must wait for the other to complete and they can deadlock.
在存储过程中使用sp_getapplock并释放最后
Use sp_getapplock inside stored procedure and release at the end
是的，您可以使用SQL Server应用程序锁sp_getapplock。您将必须使用sp_releaseapplock
Yes you can use SQL Server application locks sp_getapplock. You will have to release the lock with sp_releaseapplock
释放锁。另一种选择是使用全局临时表。
Another option is to use Global Temporary Tables.
这篇关于防止在Asp.net中与ado.net并发执行SQL Server存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
