1. 트랜잭션?
MSSQL은 AUTO_COMMIT이 기본 설정. (=자동 커밋)
실무에서 update나, delete를 잘못 쓸 경우 (조건을 안걸고 통채로 적용되어버린다면..? 으악) 큰일나기 때문에,
TRANSACTION이라는 게 존재한다.
TRANSACTION이란, 하나의 수행 단위를 여러개로 쪼개서 작업한다고 볼 수 있다.
이론적으로는 ACID를 보장한다. (Atomicity:원자성, Consistency:일관성, Isolation:독립성, Durability:영구성)
예를 들어 관리자 DB인 DB1에서 품목 삭제를 누르면,
매장용 DB인 DB2에서도 품목이 삭제되는 로직이 있다고 하자.
이때 삭제 버튼을 누르고 DB1에서 삭제가 되었는데, 정전으로 인해 DB2에서는 품목 삭제가 이뤄지지 않아 매장에서 삭제되어야 할 품목이 판매된다면..? NOooo.....
이때, 트랜잭션 안에 DB1 삭제 후 DB2에도 삭제하는 내용을 넣어준다면 둘다 실행됐을때만 COMMIT이 되어 반영된다.
2. 트랜잭션의 기본구조
BEGAN TRAN (또는 TRANSACTION)
...
...
COMMIT TRAN
BEGAN TRAN
...
...
ROLLBACK TRAN기본 구조는 이런식이다. TRAN나 TRANSACTION 둘다 사용이 가능하고 COMMIT이나 ROLLBACK중 하나를 쓰면 된다.
COMMIT => 트랜잭션의 처리가 정상완료되면 DB에 적용
ROLLBACK => 트랜잭션 처리를 취소. 원복.
* 참고하면 좋은 문서
BEGIN TRANSACTION(Transact-SQL) - SQL Server
BEGIN TRANSACTION(Transact-SQL)
learn.microsoft.com
3. 트랜지션 롤백은 언제..?
이때 궁금한 점이, TRANS안에 SELECT를 넣어 확인하는 예제들에 대해 궁금증이 생겼다.
결국은 select는 조회만 할 뿐이고.. 만약 내부 내용이 잘못된걸 확인해도 아래에 commit TRANS를 써버리면 이미 실행되는게 아닌가..? 라는 의문점.
오늘 내가 본 프로시저에서는 이런 구조로 트랜잭션이 존재했다.
BEGIN TRY
BEGAN TRANSACTION
SELECT ...
UPDATE ...
...
COMMINT TRANSACTION
END TRY
BEGAN CATCH
IF(XACT_STATE()!=0)
....
ROLLBACK TRANSACTION
END CATCHTRY CATCH문으로 트랜지션의 상태에 따라 롤백을 해주는 것이었다..!
여기서 XACT_STATE는 트랜잭션의 상태를 점검한다.
XACT_STATE()
1 : 활성상태의 커밋 가능한 트랜잭션 존재
-1 : 활성상태의 커밋 불가능한 트랜잭션 존재
0 : 비 활성화 된 트랜잭션
즉 위의 코드는 활성상태의 트랜잭션 (=끝나지 않은 트랜잭션)에 대해 ROLLBACK처리를 진행하고 있다.
제대로 실행되어 끝나지 않은 트랜잭션은 CATCH에서 롤백해버리는 것이다.
또 다른 방법으로는 , TRAN문 안에 IF NOT EXISTS (조회문) 을 써서 , 변화된 값을 확인 후 이 값이 조회하지 않으면 ROLLBACK하는 방법이 있겠다.
예시 )
-- 트랜잭션을 시작
BEGIN TRAN
DECLARE @id INT
SET @id = 10
INSERT Scores VALUES (@id, 10, 80, GETDATE())
IF NOT EXISTS (SELECT * FROM STAT WHERE Id=@id)
BEGIN
-- 에러면 트랜잭션 취소
ROLLBACK TRAN
PRINT 'ERROR : No STAT data'
RETURN
END
UPDATE Stat SET Total=Total+80 WHERE Id=@id
-- 트랜잭션 완료
COMMIT TRAN
4. SAVE POINT
트랜잭션 내에서 부분적인 롤백을 필요로 할 때 세이브포인트를 사용하면 좋다.
트랜잭션 내에서 실행하는 로직이 1 -> 1-1, 1-2 -> 2 -> 3 .. 이런 식일 경우, 1-1과 1-2가 오류가 났을때는 일부 롤백해버리고 1,2,3 로직만 실행하도록 하는 방법이다.
TRAN문 안에 'SAVE TRAN 세이브포인트명' 을 작성하고, ROLLBACK TRAN 세이브포인트명 을 작성하면 원래 세이브포인트를 선언한 곳으로 이동한다. 마치 포인터 같은 느낌??
예시)
-- 트랜잭션을 시작
BEGIN TRAN
DECLARE @id INT
SET @id = 10
-- Scores 테이블에 입력
INSERT Scores VALUES (@id, 10, 80, GETDATE())
-- Save Point 설정
SAVE TRAN ScoreSavePoint
-- UPDATE 실행
UPDATE Stat SET Total=Total+80 WHERE Id=@id
-- 만약 UPDATE 실패하면
-- 지정된 Save Point로 롤백
IF @@ERROR<>0
BEGIN
PRINT 'ERROR in UPDATE Stat Table'
ROLLBACK TRAN ScoreSavePoint
END
-- 로그 테이블 추가
INSERT Log Values(GETDATE())
-- 트랜잭션 완료
COMMIT TRAN위 코드에서는 세이브포인트 아래의 Update문이 실패할 경우, update문만 빼고 Score테이블과 Log 에만 저장된다.