TRY CATCH ROLLBACK 패턴을 포함하는 중첩 저장 프로 시저?

TRY CATCH ROLLBACK 패턴을 포함하는 중첩 저장 프로 시저?

---

다음 패턴의 부작용과 잠재적 인 문제에 관심이 있습니다.

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

내가 이해하는 한이 패턴은 단일 프로 시저와 함께 사용할 때 적절합니다. 프로시 저는 오류없이 모든 명령문을 완료하거나 모든 작업을 롤백하고 오류를보고합니다.

그러나 하나의 저장 프로 시저가 다른 저장 프로 시저를 호출하여 일부 작업 단위를 수행 할 때 (작은 프로 시저가 때때로 자체적으로 호출된다는 점을 이해하고) 롤백과 관련하여 발생하는 문제-정보 메시지 (레벨 16)를 봅니다. 라는 문구가 발행 The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.됩니다. 이것은 하위 프로 시저의 롤백이 하위 프로 시저에서 시작된 트랜잭션뿐만 아니라 항상 가장 바깥 쪽 트랜잭션을 롤백하기 때문이라고 가정합니다.

오류가 발생하면 모든 것이 롤백되고 중단되기를 원합니다 (그리고 오류가 SQL 오류로 클라이언트에보고 됨), 트랜잭션을 롤백하려는 외부 계층에서 발생하는 모든 부작용이 확실하지 않습니다. 이미 롤백되었습니다. @@TRANCOUNT각 TRY CATCH 레이어에서 롤백을 수행하기 전에 확인 해야할까요?

마지막으로 자체 트랜잭션 계층이있는 클라이언트 끝 (Linq2SQL)이 있습니다.

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

MyStoredProcedure 내부 에서 호출 된 저장 프로 시저 "MySubProcedure" 가 오류를 발생시키는 경우 MyStoredProcedure에서 이전에 수행 한 모든 작업이 롤백되고 SubmitChanges에 의해 수행 된 모든 Linq 작업이 롤백되고 마지막으로 오류가 기록됩니까? 또는 전체 작업이 원자적임을 확인하면서 하위 부분을 개별적으로 사용할 수 있도록 패턴을 변경해야합니까? (예 : 하위 절차는 여전히 동일한 원자 적 보호를 가져야합니다)

---

이것은 우리의 템플릿입니다 (오류 로깅 제거됨).

이것은 처리하도록 설계되었습니다

• Paul Randal의 기사 "SQL Server에 중첩 된 트랜잭션은 없습니다."
• 오류 266
• 롤백 트리거

설명 :

• 모든 TXN 시작 및 커밋 / 롤백은 @@TRANCOUNT입력 및 종료시 동일 하도록 쌍을 이루어야합니다.

• @@TRANCOUNT원인 오류 266의 불일치

  • BEGIN TRAN 증분 @@TRANCOUNT

  • COMMIT 감소 @@TRANCOUNT

  • ROLLBACK반환 @@TRANCOUNT제로

• @@TRANCOUNT현재 범위 에서는 감소 할 수 없습니다.
  이것이 "내부 트랜잭션"이라고 생각하는 것입니다.

• SET XACT_ABORT ON불일치로 인한 오류 266을 억제 하고 dba.se의 "SQL Server 트랜잭션 시간 초과"@@TRANCOUNT
  와 같은 문제도 처리합니다.

• 이를 통해 클라이언트 측 TXN (예 : LINQ)이 가능합니다. 단일 저장 프로시 저는 분산 또는 XA 트랜잭션의 일부이거나 단순히 클라이언트 코드 (예 : .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

메모:

• 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 has a similar shell that uses save points. I prefer an atomic DB call and don't use partial updates like their article

---

I am not a Linq guy (and neither is Erland), but he wrote the absolute bibles on error handling. Outside of the complications Linq might add to your problem, all of your other questions should be answered here:

http://www.sommarskog.se/error_handling/Part1.html

(Old link: http://www.sommarskog.se/error_handling_2005.html)

---

To solve the issue of returning the error number and line number mentioned by @AlexKuznetsov, one can raise the error as such:

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)

---

-- @Amanda method above doesnt return correct error number

DECLARE  
  @ErrorMessage   nvarchar(4000),  
  @ErrorSeverity   int,  
  @ErrorState int,  
  @ErrorLine  int,  
  @ErrorNumber   int  

BEGIN TRY  
 SELECT 1/0; -- CATCH me  
END TRY  

BEGIN CATCH  

  DECLARE @err int = @@ERROR  

  PRINT @err           -- 8134, divide by zero  
  PRINT ERROR_NUMBER() -- 8134  

  SELECT  
    @ErrorMessage  = ERROR_MESSAGE(),  
    @ErrorSeverity = ERROR_SEVERITY(),  
    @ErrorState    = ERROR_STATE(),  
    @ErrorNumber   = ERROR_NUMBER(),  
    @ErrorLine     = ERROR_LINE()  

  -- error number = 50000 :(  
  RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber, @ErrorLine)  

END CATCH  

-- error number = 8134  
SELECT 1/0

---

In case no special error handling needed in CATCH except rethrow and stored procs call chain isn't too long it may be suitable to use such simple template:

create procedure someNestedSP
as
SET XACT_ABORT ON
begin transaction
-- do some work or call some other similar SP
commit transaction

It would also rollback root transaction with all "nested" ones in case of any error but the code is shorter and more straightforward than @gbn's solution. Still XACT_ABORT takes care of most issues mentioned there.

There may be addiotional overhead for transaction nesting but it may be not too high, I guess.

ReferenceURL : https://stackoverflow.com/questions/2073737/nested-stored-procedures-containing-try-catch-rollback-pattern