[SQL서버] 커밋할 수 없는 트랜잭션

이따금 SQL서버에서
"메시지 3998, 커밋할 수 없는 트랜잭션이 일괄 처리 맨 끝에서 검색되었습니다. 트랜잭션이 롤백됩니다."
라는 오류가 발생하는 경우가 있습니다.

기술적 내용을 알고나면 지극히 당연한 오류 메시지인데, 그 전에는 도대체 무슨 뜻인지 이해하기가 쉽지 않습니다.

SQL서버에서 명시적으로 트랜잭션을 시작하는 경우, 런타임 중에 오류가 발생하면 해당 트랜잭션에 대하여 직접 완료(커밋 또는 롤백)처리를 해야 하는 모드와 SQL서버가 자동으로 롤백 처리해 주는 모드가 있습니다.

다음 명령을 통해서 모드 변경이 가능 합니다.

SET XACT_ABORT { ON | OFF }

T-SQL 디폴트는 OFF 상태 이며, 이 때는 직접 완료처리를 해야 합니다.

USE AdventureWorks2014
GO

SET XACT_ABORT OFF;
GO

BEGIN TRANSACTION; --- ①

SELECT 10 /0       --- ②
SELECT TOP 1 * FROM Sales.Store    --- ③

SELECT @@TRANCOUNT as trnCount

① 에서 명시적 트랜잭션이 시작 되었습니다.
② 에서 0으로 나누기 오류가 발생 됩니다. 그러나 다음 행의 쿼리가 계속 수행 됩니다.
③ SELECT 문이 정상적으로 수행 됩니다.

결과는 다음과 같습니다.

이 시점에서 @@TRANCOUNT는 보시는 바와 같이 1 입니다.
따라서 개발자가 롤백을 하든지 커밋을 하든지 직접 해야 합니다.
물론 위에서는 UPDATE, DELETE 가 없으므로 커밋을 하든 롤백을 하든 결과가 달라질 것은 없습니다.
XACT_ABORT가 OFF 상태일 때는 런타임 오류가 발생하여도 오류가 발생된 이후의 쿼리가 계속 수행되고
트랜잭션 상태가 유지 됩니다.

이제 XACT_ABORT가 ON인 경우를 보겠습니다.

USE AdventureWorks2014
GO

SET XACT_ABORT ON;   --- ①
GO

BEGIN TRANSACTION;

SELECT 10 /0   --- ②
SELECT TOP 1 * FROM Sales.Store

SELECT @@TRANCOUNT as trnCount

① 에서 XACT_ABORT가 ON으로 변경 되었고, 나머지 쿼리는 전과 동일 합니다.

결과는 다음과 같습니다.

② 에서 0 나누기 오류가 발생하자 그 이후의 쿼리는 수행되지 않고 트랜잭션이 종료되고 자동으로 롤백이 됩니다.
따라서 이때 개발자가 직접 롤백처리 하고자 하면 오류가 발생 합니다.

그럼 "커밋할 수 없는 트랜잭션이 일괄 처리 맨 끝에서 검색되었습니다. 트랜잭션이 롤백됩니다." 오류는 언제 발생하는 걸까요?

다음 쿼리의 실행 결과를 보겠습니다.

USE AdventureWorks2014
GO

SET XACT_ABORT ON;
GO

BEGIN TRANSACTION;
BEGIN TRY   --- ①
    SELECT * FROM person.Person WHERE BusinessEntityID = 1

    INSERT INTO person.personPhone (BusinessEntityID, PhoneNumber, PhoneNumberTypeID)
    VALUES (1, '697-555-0142', 1)    --- ②

    COMMIT
END TRY
BEGIN CATCH  --- ③
    SELECT XACT_STATE() AS XACTSTATE,  ERROR_NUMBER() AS ERR_NUMBER,   --- ④
           ERROR_MESSAGE() AS ERR_MESSAGE 
END CATCH
GO

SELECT @@TRANCOUNT as trnCount

명시적 트랜잭션 실행 후,
① 에서 TRY/CATCH를 처리하고 있습니다.
② 에서 PRIMARY KEY 제약 조건 위반(중복 키 삽입) 오류가 발생 됩니다.
③ TRY 내에서 발생된 오류(예외)로 인해서 CATCH로 제어가 넘어 오게 됩니다.

드디어 "커밋할 수 없는 트랜잭션이 일괄 처리 맨 끝에서 검색되었습니다. 트랜잭션이 롤백됩니다." 오류가 발생 하였습니다. 

XACT_ABORT가 ON 상태에서 명시적으로 트랜잭션이 시작되고, TRY 내에서 예외 발생이 되었으나 CATCH에서 트랜잭션에 대하여 롤백 처리를 하지 않고 일괄처리가 끝나면 위와 같은 오류가 발생하게 됩니다.

이제는 오류 메시지가 무슨 의미인지 이해가 될 것입니다.

참고로 ④에서 XACT_STATE()를 사용하고 있는데 반환값이 -1 이면 활성 사용자 트랜잭션이 있지만 오류가 발생하여 트랜잭션이 커밋할 수 없는 상태라는 의미 입니다.
만약 이 상태에서 CATCH 내에서 ROLLBACK이 아닌 COMMIT을 시도하게 되면 다음과 같은 오류가 발생 합니다.

현재 트랜잭션은 커밋할 수 없으며 로그 파일에 쓰는 작업을 지원하지 않습니다. 트랜잭션을 롤백하십시오

이 글이 도움이 되셨으면 추천과 댓글을 부탁 드려요!

참고
SET XACT_ABORT(Transact-SQL)

XACT_STATE(Transact-SQL)

TRY...CATCH(Transact-SQL)