SQL Server中的嵌套事务

Question

想象下面的情景：

我正在使用SQL Server 2005。我有一个事务，其中调用了除其他SQL语句外，还有一个内部有事务的存储过程。外部事务有时会失败，并在成功调用和提交存储过程后回滚。

我的问题是，存储过程的事务是否也会回滚？

- David Espart

Answer 1

在嵌套事务中，提交操作不会将除顶层事务之外的任何更改写入磁盘。然而，回滚操作无论事务级别如何都可以执行，所以它将回滚内部事务。

- MatthieuF

---

回滚将回滚所有事务，而非仅限于上一个事务。例如：http://www.emoreau.com/Entries/Articles/2011/02/EricMoreau1.gif - Pure.Krome

@Pure.Krome 是正确的。嵌套事务将不能做到你想要的。请参阅SQL Server Myth a Day series。 - Mike Bailey

我对此感到困惑，于是在谷歌上搜索并来到了这里。以下语句会抛出错误：begin transaction begin transaction select 1 rollback rollback。但如果我执行commit rollback，则可以正常工作。我猜这是因为第一个rollback之后，我不再处于事务中。第一个rollback实际上回滚了两个begin。 - TizzyFoe

这里评论中提到的两篇文章似乎都已失效。第一篇没有被归档，但第二篇有：https://web.archive.org/web/20121223021357/http://www.sqlskills.com/blogs/paul/post/a-sql-server-dba-myth-a-day-(2630)-nested-transactions-are-real.aspx - avandesa

Answer 2

完全正确，顶层事务将拥有所有数据更改直到提交或回滚。

但是，我鼓励您仔细考虑事务模型。在您的系统中存在这种情况越多，您面临的锁定问题就越大。此外，该过程的计算开销也会增加。

值得注意的是，当我合理化SQL时，我经常发现已经实现了不需要事务的地方。我鼓励您（以及任何使用事务的人）认真考虑在每个上下文中使用它们的原因，如果未实现事务会发生什么。这只是我的2美分！

- Arkios

Answer 3

是的，存储过程将被回滚。

以下是您代码的总体流程：

BEGIN TRY

    BEGIN TRANSACTION

    EXEC SotredProcedureName

    --Do some other activity

    COMMIT TRANSACTION
END TRY
BEGIN CATCH

    --IF an error occurs then rollback the current transaction, which includes the stored procedure code.
    ROLLBACK TRANSACTION

END CATCH

祝好，约翰

- John Sansom

Answer 4

我已经尝试在存储过程usp_test中使用begin tran和commit。
使用以下其他查询执行这些存储过程：

update x set name='xxx'
select * from x---contains 'xxx'
begin tran
update x set name='yyy'
select * from x---contains 'yyy'
exec usp_test
select * from x---contains 'zzz' inside the sp
rollback tran

在执行上述查询时，x表中的名称必须为“xxx”，而不是“zzz”，因为第一个begin tran已经回滚了，即使sp tran提交了。因此，首先begin tran拥有数据更改。

- Jayaprabha