스토어드 프로시저에서 "SET XACT_ABORT ON"을 사용하면 어떤 이점이 있습니까?

스토어드 프로시저에서 "SET XACT_ABORT ON"을 사용하면 어떤 이점이 있습니까?

저장 프로시저에서 사용하는 이점은 무엇입니까?

SET XACT_ABORT ON는 런타임 오류가 발생했을 때 트랜잭션 전체를 롤백하고 배치를 중단하도록 SQL Server에 지시합니다.는 SQL Server에서는).XACT_ABORT OFF□□□□□□□□★

에 " "는 트랜잭션을 열어 둡니다.SET XACT_ABORT ON는 명시적인 트랜잭션이 있는 모든 스토어드 프로시저에서 권장됩니다(특별한 이유가 없는 한).애플리케이션이 오픈 트랜잭션과의 접속에 대해 작업을 실행하는 것은 치명적이기 때문입니다.

Dan Guzman's Blog에 아주 좋은 개요가 있습니다.

SET XACT_ABORT ON은 SQL 2k5에 BEGIN TRY/BEGIN CATCH가 추가되어 폐지되었다고 생각합니다.Transact-SQL의 예외 블록 이전에는 오류를 처리하는 것이 매우 어려웠고 불균형한 프로시저가 너무 흔했습니다(프로시저에서는 엔트리와 @TRANCOUNT가 다릅니다).

Transact-SQL 예외 처리가 추가되면 트랜잭션의 균형을 적절하게 유지할 수 있는 올바른 절차를 훨씬 쉽게 작성할 수 있습니다.예를 들어 예외 처리 및 중첩 트랜잭션에 이 템플릿을 사용합니다.

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch   
end
go

복구 가능한 오류 발생 시 자신의 작업만 롤백하는 원자성 프로시저를 작성할 수 있습니다.

Transact-SQL 절차가 직면하는 주요 문제 중 하나는 데이터의 순수성입니다.때로는 수신된 파라미터나 테이블의 데이터가 완전히 잘못되어 중복 키 오류, 참조 제약 오류, 검사 제약 오류 등이 발생합니다.결국 이것이 바로 이러한 제약 조건의 역할입니다. 이러한 데이터 순도 오류가 불가능하고 모두 비즈니스 논리에 의해 파악된다면 제약 조건은 모두 폐기됩니다(극적 과장이 효과를 위해 추가됨).XACT_ABORT가 ON일 경우 예외를 정상적으로 처리하는 예외 블록을 코드화할 수 있는 것이 아니라 트랜잭션 전체가 손실됩니다.일반적인 예로는 INSERT를 시도하여 PK 위반 시 UPDATE로 되돌리는 경우가 있습니다.

MSDN 인용:

SET XACT_ABORT가 ON일 때 Transact-SQL 문이 런타임 오류를 발생시키면 트랜잭션 전체가 종료되고 롤백됩니다.SET XACT_ABORT가 OFF일 경우 오류를 발생시킨 Transact-SQL 문만 롤백되고 트랜잭션 처리가 계속됩니다.

실제로는, 일부의 스테이트먼트가 실패해, 트랜잭션이 「부분적으로 완료」되는 일이 있어, 발신자에게는 이 에러의 징후가 없는 경우가 있습니다.

간단한 예:

INSERT INTO t1 VALUES (1/0)    
INSERT INTO t2 VALUES (1/1)    
SELECT 'Everything is fine'

이 코드는 XACT_ABORT OFF 상태에서 '성공적'으로 실행되며 XACT_ABORT ON으로 오류와 함께 종료됩니다(INSERT INTO t2는 실행되지 않고 클라이언트애플리케이션에서 예외가 발생합니다).

보다 유연한 접근방식으로 각 스테이트먼트(구) 후에 @@ERROR을 체크하거나 TRY를 사용할 수 있습니다.CATCH 블록(MSSQL2005+).개인적으로는 고도의 에러 처리를 실시할 필요가 없는 경우는, XACT_ABORT 를 유효하게 하는 것을 선호합니다.

클라이언트의 타임아웃과 그 처리에 XACT_ABORT를 사용하는 것에 대해서는 SQL Client 등의 클라이언트 API에서 타임아웃이 발생하는 것은 적어도1가지 타당한 이유가 있다고 생각합니다.그것은 SQL 서버 코드에서 발생하는 교착상태로부터 클라이언트애플리케이션 코드를 보호하기 위해서입니다.이 경우 클라이언트코드는 장애는 없지만 서버에서 명령어가 완료될 때까지 대기하면서 차단되지 않도록 스스로 보호해야 합니다.이와 반대로 클라이언트코드를 보호하기 위해 클라이언트타임아웃이 필요한 경우 XACT_ABORT ON은 서버 코드를 클라이언트 중단으로부터 보호해야 합니다.서버 코드를 실행하는 데 클라이언트가 기다리는 시간보다 오래 걸리는 경우입니다.

트랜잭션 관리에 사용되며 오류가 발생하면 트랜잭션이 롤백됩니다.

여기에 새 업데이트를 추가하는 중입니다.최신 MSDN 업데이트에서는 XACT_ABORT ON과 TRY/CATCH 블록을 모두 사용하는 방법을 보여 줍니다.MSDN 링크

    -- Check to see whether this stored procedure exists.  
IF OBJECT_ID (N'usp_GetErrorInfo', N'P') IS NOT NULL  
    DROP PROCEDURE usp_GetErrorInfo;  
GO  
  
-- Create procedure to retrieve error information.  
CREATE PROCEDURE usp_GetErrorInfo  
AS  
    SELECT   
         ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_LINE () AS ErrorLine  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_MESSAGE() AS ErrorMessage;  
GO  
  
-- SET XACT_ABORT ON will cause the transaction to be uncommittable  
-- when the constraint violation occurs.   
SET XACT_ABORT ON;  
  
BEGIN TRY  
    BEGIN TRANSACTION;  
        -- A FOREIGN KEY constraint exists on this table. This   
        -- statement will generate a constraint violation error.  
        DELETE FROM Production.Product  
            WHERE ProductID = 980;  
  
    -- If the DELETE statement succeeds, commit the transaction.  
    COMMIT TRANSACTION;  
END TRY  
BEGIN CATCH  
    -- Execute error retrieval routine.  
    EXECUTE usp_GetErrorInfo;  
  
    -- Test XACT_STATE:  
        -- If 1, the transaction is committable.  
        -- If -1, the transaction is uncommittable and should   
        --     be rolled back.  
        -- XACT_STATE = 0 means that there is no transaction and  
        --     a commit or rollback operation would generate an error.  
  
    -- Test whether the transaction is uncommittable.  
    IF (XACT_STATE()) = -1  
    BEGIN  
        PRINT  
            N'The transaction is in an uncommittable state.' +  
            'Rolling back transaction.'  
        ROLLBACK TRANSACTION;  
    END;  
  
    -- Test whether the transaction is committable.
    -- You may want to commit a transaction in a catch block if you want to commit changes to statements that ran prior to the error.
    IF (XACT_STATE()) = 1  
    BEGIN  
        PRINT  
            N'The transaction is committable.' +  
            'Committing transaction.'  
        COMMIT TRANSACTION;     
    END;  
END CATCH;  
GO

언급URL : https://stackoverflow.com/questions/1150032/what-is-the-benefit-of-using-set-xact-abort-on-in-a-stored-procedure