在事务处理 SQL 中回滚存储过程中的事务

Question

我是使用Transact-SQL的新手，我有一个关于如何处理嵌套存储过程中的事务的问题。

考虑以下示例，其中我们创建一个示例表，如下所示：

CREATE TABLE EXAMPLE_TABLE 
(
ID INT, 
NAME VARCHAR(255)
); 

然后，我们创建一个没有参数的存储过程。此存储过程涉及从上面将值插入到表中。

CREATE PROCEDURE SP1
AS
BEGIN
BEGIN TRANSACTION
INSERT INTO EXAMPLE_TABLE (ID, NAME) 
VALUES (1, 'BOB')
COMMIT TRANSACTION;
END;

然后，我们使用一个调用第一个存储过程的参数创建第二个存储过程。

CREATE PROCEDURE sp2 
@EXAMPLE INT
AS
BEGIN
BEGIN TRANSACTION
EXEC SP1
IF (@EXAMPLE < 10) 
ROLLBACK TRANSACTION; 
ELSE 
COMMIT TRANSACTION;
END; 

然后我们调用第二个存储过程，如下所示：

EXEC sp2 @EXAMPLE = 5; 

在此执行结束时，值是否已添加到EXAMPLE_TABLE？还是外部存储过程中的回滚是否意味着所有内容都已回滚，并且未提交任何内容？

Answer 1

事务是有作用域的，因此事务中的任何内容都会一起提交/回滚。因此，@example变量的值为 5 会阻止将记录添加到EXAMPLE_TABLE中。您可以查看此小提琴以进行演示。

我要补充一点，如果此示例与您将要编写的实际代码相似，我建议只检查变量值并决定是否首先运行插入存储过程。

Answer 2

亚伦回答的结论是正确的，但推理有点误导。

事务并没有以您通常认为的范围方式真正"限定范围"。最外层的begin tran当然会开始交易。但是任何嵌套begin tran除了增加@@trancount之外并没有真正做任何事情。然后，当你commit时，除非@@trancount是 1，否则这不会真正提交任何东西。只有最外层的commit才是"真正的"commit.最后，rollback将回滚所有内容，而不仅仅是当前"嵌套最多"的事务，将@@trancount返回到 0。此时，如果您尝试commit或rollback，则会收到错误：

begin tran
print @@trancount
begin tran
print @@trancount
rollback
print @@trancount
commit

1
2
0
Msg 3902, Level 16, State 1, Line 61
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

出于这个原因，作为实际编码交易时的风格指南，我强烈建议不要将begin tran视为需要缩进的块的开始。将begin tran、commit和rollback视为常规语句，而不是块的开始和结束。

此行为的唯一例外是当您开始命名事务时，在这种情况下，您可以回滚到该命名事务的开头。