包含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)，说明The ROLLBACK TRANSACTION request has no corresponding 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 "的存储过程"MySubProcedure"引发MyStoredProcedure的错误时，我可以确定以前在MyStoredProcedure中完成的所有操作都会回滚，所有由Linq进行的Linq操作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的文章
• 错误266
• 触发回滚

说明:

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

• all TXN begin and commit/rollbacks must be paired so that @@TRANCOUNT is the same on entry and exit

@@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

SET XACT_ABORT ON suppresses error 266 caused by mismatched @@TRANCOUNT
And also deals with issues like this "SQL Server Transaction Timeout" on dba.se

这允许客户端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)

用法:

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

摘要

• 因此，创建的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屋！