System Engineering

[펌] MS-SQL 중첩 트랜잭션 처리 가이드

출처 : http://merritt.co.kr/tt/index.php?pl=33

중첩 트랜잭션 처리 가이드

복잡한 업무를 개발하다 보면 sp 를 분리해야 할 경우가 많습니다. 각각의 sp 가 asp 에서 직호출될 때도 트랜잭션이 보장되어야 하고 sp 를 중첩해서 호출할 때도 하나의 트랜잭션으로 처리되어야 합니다. 의외로 이런 상황이 자주 발생하는데, 단독호출/중첩호출 상황을 한 트랜잭션에 모두 커버할 수 있는 표준적인 sp 작성법은 부재한 실정입니다. 본 문서는 이런 상황을 비교적 깔끔하게 해결할 수 있는 sp 작성법을 알려드리고자 쓰여졌습니다. 간단한 시나리오를 가정해서 설명을 하도록 하겠습니다.

1. dbo.up_inner 를 먼저 개발

A라는 프로젝트에서 up_inner 라는 sp 를 만들었습니다. DB개발 가이드가 배포되기 이전의 일반적인 sp 작성법에 따르면 대체로 다음과 같은 방식으로 만들어집니다.

create proc dbo.up_inner
    @cust_no varchar(10)
as
    set nocount on
    set transaction isolation level read uncommitted

    begin tran

    select (생략...)
    insert (생략...)

    if @@error <> 0 
    begin
        rollback tran
        return
    end

    update (생략)

    if @@error <> 0 
    begin
        rollback tran
    return
end

commit tran

asp 에서 이 sp 를 호출해서 사용하는데 아무런 문제가 없습니다. 트랜잭션 처리도 잘 되어 있고 정상적으로 돌아갑니다.

2. dbo.up_outer 를 개발

dbo.up_inner 를 사용해서 프로젝트를 오픈했습니다. 그런데 운영업무 도중 어떤 요구사항 때문에 dbo.up_outer 를 만들고, 그 안에서 dbo.up_inner 를 call 해야 할 일이 생겼습니다. 당연히 dbo.up_outer 전체에서 한 트랜잭션에 모든 것이 처리되어야 합니다. 일단 다음과 같은 코드가 만들어 질 것을 예상할 수 있습니다.

create proc dbo.up_outer
    @cust_no varchar(10)
as
    set nocount on
    set transaction isolation level read uncommitted

    begin tran

    insert (생략)

    if @@error <> 0 
    begin
        rollback tran
        return
    end

    exec dbo.up_inner @cust_no

    delete (생략)

    if @@error <> 0 
    begin 
        rollback tran
        return
    end

commit tran

위의 코드를 하나하나 뜯어보겠습니다. 언뜻 보기에는 에러처리가 잘 되어 있는 듯 하지만 여러 문제점이 존재합니다.

    exec dbo.up_inner @cust_no

이 부분이 가장 심각한 문제가 있습니다. 호출하는 것 자체는 문제가 아닙니다. 하지만 dbo.up_inner 에서 어떤 에러로 인해 rollback 을 했다고 가정합시다. 그런데 문제는 dbo.up_inner 에서 rollback 을 하면 dbo.up_inner 내부에서 시작한 트랜잭션만 rollback 하는 것이 아니라 자신이 속해있는 transaction context 를 모두 rollback 해버립니다. 즉, dbo.up_outer 에서 시작한 트랜잭션을 rollback 해버리는 것이지요.

이제 어떻게 될까요?

네… dbo.up_outer 의 commit 에서 에러를 냅니다. 만약 delete 문이 실패했다면, rollback 을 하려고 해도 dbo.up_inner 에서 트랜잭션이 이미 rollback 되었기 때문에 에러를 냅니다. 이런 상황은 여러 번 겪어보셨을 줄로 압니다. 그래서 기존의 코드들을 보면 dbo.up_inner 에서 트랜잭션을 빼는 방향으로 수정해서 이런 상황을 해결하고 있습니다. 하지만 아시다시피 dbo.up_inner 에서 트랜잭션을 제거하면 dbo.up_inner 가 단독으로 호출되었을 때 데이터 정합성을 보장받을 수 없죠. 데이터 맞추느라 짚더미에서 바늘찾기를 해보신 분들이라면 데이터 정합성의 중요성을 뼈저리게 느끼고 있으실 겁니다.

이 상황을 깔끔하게 해결하는 방법이 없을까요?

네 있습니다. 모든 sp 를 DB개발 가이드의 에러처리 샘플을 정확하게 지켜서 개발하시면 됩니다. ㅎㅎㅎ 개발 가이드의 에러처리 샘플은 중첩호출 상황을 염두에 두고 만들어진 것입니다. 이제 dbo.up_inner 와 dbo.up_outer 를 에러처리 샘플에 맞춰서 수정해보겠습니다.

create proc dbo.up_inner
    @cust_no varchar(10)
as
    set nocount on
    set transaction isolation level read uncommitted

    begin tran

    select (생략...)
    insert (생략...)

    if @@error <> 0 
    begin
        goto HANDLE_ERROR -- 에러가 발생하면 공통 에러 처리 레이블로 점프
    end

    update (생략)

    if @@error <> 0 
    begin
        goto HANDLE_ERROR
    end

    commit tran

    if @@nestlevel = 1 -- 중첩레벨 1 은 asp 에서 직접호출
        select -1 as ret_code
        return -- 반드시있어야함! 이게없으면아래쪽의HANDLE_ERROR 까지실행하게됨
    else -- 중첩레벨1 아닌 것은 외부sp 에서 중첩호출
        return -1

    HANDLE_ERROR:
    if @@trancount > 0 rollback tran
    if @@nestlevel = 1
        select -1 as ret_code
    else
        return -1

볼드체로 된 부분에 주목해 주세요. 에러 처리 로직을 한군데로 몰았고, commit 후나 rollback 후에 @@nestlevel 을 체크해서 최종 결과 코드를 리턴하고 있습니다. @@nestlevel 은 중첩레벨을 나타내는 시스템 함수입니다. @@nestlvel 이 0 이면 asp 에서 호출한 것이므로 select 로 에러코드를 넘겨줍니다. @@nestlevel 이 0 보다 크면 외부 sp 에서 중첩호출한 것이므로 return 문으로 에러코드를 넘겨줍니다. 또는 OUTPUT 변수로 넘겨줄 수도 있겠습니다. (참고로 return 문은 numeric 데이터만 리턴가능합니다. 문자는 리턴할 수 없습니다)

create proc dbo.up_outer
    @cust_no varchar(10)
as
    set nocount on
    set transaction isolation level read uncommitted

    begin tran

    insert (생략)

    if @@error <> 0 
    begin
        goto HANDLE_ERROR
    end

    declare @ret_code int

    exec @ret_code = dbo.up_inner @cust_no -- dbo.up_inner에서 return하 고 결과코드를 받음

    if @@error <> 0 or @ret_code < 0 -- @@error 와@ret_code 값에따라에러처리
    begin
        goto HANDLE_ERROR
    end

    delete (생략)

    if @@error <> 0 
    begin 
        goto HANDLE_ERROR
    end

    commit tran

    if @@nestlevel = 1
        select -1 as ret_code
        return -- 반드시있어야함! 이게 없으면 아래쪽의 HANDLE_ERROR까지 실행하게 됨
    else
        return -1

    HANDLE_ERROR:
    if @@trancount > 0 rollback tran -- 항상 @@trancount를 체크해서 rollback
    if @@nestlevel = 1
        select -1 as ret_code
    else
        return -1

마찬가지로 볼드체 부분에 주목해주세요. dbo.up_inner 를 호출해서 @ret_code 를 받을 때는 위와 같은 방식으로 코드를 작성합니다. 그리고 exec 를 하자마자 바로 @@error 와 @ret_code 를 체크해서 에러처리를 수행합니다. Exec 후에 set @aaa = @bbb 와 같은 코드가 들어가면 그 순간 @@error = 0 이 되어 에러처리 로직이 동작하지 않게 되므로 주의해주세요.

이제 rollback 하는 부분을 특히 주목해서 봐주세요. HANDLE_ERROR 로 넘어올 때는 이미 dbo.up_inner 에서 전체 트랜잭션을 rollback 한 후가 될 수도 있습니다. 따라서 rollback 하기 전에는 언제나 @@trancount 를 체크해서 rollback 하도록 하면 안전한 코드가 됩니다. 그리고 외부에 결과코드를 넘겨주는 것도 @@nestlevel 을 체크해서 수행합니다.

그런데 여기서 한가지 의문이 생길 수 있습니다. 트랜잭션을 중첩시키고 있는데, 중첩 트랜잭션은 단일 트랜잭션보다 시스템에 더욱 부하를 주는 것이 아닌가 하는 것입니다. 결론부터 말씀드리면 트랜잭션을 중첩시킨다고 해서 특별히 부하가 더 가중되는 것은 아닙니다. Begin tran 후에 또 다시 begin tran 은 하게 되면 단지 내부적으로 @@trancount 가 1 증가할 뿐입니다. 그리고 rollback 을 하게 되면 전체 트랜잭션을 rollback 하고 @@trancount 를 0 으로 만듭니다. 중첩 트랜잭션하에서 commit 은 @@trancount 가 1 이 될 때까지 @@trancount 를 하나 감소시키기만 하다가 @@trancount 가 1 인 상태에서 commit 를 만나면 그때서야 비로소 DB에 실제 commit 를 하게 됩니다. 따라서 위와 같은 방식으로 코드를 작성해도 특별히 DB에 부하가 더 가는 것은 아닙니다. 시스템에 부담을 주는 것은 트랜잭션의 중첩 수준이 아니라 트랜잭션의 길이입니다. 데이터 정합성을 고려해 트랜잭션을 최대한 지키면서 가능한 한 트랜잭션의 길이를 짧게 가져가는 방향으로 고민을 해주시길 부탁드립니다.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.