[SQL] TCL (COMMIT, ROLLBACK, SAVEPOINT)

트랜잭션

트랜잭션은 분리될 수 없는 한 개 이상의 테이터베이스 조작을 의미한다. 

하나의 트랜잭션에는 하나 이상의 SQL문장이 포함된다.

트랜잭션은 분할할 수 없는 최소의 단위이다. 

그렇기 때문에 전부 적용하거나 전부 취소한다. 

트랜잭션은 "All or Notiong" 이다. 

 

예를들어 은행 계좌이체 상황이라고 하자. 

STEP1. 1번 계좌의 잔액에서 1만원을 뺀다.

STEP2. 2번 계좌의 잔액에 1만원을 더한다. 

 

계좌이체라는 작업 단위는 이런 두 개의 업데이트가 모두 성공적으로 완료되었을 때 종료된다.

둘 중 하나라도 실패할 경우 계좌이체는 원래 금액을 유지해야 한다. 

장애로 인해 어느 한쪽만 실행되면 금액이 증가하거나 사라질 수 있겠지?

이런 일이 있어서는 안되므로 두 작업을 하나의 트랜잭션으로 묶는 것이다. 

 

이렇게 데이터를 반영시키는 것을 커밋(COMMIT)

트랜잭션 시작 이전 상태로 되돌리는 것을 롤백(ROLLBACK)

저장점(SAVE POINT) 

라고 한다. 

이 3가지 명령어를 트랜잭션을 컨트롤하는 TCL이라고 한다. 

 

트랜잭션의 대상이 되는 SQL문은 INSERT, UPDATE, DELETE 이다. 

 

 

트랜잭션의 특성

원자성: 모두 성공하거나 모두 실패해야 한다. (all or nothing) 이를 지원하기 위해 잠금(LOCKING) 기능을 제공한다. 

일관성: 실행 전에 문제가 없다면 실행 후에도 문제가 없어야 한다. 

고립성: 트랜잭션 실행 중에는 외부 영향을 받지 않는다. 

지속성: 트랜잭션이 성공하면 반영된 내용은 영구적으로 저장된다. 

 

 

COMMIT

데이터에 문제가 없다고 판단하는 경우 COMMIT 명령어를 통해 트랜잭션을 완료할 수 있다. 

변경된 데이터를 데이블에 영구적으로 반영하는 것. 

COMMIT ;

COMMIT 이후의 데이터 상태는 다음과 같다. 

- 데이터에 대한 변경 사항이 데이터베이스에 반영된다. 

- 이전 데이터는 영원히 잃어버리게 된다. 

- 모든 사용자는 결과를 볼 수 있다. 

- 관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있게 된다. 

 

오라클과 sql server의 COMMIT

오라클은 사용자가 COMMIT 해주어야 하지만, sql server는 AUTO COMMIT 이라서 DML 수행후 자동으로 커밋된다. 

 

sql server의 트랜잭션 3가지 방식 

1) AUTO COMMIT : 자동으로 COMMIT을 수행하고 오류가 발생하면 자동으로 ROLLBACK 한다. 

2) 암시적 트랜잭션 : 시작은 자동으로 처리하고, 끝은 사용자가 명시적으로 COMMIT한다. 

3) 명시적 트랜잭션 : 트랜잭션 시작과 끝을 사용자가 명시적으로 지정하는 방식. 

 

 

ROLLBACK

변경사항을 취소하는 기능. 이전 상태로 복구한다. 

ROLLBACK ; 

 

ROLLBACK 후의 데이터 상태는 다음과 같다. 

- 데이터에 대한 변경 사항은 취소된다.

- 이전 데이터는 다시 재저장된다.

- 관련 행에 대한 LOCKING이 풀리고, 다른 사용자들이 행을 조작할 수 있게 된다. 

 

COMMIT과 ROLLBACK을 사용함으로써 다음과 같은 효과를 볼 수 있다. 

- 데이터 무결성 보장

- 영구적인 변경을 하기 전에 데이터의 변경 사항 확인 가능

- 논리적으로 연관된 작업을 그룹핑하여 처리 가능

 

 

SAVEPOINT

저장점을 정의하면 롤백할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라, 

현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백할 수 있다. 

 

SAVEPOINT SVPT1 ; 

-- 저장점까지 롤백할 때는 ROLLBACK 뒤에 저장점 명을 지정한다. 
ROLLBACK TO SVPT1; 

-- sql server

SAVE TRANSACTION SVPT1 ;

ROLLBACK TRANSACTION SVPT1 ;

 

저장점 A로 되돌리고 나서 다시 B와 같은 미래 방향으로 되돌리는 것은 불가.

저장점까지 롤백하면 저장점 이후는 무효된다. 

 

 

참고!

- DDL (CREATE, ALTER, DROP, RENAME, TRUNCATE TABLE) 는 자동 커밋된다. 

 

 

 

 

* kdata 한국데이터진흥원에서 출간한 SQL 전문가 가이드 2013 Edition을 요약했습니다.