[SQL Server] 쿼리문 실행 예외 발생시 자동 롤백하는 T-SQL 예외처리 트랜잭션 코드 작성 방법

T-SQL 초보에게는 다소 어려운 것이 트랜잭션 처리입니다.

많은 레코드를 한꺼번에 배치 처리하다가 중간에 에러라도 발생하면 트랜잭션 처리를 하지 않은 것이 뼈아프게 다가옵니다. T-SQL에도 예외 오류를 처리하는 처리 구문이 있고, 이 구문을 이용하면 프로그램에서 쿼리문 오류로 인해 발생하는 시스템 예외처리를 별도로 하지 않아도 되는 장점이 있습니다.

쿼리문 예외 처리 방법

T-SQL의 트랜잭션 처리는 간단합니다.

BEGIN TRAN; -- 트랜잭션 시작
-- 배치 실행 쿼리문들
COMMIT TRAN; -- 성공시
ROLLBACK TRAN; -- 실패시

이때 배치 실행 쿼리문들 실행 중간에 에러가 나면 어떻게 될까?

성공한 쿼리문까지는 커밋이 되고, 에러를 발생시킵니다.

에러가 발생하기 전까지 성공한 쿼리문까지 커밋이 되는 게 중요합니다. 원래 데이터로 롤백을 할 수 없다는 뜻입니다.

삽입 배치 처리문이면 삽입된 행들만 삭제 처리하고 다시 실행하면 되지만, 레코드 업데이트나 조건부 레코드 삭제 배치 처리 쿼리문들이라면 돌이킬 수 없는 지옥으로 가게 되기도 합니다.

원 데이터로 다시 복구를 할 수 있도록 에러가 발생하면 예외 핸들링을 할 수 있도록 T-SQL에도 예외처리 구문이 지원됩니다.

BEGIN TRY 
	begin tran;
	-- 배치 실행 쿼리문들
	commit tran;
END TRY
BEGIN CATCH -- 예외 발생시 처리
	rollback tran; -- 롤백
END CATCH;

사용 방법과 구문은 단순합니다.

프로그래밍 언어 대부분이 사용하는 try ~ catch 예외 처리 방법과 구조 그대로이고, 배치 처리되는 쿼리문이 중간에 에러가 발생하면 롤백(rollback tran)을 하고 쿼리문을 정상 종료합니다.

프로그램 단에서 쿼리문 예외처리를 대응하는 방법

쿼리문 결과를 받아서 처리를 하는 프로그램 코드를 작성할 때 주의할 점이 있습니다.

쿼리문이 정상 종료되기 때문에 쿼리문 결과를 받아서 처리하는 프로그램 코드에서는 실행된 쿼리문의 개수가 반환됩니다. 

실행된 쿼리문의 개수가 반환되는 조건은 없으며, 쿼리문 배치 실행 중에 예외 에러가 발생되어 롤백된 경우 실행된 쿼리문 개수는 0개가 아니라 정상 실행되었던 쿼리 문의 개수입니다.

다음과 같은 C# 가상 실행 코드에서 C# 코드의 catch() 예외 처리 코드는 절대 실행되지 않습니다.

쿼리문에서 이미 예외처리가 되어 정상 롤백되고 쿼리문 전체가 정상 종료되었기 때문입니다.

따라서 다음의 try{} ~ catch(){} C# 코드와 같은 예외처리는 불필요합니다.

쿼리문 실행중 롤백 처리된 것도 정상 실행 완료되는 쿼리 처리라는 것에 주의해야 합니다.

saveSQL = "BEGIN TRY begin tran;" + saveSQL + "commit tran; END TRY BEGIN CATCH rollback tran; END CATCH";
try{
	ret = db.Execute(saveSQL);
}catch(System.Exception ex){
	Response.Write(ex.Message);
}

이때 쿼리가 실행된 후 반환되는 값을 받는 ret 값이 중요합니다.

쿼리문이 1000개의 배치 레코드 삽입 쿼리문이었는데 501번째 레코드 삽입을 실행하다 에러가 발생한 경우 쿼리문 실행은 정상 종료되고, 반환되는 값은 500이 됩니다.

즉, 500 레코드까지는 정상 저장하고, 501번째 레코드를 저장 쿼리문을 실행하다 에러가 발생해서 예외 처리가 되었고, 전체 삽입이 롤백되어 저장되었던 500 레코드 삽입이 취소되었다는 것을 ret 값을 통해 유추할 수 있게 됩니다.

쿼리문 실행 후에는 ret 값이 저장하려고 했던 실제 배치 레코드 개수와 일치하는지 확인해서 처리하는 과정을 반드시 거쳐야 배치 레코드 삽입을 프로그램 코드에서 제대로 완료할 수 있습니다.

T-SQL 예외처리 구문(BEGIN TRY, END TRY, BEGIN CATCH, END CATCH)은 SQL Server 내장 커맨드이기 때문에 SQL 문처럼 실행문의 표시를 위한 세미콜론(;)이 필요 없습니다.

하나의 문자열로 붙여서 처리할 때는 공백으로 띄워서 구분만 해주면 SQL Server가 알아서 인식합니다.

롤백과 자동 증가 칼럼

SQL문으로 삽입을 할 때 키값과 유니크 값을 편리하게 관리하는 방법 중의 하나로 자동 증가 칼럼을 많이 사용합니다.

자동 증가 칼럼에는 중요한 특성이 하나 있는데 트랜잭션의 적용을 받지 않는 것입니다.

DB 데이터의 무결성을 위한 것으로 관계형 데이터베이스의 구조적인 특징이라고 이해를 하면 됩니다.

따라서 트랜잭션을 롤백해도 자동 증가 칼럼의 값이 트랜잭션 이전으로 복구되지 않습니다.

이것을 "넌트랜지셔널"이라고 따로 용어를 사용하기도 하는데, 어쨌든 자동 증가 칼럼은 트랜잭션의 영향을 받지 않고 레코드 삽입이 일어날 때마다 증가합니다.

1000개의 레코드를 배치 삽입하는 쿼리문들을 실행하다 501번째 레코드에서 에러가 나서 롤백을 하면 자동 증가 칼럼의 값은 500이 늘어난 상태로 있게 됩니다.

다음번 레코드 삽입이 발생하면 501이 더해진 값이 자동 증가 칼럼의 값이 됩니다.

트랜잭션으로 롤백이 있을 수 있는 쿼리문을 실행할 때, 자동 증가 컬럼의 값을 미리 예측해서 사용하면 안 됩니다.