T-SQL 存储过程执行是“原子的"吗? [英] Is T-SQL Stored Procedure Execution 'atomic'?

问题描述

假设我有一个像这样的简单存储过程(注意:这只是一个例子，不是一个实际的过程):

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

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

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

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

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

即这可能吗:

我调用了两次incrementCounter".调用 A 到达它设置当前"变量的地步(假设它是 5).调用 B 到达设置当前"变量(也将是 5)的点.调用 A 完成执行，然后调用 B 完成.最后，该表应该包含值 6，但由于执行重叠而包含 5

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

推荐答案

这是针对 SQL Server 的.

This is for 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 TRAN 和 END TRAN.)

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

当然，根据同时发生的其他事情，有很多方法会导致锁定问题，因此您可能需要一种处理失败事务的策略.(完整讨论可能导致锁定的所有情况，无论您如何设计这个特定的 SP，都超出了问题的范围.)但由于原子性，它们仍然可以重新提交.根据我的经验，您可能会没事，无需了解您的交易量和数据库上的其他活动.请原谅我说的太明显了.

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.

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

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

这篇关于T-SQL 存储过程执行是“原子的"吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！