T-SQL存储过程执行'原子'吗？

Let's say I have a simple stored procedure that looks like this (note: this is just an example, not a practical procedure):

假设我有一个看起来像这样的简单存储过程（注意：这只是一个例子，不是一个实际的过程）：

CREATE PROCEDURE incrementCounter AS

DECLARE @current int
SET @current = (select CounterColumn from MyTable) + 1

UPDATE
    MyTable
SET
    CounterColumn = current
GO

We're assuming I have a table called 'myTable' that contains one row, with the 'CounterColumn' containing our current count.

我们假设我有一个名为'myTable'的表，其中包含一行，'CounterColumn'包含我们当前的计数。

Can this stored procedure be executed multiple times, at the same time?

这个存储过程可以同时执行多次吗？

i.e. is this possible:

即这是可能的：

I call 'incrementCounter' twice. Call A gets to the point where it sets the 'current' variable (let's say it is 5). Call B gets to the point where it sets the 'current' variable (which would also be 5). Call A finishes executing, then Call B finishes. In the end, the table should contain the value of 6, but instead contains 5 due to the overlap of execution

我称之为'incrementCounter'两次。调用A到达设置'当前'变量的位置（假设它是5）。调用B到达设置'current'变量（也是5）的点。呼叫A完成执行，然后呼叫B结束。最后，该表应包含值6，但由于执行重叠而包含5

5 个解决方案

#1

This is for SQL Server.

这适用于SQL Server。

Each statement is atomic, but if you want the stored procedure to be atomic (or any sequence of statements in general), you need to explicitly surround the statements with

每个语句都是原子的，但如果您希望存储过程是原子的（或者通常是任何语句序列），则需要使用

BEGIN TRANSACTION
Statement ...
Statement ...
COMMIT TRANSACTION

BEGIN TRANSACTION Statement ... Statement ... COMMIT TRANSACTION

(It's common to use BEGIN TRAN and END TRAN for short.)

（通常使用BEGIN TRAN和END TRAN。）

Of course there are lots of ways to get into lock trouble depending what else is going on at the same time, so you may need a strategy for dealing with failed transactions. (A complete discussion of all the circumstances that might result in locks, no matter how you contrive this particular SP, is beyond the scope of the question.) But they will still be resubmittable because of the atomicity. And in my experience you'll probably be fine, without knowing about your transaction volumes and the other activities on the database. Excuse me for stating the obvious.

当然，根据其他正在发生的事情，有很多方法可以解决锁定问题，因此您可能需要一种策略来处理失败的事务。（完全讨论可能导致锁定的所有情况，无论你如何设计这个特定的SP，都超出了问题的范围。）但是由于原子性，它们仍然会被重新提交。根据我的经验，您可能会很好，不知道您的交易量和数据库上的其他活动。请原谅我说明显的。

Contrary to a popular misconception, this will work in your case with default transaction level settings.

与流行的误解相反，这将适用于您的默认事务级别设置。

#2

In addition to placing the code between a BEGIN TRANSACTION and END TRANSACTION, you'd need to ensure that your transaction isolation level is set correctly.

除了将代码放在BEGIN TRANSACTION和END TRANSACTION之间之外，您还需要确保正确设置事务隔离级别。

For example, SERIALIZABLE isolation level will prevent lost updates when the code runs concurrently, but READ COMMITTED (the default in SQL Server Management Studio) will not.

例如，SERIALIZABLE隔离级别将防止代码并发运行时丢失更新，但READ COMMITTED（SQL Server Management Studio中的默认值）不会。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

As others have already mentioned, whilst ensuring consistency, this can cause blocking and deadlocks and so may not be the best solution in practice.

正如其他人已经提到的那样，在确保一致性的同时，这可能导致阻塞和死锁，因此可能不是实践中的最佳解决方案。

#3

I use this method

我用这种方法

CREATE PROCEDURE incrementCounter
AS

DECLARE @current int

UPDATE MyTable
SET
  @current = CounterColumn = CounterColumn + 1

Return @current

this procedure do all two command at one time and it is isolate from other transaction.

此过程一次执行所有两个命令，它与其他事务隔离。

#4

Maybe I'm reading too much into your example (and your real situation may be significantly more complicated), but why wouldn't you just do this in a single statement?

也许我在你的例子中读得太多了（你的真实情况可能要复杂得多），但为什么你不能在一个声明中这样做呢？

CREATE PROCEDURE incrementCounter AS

UPDATE
    MyTable
SET
    CounterColumn = CounterColumn + 1

GO

That way, it's automatically atomic and if two updates are executued at the same time, they'll always be ordered by SQL Server so as to avoid the conflict you describe. If, however, your real situation is much more complicated, then wrapping it in a transaction is the best way to do this.

这样，它是自动原子的，如果同时执行两个更新，它们将始终由SQL Server排序，以避免您描述的冲突。但是，如果您的实际情况要复杂得多，那么将其包装在事务中是最好的方法。

However, if another process has enabled a "less safe" isolation level (like one that allows dirty reads or non-repeatable reads), then I don't think a transaction will protect against this, as another process can see into the partially updated data if it's elected to allow unsafe reads.

但是，如果另一个进程启用了“安全性较低”的隔离级别（如允许脏读或不可重复读取的那个），那么我认为事务不会对此进行保护，因为另一个进程可以看到部分更新数据，如果它被选为允许不安全的读取。

#5

Short answer to your question is YES it can and will come up short. If you want to block concurrent execution of stored procedures start a transaction and update the same data in every execution of the stored procedure before continuing to do any work within the procedure.

对你的问题的简短回答是肯定它可以并且将会缩短。如果要阻止并发执行存储过程，请在继续执行过程中的任何工作之前启动事务并在每次执行存储过程时更新相同的数据。

CREATE PROCEDURE ..
BEGIN TRANSACTION
UPDATE mylock SET ref = ref + 1
...

This will force other concurrent executions to wait their turn since they will not be able to change 'ref' value until the other transaction(s) complete and associated update lock is lifted.

这将强制其他并发执行等待轮到他们，因为他们将无法更改'ref'值，直到其他事务完成并且关联的更新锁被解除。

In general it is a good idea to assume result of any and all SELECT queries are stale before they are ever even executed. Using "heavy" isolation levels to workaround this unfortunate reality severely limits scalability. Much better to structure changes in a way which make optimistic assumptions about state of system you expect to exist during the update so when your assumption fail you can try again later and hope for a better outcome. For example:

一般来说，假设任何和所有SELECT查询的结果在执行之前都是陈旧的，这是一个好主意。使用“重”隔离级别来解决这个不幸的现实严重限制了可扩展性。更好地构建更改的方式可以对更新期间存在的系统状态做出乐观的假设，因此当您的假设失败时，您可以稍后重试并希望获得更好的结果。例如：

UPDATE
    MyTable
SET
    CounterColumn = current 
WHERE CounterColumn = current - 1

Using your example with added WHERE clause this update does not affect any rows if assumption about its current state fails. Check @@ROWCOUNT to test number of rows and rollback or some other action as appropriate while it differs from expected outcome.

使用添加了WHERE子句的示例，如果假设其当前状态失败，则此更新不会影响任何行。检查@@ ROWCOUNT以测试行数和回滚或其他适当的操作，但它与预期结果不同。

#1