包含尝试捕获回滚模式的嵌套存储过程？

Question

包含尝试捕获回滚模式的嵌套存储过程？我对以下模式的副作用和潜在问题感兴趣：CREATE PROCEDURE [Name]ASBEGIN     BEGIN TRANSACTION     BEGIN TRY        [...Perform work, call nested procedures...]     END TRY    BEGIN CATCH        ROLLBACK TRANSACTION         RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]     END CATCHEND据我所知，该模式在与单个过程一起使用时是合理的-该过程要么完成其所有语句而没有错误，要么回滚所有操作并报告错误。但是，当一个存储过程调用另一个存储过程来执行某个子单元的工作时(但有一项了解，即有时会单独调用较小的过程)，我看到一个与回滚有关的问题-发出一条信息性消息(级别16)，声明The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION...我认为这是因为子过程中的回滚总是回滚最外层的事务，而不仅仅是在子过程中开始的事务。如果发生任何错误(并且向客户端报告为SQL错误)，我确实希望整个事件回滚并中止，我只是不确定来自试图回滚一个已经回滚的事务的外部层产生的所有副作用。也许是一张支票@@TRANCOUNT在每个尝试捕捉层做回滚之前？最后是客户端(Linq2SQL)，它有自己的事务层：try{     var context = new MyDataContext();     using (var transaction = new TransactionScope())     {                    // Some Linq stuff         context.SubmitChanges();         context.MyStoredProcedure();         transactionComplete();     }}catch{     // An error occured!}如果存储过程“MySubProcedure”调用内MyStoredProcedure会引发一个错误，我是否可以确保以前在MyStoredProcedure中完成的所有操作都将被回滚，SubmitChanges所做的所有Linq操作都将被回滚，并最终记录该错误？或者我需要改变我的模式，以确保整个操作是原子的，同时仍然允许单独使用子部分(即子过程应该仍然具有相同的原子保护)。

Answer 1

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

这是设计用来处理

• 保罗·兰德尔的文章

  “SQL Server中没有嵌套事务”

• 误差266
• 触发回滚

解释：

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

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

• BEGIN TRAN增量@@TRANCOUNT

• COMMIT减量@@TRANCOUNT

• ROLLBACK回报@@TRANCOUNT降至零

• 你不能减少@@TRANCOUNT适用于当前范围
  这就是你认为的“内部交易”

• SET XACT_ABORT ON取消由不匹配引起的错误266。@@TRANCOUNT
  也处理像这样的问题“SQLServer事务超时”日照

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

用法：

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

摘要

• 所以不要创建比你需要的更多的txn

密码

CREATE PROCEDURE [Name]ASSET XACT_ABORT, NOCOUNT ONDECLARE @starttrancount intBEGIN TRY    SELECT @starttrancount = @@TRANCOUNT    
IF @starttrancount = 0
        BEGIN TRANSACTION

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

    IF @starttrancount = 0 
        COMMIT TRANSACTIONEND TRYBEGIN 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..然而，它让我感觉更好，看上去很奇怪没有，并考虑到一些情况下，你不想要它。

• 鲁萨努有一个相似壳它使用保存点。我更喜欢原子DB调用，不像他们的文章那样使用部分更新

Answer 2

为了解决返回@AlexKuznetsov所提到的错误号和行号的问题，可以这样引发错误：

DECLARE @ErrorMessage NVARCHAR(4000)

DECLARE @ErrorSeverity INT

DECLARE @ErrorState INT

DECLARE @ErrorLine INT

DECLARE @ErrorNumber INT

SELECT @ErrorMessage = ERROR_MESSAGE(),

@ErrorSeverity = ERROR_SEVERITY(),

@ErrorState = ERROR_STATE(),

@ErrorNumber = ERROR_NUMBER(),

@ErrorLine = ERROR_LINE()

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber, @ErrorLine)