일러두기. 본문서는 기배포된 중첩 트랜잭션 처리 가이드와 함께 읽으면 좋습니다.
1. @@error 와 @@rowcount 를 이용한 에러 처리
@@error : SQL문 실행도중 어떤 이유로 에러가 발생했을 때 0 이 아닌 값으로 세팅되는 시스템 함수. 에러가 발생했다가 단 한줄이라도 다음 문장이 성공하면 0 으로 세팅됨.
@@rowcount : select, insert, delete, update, set 문이 성공한 후에 몇 행을 처리했는지 세팅되는 함수.
일반적으로 SP 안에서 에러처리는 다음과 같은 패턴으로 이루어집니다.
if @@error <> 0 or @@rowcount <> 1
begin
select -1 as result_code
return
end
자주 저지르는 실수!
UPDATE DEPT SET DEPT_NM = '테스트부서' WHERE DEPT = 'AX' -- 'AX'라는 부서는 존재하지 않는다고 가정. 이럴 경우 처리된 행의 개수는 0개임
set @processed_rows = @@rowcount
set @result_code = @@error
if @result_code <> 0 or @processed_rows <> 1
begin
select @result_code
return
end
위의 코드는 에러가 발생하면 @@error 와 @@rowcount 를 별도의 변수에 저장하고 에러를 처리하는 코드입니다. 그런데 위의 에러처리 루틴은 결코 실행되지 않습니다. @result_code 를 세팅하기 전에 set @processed_rows = @@rowcount 가 성공하면서 @@error 가 0으로 되고 @result_code 에는 언제나 0 이 세팅되기 때문입니다.
그렇다면 코드를 이렇게 바꾸면 어떨까요?
UPDATE DEPT SET DEPT_NM = '테스트부서' WHERE DEPT = 'AX'
set @result_code = @@error
set @processed_rows = @@rowcount
if @result_code <> 0 or @processed_rows <> 1
begin
select @result_code
return
end
update 문을 실행하자마자 @@error 를 @result_code 에 넣었으므로 정상적인 값이 들어갈 것입니다. 그러면 @@rowcount 는 어떨까요? DEPT 테이블에 AX라는 부서가 없으므로 @@rowcount 는 0 이 되어야 합니다. 하지만 실행시켜보면 @processed_rows 에 1 이 들어가 있습니다. 이게 어찌된 일일까요???
원인은 set @result_code = @@error 때문입니다. @@rowcount 는 set 문에 의해서도 영향을 받습니다. 따라서 set @result_code = @@error 가 성공하면 한 개의 데이터가 변수에 저장되었으므로 @@rowcount 는 1이 되는 것입니다.
(참고 : http://msdn2.microsoft.com/ko-kr/library/ms187316.aspx)
정확한 코드는 다음과 같습니다.
UPDATE DEPT SET DEPT_NM = '테스트부서' WHERE DEPT = 'AX'
select @result_code = @@error , @processed_rows = @@rowcount
if @result_code <> 0 or @processed_rows <> 1
begin
select @result_code
return
end
select 문도 변수에 값을 저장할 수 있습니다. select 문을 이용해서 한꺼번에 값을 저장하면 정확하게 원하는 값이 저장됩니다. SQL Server 2000 은 C++ 이나 Java, C# 에서 제공되는 try ~ catch 구문을 제공하지 않는다. (2005에서는 제공합니다) 따라서 에러 발생의 소지가 있는 각 insert, delete, update 문 마다 위와 같은 에러 처리 루틴을 넣어야 합니다.
(참고 : set 문으로 변수를 저장하는 것 보다 select 로 저장하는 것이 더 느리다는 개발자의 경험사례 보고가 있었습니다. 반복 테스트가 필요하겠습니다. 테스트 후 결과를 올리도록 하겠습니다.)
2. rollback, commit 할 때는 언제나 @@trancount 를 체크하자
@@trancount : 트랜잭션의 중첩 수준을 나타내는 시스템 변수. Begin tran 문에 의해 1씩 증가하고 commit tran 에 의해 1씩 감소한다. rollback tran 을 만나면 모두 롤백하면서 어떤 값을 가지고 있더라도 0 으로 변한다.
sp를 작성할 때 데이터의 정합성을 보장하기 위해서 트랜잭션을 사용하게 됩니다. 트랜잭션을 시작하면 언제나 begin tran, commit tran의 짝이 일치해야 하는 것은 당연하지요. 즉, 트랜잭션이 중첩되어서 begin tran 을 세번하게 되면 세번 commit 이 있어야 합니다. 단 rollback 은 한번에 @@trancount 를 0 으로 만들고 rollback 하기 때문에 rollback 하기 전에 언제나 @@trancount 를 체크하면서 rollback 해야 합니다.
INSERT INTO DEPT ( … )
if @@error <> 0 or @@rowcount <> 1
begin
if @@trancount > 0 rollback
select -1 as err_code
return
end
INSERT INTO TEST_TBL ( … )
if @@error <> 0 or @@rowcount <> 1
begin
if @@trancount > 0 rollback
select -2 as err_code
return
end
if @@trancount > 0 commit
rollback 이나 commit 하기 전에 @@trancount 를 체크하는 것은 sp들간에 복잡하게 호출하고 각 sp들이 자체적으로 transaction 을 사용할 때 위력을 발휘합니다. 중첩 트랜잭션 처리에 관한 사항은 기배포한 중첩 트랜잭션 처리 가이드를 참고해주세요.
Posted by maceo
