[SQL] TCL
개요
트랜잭션은 DB의 논리적 연산단위다. 트랜잭션이란 밀접히 관련돼 분리될 수 없는 한 개 이상의 DB조작을 가리킨다. 하나의 트랜잭션에는 하나 이상의 SQL 문장이 포함된다. 트랜잭션은 분할할 수 없는 최소의 단위다. 그러므로 전부 적용하거나 전부 취소한다. 즉, TRANSACTION은 ALL OR NOTHING의 개념이다.
특성
- 원자성
트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아 있어야 한다.(ALL OR NOTHING)
- 일관성
트랜잭션이 실행되기 전의 DB내용이 잘못돼 있지않다면 트랜잭션이 실행된 이후에도 DB의 내용에 잘못이 있으면 안된다.
- 고립성
트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.
- 지속성
트랜잭션이 성공적으로 수행되면, 그 트랜잭션이 갱신한 DB의 내용은 영구적으로 저장된다.
알아두기
계좌이체는 한 계좌에서 현금이 인출된 후에 다른 계좌로 입금되는데, 현금이 인출되기 전에 다른 계좌에 입금되는 것은 문제를 발생시킬 수 있다. 그리고 이체가 결정되기 전까지는 다른 사람이 이 계좌의 정보를 변경할 수 없다. 이것을 보통 문에 자물쇠를 채우듯이 한다고 해 잠금(LOCKING)이라고 표현한다.
트랜잭션의 특성(특히 원자성)을 충족하기 위해 DB는 다양한 레벨의 잠금 기능을 제공한다. 잠금은 기본적으로 트랜잭션이 수행하는 동안 특정 데이터에 대해서 다른 트랜잭션이 동시에 접근하지 못하도록 제한하는 기법이다. 잠금이 걸린 데이터는 잠금을 실행한 트랜잭션만 독점적으로 접근할 수 있고, 다른 트랜잭션으로부터 간섭이나 방해를 받지 않는 것이 보장된다. 잠금이 걸린 데이터는 잠금을 수행한 트랜잭션만 해제할 수 있다.
종류
TCL은 올바르게 반영된 데이터를 DB에 반영시키는 COMMIT, 데이터를 트랜잭션 시작 이전의 상태로 되돌리는 ROLLBACK, 트랜잭션의 일부만 취소할 수 있게 만드는 저장점 SAVEPOINT 3가지로 분류한다.
COMMIT
입력, 수정, 삭제한 데이터에 대해 전혀 문제가 없다고 판단됬을 경우 COMMIT 명령어로 트랜잭션을 완료할 수 있다. COMMIT이나 ROLLBACK 이전의 데이터 상태와 이후의 상태는 다음과 같다.
COMMIT 이전의 데이터 상태
- 데이터의 변경을 취소해 이전 상태로 복구 가능하다.
- 현재 사용자는 SELECT 문장으로 결과를 확인 가능하다.
- 다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없다.
- 변경된 행은 잠금(LOCKING)이 설정되어 다른 사용자가 변경할 수 없다.
COMMIT 이후의 데이터 상태
- 데이터에 대한 변경 사항이 DB에 반영된다.
- 이전 데이터는 영원히 잃어버리게 된다.
- 모든 사용자는 결과를 볼 수 있다.
- 관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있게 된다.
예제
예제에 사용되는 릴레이션은 아래와 같다.
- 쿼리
1
2
3
4
5
6
7
8
9
INSERT INTO EMP (
EMPNO
, ENAME
) VALUES (
'8899'
, 'SON'
);
COMMIT;
- 결과
ROLLBACK
테이블 내 입력한 데이터나 수정한 데이터, 삭제한 데이터에 대해 COMMIT 이전에는 변경 사항을 취소할 수 있다. DB에서는 롤백 기능을 사용한다. 롤백은 데이터 변경 사항이 취소돼 데이터가 이전 상태로 복구되며, 관련된 행에 대한 잠금(LOCKING)이 풀리고 다른 사용자들이 데이터 변경을 할 수 있게 된다.
ROLLBACK 이전의 데이터 상태
- 데이터의 변경을 취소해 이전 상태로 복구 가능하다.
- 현재 사용자는 SELECT 문장으로 결과를 확인 가능하다.
- 다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없다.
- 변경된 행은 잠금(LOCKING)이 설정되어 다른 사용자가 변경할 수 없다.
ROLLBACK 이후의 데이터 상태
- 데이터에 대한 변경 사항이 DB에 반영된다.
- 이전 데이터는 영원히 잃어버리게 된다.
- 모든 사용자는 결과를 볼 수 있다.
- 관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있게 된다.
예제
예제에 사용되는 릴레이션은 아래와 같다.
- 쿼리
1
2
3
4
DELETE FROM EMP
WHERE EMPNO = '8899';
ROLLBACK;
- 결과
SAVEPOINT
저장점(SAVEPOINT)을 정의하면 롤백할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라, 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백할 수 있다. 따라서 복잡한 대규모 트랜잭션에서 에러가 발생했을 때 SAVEPOINT까지의 트랜잭션만 롤백하고 실패한 부분에 대해서만 다시 실행할 수 있다.(일부 툴에서는 지원을 안 할 수 있음)
저장점은 복수로 정의할 수 있으며, 동일이름으로 여러 개의 저장점을 정의했을 경우 마지막에 정의한 저장점만 유효하다.
예제
예제에 사용되는 릴레이션은 아래와 같다.
- 쿼리
1
2
3
4
5
6
SAVEPOINT SVPT1;
DELETE FROM EMP
WHERE EMPNO = '8899';
ROLLBACK TO SVPT1;
- 결과
정리
해당 테이블에 데이터의 변경을 발생시키는 입력, 수정, 삭제 명령어 수행 시 변경되는 데이터의 무결성을 보장하는 것이 커밋과 롤백의 목적이다.
커밋은 ‘변경된 데이터를 테이블이 영구적으로 반영해라’ 라는 의미를 갖는 것이고, 롤백은 ‘변경된 데이터가 문제가 있으니 변경사항을 취소하고 변경 이전의 데이터로 복구하라’ 는 의미이다.
오라클의 트랜잭션은 트랜잭션의 대상이 되는 SQL 문장을 실행하면 자동으로 시작되고, 커밋 또는 롤백을 실행한 시점에서 종료된다. 단 다음의 경우에는 커밋과 롤백을 실행하지 않아도 자동으로 트랜잭션이 종료된다.
- CREATE, ALTER, DROP, RENAME, TRUNCATE TABLE 등 DDL 문장을 실행하면, 그 전후 시점에 자동으로 커밋(Auto Commit)이 수행된다. 즉, DML 문장 이후에 명시적 커밋 없이도 DDL 문장이 실행되면 데이터의 변경 사항이 자동으로 커밋된다.
- 데이터베이스를 정상적으로 접속 종료하면 자동으로 트랜잭션이 커밋된다.
- 애플리케이션의 이상 종료로 DB와의 접속이 단절됬을 때는 트랜잭션이 자동으로 롤백된다.
SQL Server의 트랜잭션은 DBMS가 트랜잭션을 컨트롤하는 방식인 AUTO COMMIT이 기본 방식이다. 다음의 경우는 오라클과 같이 자동으로 트랜잭션이 종료된다.
- 애플리케이션의 이상 종료로 DB(인스턴스)와의 접속이 단절됬을 때는 트랜잭션이 자동으로 롤백된다.
읽어주셔서 감사합니다. 😊
Reference
SQL 전문가 가이드 - Kdata 한국데이터산업진흥원