包含 TRY CATCH ROLLBACK 模式的嵌套存储过程? [英] Nested stored procedures containing TRY CATCH ROLLBACK pattern?

问题描述

我对以下模式的副作用和潜在问题感兴趣:

I'm interested in the side effects and potential problems of the following pattern:

CREATE PROCEDURE [Name]
AS
BEGIN
    BEGIN TRANSACTION
    BEGIN TRY
        [...Perform work, call nested procedures...]
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
    END CATCH
END

据我所知，这种模式在与单个过程一起使用时是合理的 - 该过程要么完成其所有语句而不会出错，要么将回滚所有操作并报告错误.

To the best of my understanding this pattern is sound when used with a single procedure - the procedure will either complete all of its statements without error, or it will rollback all actions and report the error.

但是，当一个存储过程调用另一个存储过程来执行一些子工作单元时(理解有时会单独调用较小的过程)我看到一个与回滚相关的问题 - 一条信息性消息(级别 16) 发出声明 ROLLBACK TRANSACTION 请求没有相应的 BEGIN TRANSACTION..我认为这是因为子过程中的回滚总是回滚最外层的事务，而不仅仅是子过程中开始的事务.

However when one stored procedure calls another stored procedure to do some sub-unit of work (with the understanding that the smaller procedure is sometimes called on its own) I see an issue coming about with relation to rollbacks - an informational message (Level 16) is issued stating The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.. This I assume is because the rollback in the sub-procedure is always rolling back the outer-most transaction, not just the transaction started in the sub-procedure.

如果发生任何错误，我确实希望整个事情回滚并中止(并且该错误作为 SQL 错误报告给客户端)，我只是不确定来自外层的所有副作用试图回滚已经回滚的事务.也许在每个 TRY CATCH 层回滚之前检查 @@TRANCOUNT?

I do want the whole thing rolled back and aborted if any error occurs (and the error reported to the client as an SQL error), I'm just not sure of all the side effects that come from the outer layers trying to rollback a transaction that has already been rolled back. Perhaps a check of @@TRANCOUNT before doing a rollback at each TRY CATCH layer?

最后是客户端(Linq2SQL)，它有自己的事务层:

Finally there is the client end (Linq2SQL), which has it's own transaction layer:

try
{
    var context = new MyDataContext();
    using (var transaction = new TransactionScope())
    {       
            // Some Linq stuff
        context.SubmitChanges();
        context.MyStoredProcedure();
        transactionComplete();
    }
}
catch
{
    // An error occured!
}

如果名为 inside MyStoredProcedure 的存储过程MySubProcedure"引发错误，我是否可以确定之前在 MyStoredProcedure 中完成的所有操作都将回滚，所有由SubmitChanges 会被回滚，最后那个错误会被记录下来?或者我需要在我的模式中改变什么以确保整个操作是原子的，同时仍然允许单独使用子部分(即子过程应该仍然具有相同的原子保护)

In the event that a stored procedure, "MySubProcedure", called inside MyStoredProcedure raises an error, can I be sure that everything previously done in MyStoredProcedure will be rolled back, all the Linq operations made by SubmitChanges will be rolled back, and finally that the error will be logged? Or what do I need to change in my pattern to ensure the whole operation is atomic, while still allowing the child parts to be used individually (i.e. the sub-procedures should still have the same atomic protection)

推荐答案

这是我们的模板(已删除错误日志)

This is our template (error logging removed)

这是为了处理

• Paul Randal 的文章 "SQL Server 中没有嵌套事务之类的东西"
• 错误 266
• 触发回滚

说明:

• 所有 TXN 开始和提交/回滚必须配对，以便 @@TRANCOUNT 在进入和退出时相同

@@TRANCOUNT 的不匹配导致错误 266，因为

mismatches of @@TRANCOUNT cause error 266 because

• BEGIN TRAN 递增 @@TRANCOUNT

COMMIT 递减 @@TRANCOUNT

ROLLBACK 将 @@TRANCOUNT 归零

您不能为当前范围减少 @@TRANCOUNT
这就是你认为的内部交易"

You can not decrement @@TRANCOUNT for the current scope
This is what you'd think is the "inner transaction"

SET XACT_ABORT ON 抑制由 @@TRANCOUNT
不匹配引起的错误 266并且还处理 dba.se 上的 "SQL Server Transaction Timeout" 等问题

这允许客户端 TXN(如 LINQ)单个存储过程可能是分布式或 XA 事务的一部分，或者只是在客户端代码中启动的一个(比如 .net TransactionScope)

This allows for client side TXNs (like LINQ) A single stored procedure may be part of distributed or XA transaction, or simply one initiated in client code (say .net TransactionScope)

用法:

• 每个存储过程必须符合相同的模板

总结

• 所以不要创建超过你需要的 TXN

代码

CREATE PROCEDURE [Name]
AS
SET XACT_ABORT, NOCOUNT ON

DECLARE @starttrancount int

BEGIN TRY
    SELECT @starttrancount = @@TRANCOUNT

    IF @starttrancount = 0
        BEGIN TRANSACTION

       [...Perform work, call nested procedures...]

    IF @starttrancount = 0 
        COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 AND @starttrancount = 0 
        ROLLBACK TRANSACTION;
    THROW;
    --before SQL Server 2012 use 
    --RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
END CATCH
GO

注意事项:

• 回滚检查实际上是多余的，因为SET XACT_ABORT ON.然而，它让我感觉更好，没有它看起来很奇怪，并且允许你不想要它的情况

• The rollback check is actually redundant because of SET XACT_ABORT ON. However, it makes me feel better, looks odd without, and allows for situations where you don't want it on

Remus Rusanu 有一个 类似的外壳，使用保存点.我更喜欢原子数据库调用，而不像他们的文章那样使用部分更新

Remus Rusanu has a similar shell that uses save points. I prefer an atomic DB call and don't use partial updates like their article

这篇关于包含 TRY CATCH ROLLBACK 模式的嵌套存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！