[Sequel Safe] 에러 핸들링 (下)

ErrorHandler 레이블

에러 핸들링을 위한 루틴은 아래와 같이 레이블(Label)로 만듭니다.

ErrorHandler:

IF XACT_STATE() <> 0

    ROLLBACK TRANSACTION;

 

IF @intReturnValue IS NULL OR @intReturnValue = 0

    EXEC @intReturnValue = dbo.P_AddErrorLog;

 

RETURN @intReturnValue;

ErrorHandler 레이블에서의 프로세스는...

1. 현재 트랜잭션이 열려있으나 commit할 수 없는 상태이면, 트랜잭션을 rollback 한다.
2. @intReturnValue 변수의 값이 NULL이거나 0이면, P_AddErrorLog를 호출하고 해당 SP의 리턴 값을 @intReturnValue 변수에 대입한다.
3. @intReturnValue를 리턴하고 종료한다.

기본 컨셉은 "에러가 발생하면 GOTO문을 사용해 ErrorHandler 레이블로 이동시켜주는 것"입니다.


Demo DB로 테스트해 보자~

지금부터 Demo DB를 사용하여 설명하겠습니다.

Demo DB의 ERD는 위와 같습니다.

Categories : 기사 카테고리
Articles : 기사
Movies : 영화
MoviesAndArticles : 기사 테이블과 영화 테이블의 교차 테이블

간단한 구조이니 쉽게 이해하실 수 있습니다.

이제 테스트 환경을 만들기 위해 기사 카테고리를 하나 추가합니다.

DECLARE @intReturnValue int;

DECLARE

      @intParentCategoryID int    -- 상위기사카테고리일련번호.

    , @nvcCategoryName nvarchar(100)    -- 기사카테고리이름.

    , @intCategoryID int;    -- 기사카테고리일련번호.

 

SET @intParentCategoryID = NULL;

SET @nvcCategoryName = N'인물';

 

EXEC @intReturnValue = dbo.P_AddCategory

      @intParentCategoryID = @intParentCategoryID

    , @nvcCategoryName = @nvcCategoryName

    , @intCategoryID = @intCategoryID OUTPUT;

 

SELECT @intCategoryID AS [@intCategoryID];

 

PRINT @intReturnValue;

이제 Categories 테이블의 내용은 아래와 같습니다.

"논리적 에러" & "미리 예상 가능한 시스템 에러" 처리하기

미션 1. Category를 삭제하는 SP를 만들자

Category를 1개 삭제할 때 처리해줘야하는 논리적 에러에는 무엇이 있을까요?

가장 기본적인 것은 "삭제할 행이 없음"에 대한 처리입니다.

DELETE dbo.Categories WHERE categorySN = @intCategorySN;

 

IF @@ROWCOUNT = 0

BEGIN

    SET @intReturnValue = 102;

    GOTO ErrorHandler;

END

RETURN 0;

ErrorHandler:

IF XACT_STATE() <> 0

    ROLLBACK TRANSACTION;

 

IF @intReturnValue IS NULL OR @intReturnValue = 0

    EXEC @intReturnValue = dbo.P_AddErrorLog;

 

RETURN @intReturnValue;

DELETE문을 실행한 직후, affected row 수를 확인하여, 삭제된 행이 없다면 SP가 102를 리턴하고 강제 종료하도록 합니다.

이런 경우... ErrorHandler 레이블에서는 이렇게 동작할 겁니다.

1. 현재 트랜잭션이 열려있지 않으므로 첫번째 IF 블럭 건너 뜀
2. @intReturnValue 변수의 값이 102이므로 두번째 IF 블럭 건너 뜀
3. 102를 리턴하고 종료

미션 2. Article을 추가하는 SP를 만들자

[Categories] 테이블과 [Articles] 테이블 사이에는 FK_Categories_Articles 라는 참조키가 설정되어 있습니다.
cardinality는 1:n 입니다.

풀어서 설명하면...
- 모든 기사는 하나의 기사 카테고리에 속한다.
- 하나의 기사 카테고리는 n개의 기사를 포함한다.

위의 룰을 위반하는 입력을 예상할 수 있는데...

즉, "기사"를 추가하려는데 기사가 속한 "기사 카테고리"가 사실은 존재하지 않는 경우입니다.

우리는 이런 상황이 발생했을 때 SP가 103을 리턴하고 강제 종료되길 원하는데, 코드로는 이렇게 작성하면 됩니다.

BEGIN TRY

    INSERT dbo.Articles (categoryID, [subject], writer, [content], registerDate)

    VALUES (@intCategoryID, @intSubject, @nvcWriter, @nvcContent, GETDATE());

 

    SET @intArticleID = SCOPE_IDENTITY();

END TRY

BEGIN CATCH

    IF ERROR_NUMBER() = 547 AND ERROR_MESSAGE() LIKE N'%FK_Categories_Articles%'

        SET @intReturnValue = 103;

 

    GOTO ErrorHandler;

END CATCH;

 

RETURN 0;

ErrorHandler:

IF XACT_STATE() <> 0

    ROLLBACK TRANSACTION;

 

IF @intReturnValue IS NULL OR @intReturnValue = 0

    EXEC @intReturnValue = dbo.P_AddErrorLog;

 

RETURN @intReturnValue;

TRY 블럭에서 INSERT 문을 실행합니다. 입력 조건이 정상이라면 0을 리턴하고 종료합니다.

하지만, 위에 설명한 예외가 발생하는 입력 조건이라면 어떨까요?

SQL Server 2005에서 참조키 제약 조건이 위배되는 경우 547번 에러가 발생합니다. 그리고 에러 메세지에는 위반한 제약 조건의 이름이 포함되는데... 이것을 위와 같이 CATCH블럭에서 이용할 수 있습니다.


혹시 이렇게 생각하고 계신가요?

"그런 검사는 이미 클라이언트에서 하고 난 다음이니까 굳이 다시 확인할 필요 없어!!!"


맞습니다.

이런 검사는 먼저 클라이언트나 어플리케이션 서버에서 미리 수행하고... 룰 위반이면 SP를 호출하지 않아야 합니다.

그.러.나... SQL개발자는 늘 동시성에 대한 고려를 해야합니다.

SP를 호출하기 직전까지 존재했던 카테고리도 SP가 실행되는 도중, 다른 세션에 의해 지워지고 없을 수 있으니까요.

즉, 클라이언트에서의 확인은 옵션... DB에서의 확인은 필수... 라는 뜻입니다. 이걸 거꾸로 생각하면 큰일납니다.


또는 "이렇게 작성하는게 어때?" 하고 생각할 수도 있겠군요.

BEGIN TRY

    IF NOT EXISTS (SELECT * FROM dbo.Categories WHERE categoryID = @intCategoryID)

    BEGIN

        SET @intReturnValue = 103;

        GOTO ErrorHandler;

    END

 

    INSERT dbo.Articles (categoryID, [subject], writer, [content], registerDate)

    VALUES (@intCategoryID, @intSubject, @nvcWriter, @nvcContent, GETDATE());

 

    SET @intArticleID = SCOPE_IDENTITY();

END TRY

이 방법 역시 동시성 문제를 가지고 있습니다.

IF NOT EXISTS ()문을 실행할 때와 INSERT문이 실행할 때의 시간 사이에 Category가 삭제되고 없을 수 있기 때문입니다.

그래도 굳이 이 방식을 고집하시겠다면 테이블 잠금 힌트를 사용할 수는 있지만... 그렇게해서 얻을 수 있는 이득은 없어보입니다.

오히려 테이블 잠금으로 인한 Blocking 이슈도 발생하게 되죠.


시스템 에러 기록 & Rethrow

시스템 에러를 DB에 기록하고 Rethrow하는 코드는 P_AddErrorLog에 포함되어 있습니다.

소스 코드의 주요 부분은 아래와 같습니다.

SET @intErrorNumber = ERROR_NUMBER();

SET @intErrorSeverity = ERROR_SEVERITY();

SET @intErrorState = ERROR_STATE();

SET @intErrorLine = ERROR_LINE();

SET @nvcErrorProcedure = ERROR_PROCEDURE();

SET @nvcErrorMessage = ERROR_MESSAGE();

 

INSERT dbo.ErrorLogs (loginName, hostName, errorNumber, errorSeverity, errorState, errorProcedure, errorLine, errorMessage, occurredDate)

VALUES (CAST(ORIGINAL_LOGIN() AS nvarchar(128)), CAST(HOST_NAME() AS nvarchar(128)), @intErrorNumber, @intErrorSeverity, @intErrorState, @nvcErrorProcedure, @intErrorLine, @nvcErrorMessage, GETDATE());

 

SET @nvcErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, Message: ' + @nvcErrorMessage;

RAISERROR (@nvcErrorMessage, @intErrorSeverity, 1, @intErrorNumber, @intErrorSeverity, @intErrorState, @nvcErrorProcedure, @intErrorLine);

 

RETURN 100;

예상하지 않은 시스템 에러가 발생하면, ErrorHandler 레이블에서 P_AddErrorLog를 호출합니다.

P_AddErrorLog는 위에서 보는 바와 같이 [ErrorLogs] 테이블에 에러 내용을 INSERT하고, RAISERROR 문을 사용하여 에러를 발생 시킨 후 100을 리턴합니다.


중첩된 SP에서 에러 번호 전달

SP에서 SP를 호출할 때 아래와 같이 작성하면 sub SP에서 발생한 에러 번호를 caller SP로 계속 전달 할 수 있습니다.

EXEC @intReturnValue = dbo.P_SubProcedure;

 

IF @intReturnValue <> 0

    GOTO ErrorHandler;

때로는 0만을 리턴하는 SP가 있을 수 있는데, 이런 SP를 sub SP로 호출하는 경우에도 반드시 위와 같이 작성하는 것이 좋습니다. sub SP가 0 이외의 값을 리턴하도록 언제 수정될지 모를 일이기 때문입니다.