嵌套存储过程中事务之间的关系？

Question

我把交易放在我所有的“设定”程序中。没问题。一切正常。

在这种情况下，我需要一个设定的过程，谢天谢地，只调用另一个过程一次，否则可能会让事情变得更加复杂。

所以快乐的沐浴就是这样。

• 我在 ProcA 中并开始交易。
• 它调用 ProcB 并启动一个事务。
• ProcB 成功并提交。
• ProcA 成功并提交。

但是，如果 ProcB 失败、回滚并重新抛出错误，会发生什么情况。它应该也会导致 ProcA 回滚，对吗？

如果 ProcB 成功并提交，然后 ProcA 随后失败并回滚怎么办……ProcB 中发生的事情会回滚吗？或者它是否已承诺？

我需要这两者一起工作，要么都成功，要么失败并且都回滚。确保这种情况发生的最佳方法是什么？

我正在使用 Microsoft SQL Server 2008 R2 (SP1)

注意：如果 ProcB 需要事务，因为可以在没有 ProcA 包装它的情况下调用它。从技术上讲，ProcA 并不总是调用 ProcB（取决于输入）。

Answer 1

下面是一个简单的演示，展示了嵌套事务会发生什么：

CREATE TABLE TranTest (Field1 INTEGER)

BEGIN TRANSACTION
SELECT @@TRANCOUNT -- 1 open transaction
INSERT TranTest VALUES (1)

    BEGIN TRANSACTION
    SELECT @@TRANCOUNT -- 2 open transactions
    INSERT TranTest VALUES (2)
    ROLLBACK TRANSACTION -- this rolls back ALL transaction

SELECT @@TRANCOUNT -- 0 open transactions (you may have expected 1?)
SELECT * FROM TranTest -- No rows

如果您执行了 COMMIT TRANSACTION，而不是上面的 ROLLBACK，则实际上除了递减 @@TRANCOUNT 之外什么也不做。因此，您需要提交外部事务（这会将两行都提交到表中），或者执行 ROLLBACK ，这将导致没有行提交到表中。

以下是有关嵌套事务的 MSDN 参考： http://msdn.microsoft.com/en -us/library/ms189336.aspx

Answer 2

只需使用 XACT_ABORT ON 即可。运行以下脚本并亲自查看：

CREATE DATABASE ak_test;
GO
USE ak_test;
GO
CREATE TABLE dbo.a(i INT CONSTRAINT a_CannotInsertNegative CHECK(i>=0));
GO
CREATE TABLE dbo.b(i INT CONSTRAINT b_CannotInsertNegative CHECK(i>=0));
GO

CREATE PROCEDURE dbo.innerProc @i INT
AS
SET XACT_ABORT ON ;
BEGIN TRAN
INSERT b(i)VALUES(@i);
COMMIT;
GO

CREATE PROCEDURE dbo.outerProc @i1 INT, @i2 INT, @i3 INT
AS
SET XACT_ABORT ON ;
BEGIN TRAN
INSERT a(i)VALUES(@i1);
EXEC innerProc @i=@i2;
INSERT a(i)VALUES(@i3);
COMMIT;
GO

-- succeeds
EXEC dbo.outerProc 1, 2, 3;
SELECT * FROM dbo.a;
SELECT * FROM dbo.b;
GO
-- inner proc fails
EXEC dbo.outerProc 2, -3, 4;
GO
SELECT * FROM dbo.a;
SELECT * FROM dbo.b;
GO
-- second insert in outer proc fails
EXEC dbo.outerProc 3, 4, -5;
GO
SELECT * FROM dbo.a;
SELECT * FROM dbo.b;

Answer 3

我对事务很偏执（有一次该事务在生产中处于打开状态，半小时内没有人注意到......）所以我会像这样扭曲潜在内部事务：

CREATE PROCEDURE etcetc

...

DECLARE @IsTransaction bit = 0

IF @@trancount > 0
 BEGIN
    BEGIN TRANSACTION
    SET @IsTransaction = 1
 END

...

IF @IsTransaction = 1
 BEGIN
    COMMIT
    --  or ROLLBACk, as necessary
 END

所有事务处理（以及处理事务中发生的错误）必须在启动事务的任何级别进行处理。

（还有其他人注意到 BOL 实际上并没有说明当您向非最外层事务的指定事务发出 ROLLBACK 时会发生什么吗？他们确实阐明了所有其他排列......）