TCL
IT 위키
더 많은 작업
- 상위 문서: SQL
- Transaction Control Language; 트랜잭션 제어어
- 트랜잭션을 확정(COMMIT)하거나 취소(ROLLBACK)하거나 중간 저장점(SAVEPOINT)을 두어 데이터 변경의 반영 단위를 제어하는 SQL 명령
TCL은 DML로 바꾼 데이터를 데이터베이스에 영구히 반영할지, 변경 전 상태로 되돌릴지를 정하는 명령이다. 대표 명령은 COMMIT, ROLLBACK, SAVEPOINT이다. 트랜잭션 특성 중 원자성(모두 반영하거나 모두 취소)과 지속성(확정한 변경은 남음)이 이 명령들로 구현된다.
| 명령 | 역할 |
|---|---|
| COMMIT | 현재 트랜잭션의 변경을 모두 확정하고 트랜잭션을 끝낸다. |
| ROLLBACK | 현재 트랜잭션의 변경을 모두 취소하고 트랜잭션을 끝낸다. |
| ROLLBACK TO 저장점 | 지정한 저장점 이후의 변경만 취소한다. 트랜잭션은 끝나지 않고 계속된다. |
| SAVEPOINT 저장점 | 트랜잭션 안에 되돌아갈 지점을 표시한다. |
- SQLD 출제기준은 관리 구문을 DML, TCL, DDL, DCL로 나눈다. Oracle SQL Language Reference도 COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION, SET CONSTRAINT를 Transaction Control Statements로 따로 분류한다.
- 일부 교재와 기존 문서는 COMMIT과 ROLLBACK을 DCL에 넣기도 한다. 시험에서는 TCL로 구분하는 것이 기준이다.
Oracle에서 트랜잭션은 첫 번째 실행 가능한 SQL 문(DML, DDL 등)을 만나면 시작하고, 다음 경우에 끝난다.
- COMMIT 또는 ROLLBACK(SAVEPOINT 절 없이)을 실행한 경우
- CREATE, ALTER, DROP, RENAME, TRUNCATE 같은 DDL을 실행한 경우: DDL 앞뒤로 암묵적 COMMIT이 일어난다.
- 대부분의 Oracle 도구를 정상 종료한 경우: 현재 트랜잭션이 암묵적으로 커밋된다. 다만 Oracle 문서는 연결을 끊을 때의 커밋 동작이 응용 프로그램에 따라 다르고 설정할 수 있다고 밝히며, 종료 전에 명시적으로 COMMIT이나 ROLLBACK을 하라고 권한다.
- 클라이언트 프로세스가 비정상 종료한 경우: 트랜잭션이 자동으로 롤백된다.
| 시점 | 변경한 세션 | 다른 세션 | 잠금 |
|---|---|---|---|
| COMMIT·ROLLBACK 전 | 변경된 값을 본다. | 변경 전 값을 본다. | 변경한 행에 잠금이 걸려 다른 세션은 그 행을 변경할 수 없다. |
| COMMIT 후 | 변경된 값 | 변경된 값을 본다. | 잠금이 풀리고 저장점이 모두 지워진다. |
| ROLLBACK 후 | 변경 전 값 | 변경 전 값 | 잠금이 풀린다. |
두 세션을 열어 확인한 결과는 다음과 같다. 계좌 테이블에는 (1, 1000), (2, 2000)이 커밋되어 있다.
| 순서 | 세션 1 | 세션 2 | 결과 |
|---|---|---|---|
| 1 | UPDATE 계좌 SET 잔액 = 0 WHERE 번호 = 1 |
1행 변경 | |
| 2 | SELECT |
1번 잔액 0 | |
| 3 | SELECT |
1번 잔액 1000(변경 전 값) | |
| 4 | 1번 행 SELECT ... FOR UPDATE NOWAIT |
ORA-00054(잠금을 얻지 못함) | |
| 5 | 2번 행 SELECT ... FOR UPDATE NOWAIT |
성공(다른 행은 잠기지 않음) | |
| 6 | COMMIT |
잠금 해제 | |
| 7 | SELECT |
1번 잔액 0 |
-- 계좌: (1, '김', 1000), (2, '이', 2000) 이 커밋된 상태
INSERT INTO 계좌 VALUES (3, '박', 3000);
SAVEPOINT SP1;
UPDATE 계좌 SET 잔액 = 잔액 - 500 WHERE 번호 = 1;
SAVEPOINT SP2;
DELETE FROM 계좌 WHERE 번호 = 2;
ROLLBACK TO SP2;
ROLLBACK TO SP1;
ROLLBACK;
각 단계 뒤에 SELECT COUNT(*), SUM(잔액) FROM 계좌를 실행한 결과는 다음과 같다.
| 단계 | 행 수 | 잔액 합계 | 설명 |
|---|---|---|---|
| 시작 | 2 | 3000 | 커밋된 상태 |
| INSERT 3번 | 3 | 6000 | |
| SAVEPOINT SP1 후 UPDATE 1번 | 3 | 5500 | |
| SAVEPOINT SP2 후 DELETE 2번 | 2 | 3500 | |
| ROLLBACK TO SP2 | 3 | 5500 | DELETE만 취소 |
| ROLLBACK TO SP1 | 3 | 6000 | UPDATE까지 취소, INSERT는 남음 |
| ROLLBACK | 2 | 3000 | 트랜잭션 전체 취소 |
- ROLLBACK TO SP1을 실행하면 그 뒤에 만든 SP2는 사라진다. 이어서
ROLLBACK TO SP2를 실행하면 ORA-01086 오류가 난다. - 같은 이름으로 저장점을 다시 만들면 앞의 저장점은 지워진다. 아래에서
ROLLBACK TO A는 두 번째 A로 돌아가므로 4번 행은 남고 5번 행만 취소된다.
INSERT INTO 계좌 VALUES (3, '박', 3000);
SAVEPOINT A;
INSERT INTO 계좌 VALUES (4, '최', 4000);
SAVEPOINT A; -- 앞의 A는 지워진다
INSERT INTO 계좌 VALUES (5, '정', 5000);
ROLLBACK TO A;
SELECT 번호 FROM 계좌 ORDER BY 번호; -- 1, 2, 3, 4
- COMMIT하면 저장점이 모두 지워지므로 COMMIT 뒤에
ROLLBACK TO저장점을 실행하면 ORA-01086 오류가 난다.
Oracle은 DML 뒤에 COMMIT을 명시해야 반영되지만, DDL은 실행 앞뒤로 자동 커밋(암묵적 커밋)한다. 그래서 DDL을 실행하면 그 앞의 DML도 함께 확정되어 ROLLBACK으로 되돌릴 수 없다.
-- 계좌: 1, 2번 행이 커밋된 상태
INSERT INTO 계좌 VALUES (9, '홍', 9000);
CREATE TABLE 임시 (A NUMBER); -- 이 시점에 앞의 INSERT가 커밋된다
ROLLBACK;
SELECT 번호, 이름 FROM 계좌 ORDER BY 번호;
| 번호 | 이름 |
|---|---|
| 1 | 김 |
| 2 | 이 |
| 9 | 홍 |
- ROLLBACK을 했는데도 9번 행이 남는다.
- 실습에서는 이미 있는 이름으로 CREATE TABLE을 실행해 ORA-00955 오류가 난 경우에도 앞의 INSERT가 커밋되어 ROLLBACK 후에도 남았다. Oracle은 DDL을 실행하기 전에 먼저 커밋하기 때문이다.
- TRUNCATE TABLE도 DDL이므로 ROLLBACK으로 되돌릴 수 없다. Oracle 문서는 TRUNCATE TABLE 문을 롤백할 수 없다고 명시한다.
| 항목 | Oracle | SQL Server |
|---|---|---|
| 기본 동작 | DML은 COMMIT해야 확정된다. | 자동 커밋(Autocommit) 모드가 기본이다. 문장 하나하나가 트랜잭션이 되어 바로 확정된다. |
| 명시적 트랜잭션 | 첫 실행 문장에서 자동으로 시작한다. | BEGIN TRAN(BEGIN TRANSACTION)으로 시작해 COMMIT 또는 ROLLBACK으로 끝낸다.
|
| 암시적 트랜잭션 | 기본 방식과 같다. | SET IMPLICIT_TRANSACTIONS ON이면 INSERT, UPDATE, DELETE, CREATE, DROP 등이 트랜잭션을 시작하고 COMMIT이나 ROLLBACK을 직접 해야 끝난다.
|
| 저장점 | SAVEPOINT 이름, ROLLBACK TO 이름 |
SAVE TRAN 이름, ROLLBACK TRAN 이름
|
| DDL | 앞뒤로 자동 커밋되어 롤백할 수 없다. | 자동 커밋되지 않는다. CREATE, ALTER TABLE, DROP, TRUNCATE TABLE도 암시적 트랜잭션을 시작하는 문장 목록에 들어 있으며, 문서에는 트랜잭션 안의 TRUNCATE TABLE을 ROLLBACK으로 되돌리는 예제가 있다. |
-- SQL Server
BEGIN TRAN;
UPDATE 계좌 SET 잔액 = 잔액 - 500 WHERE 번호 = 1;
SAVE TRAN SP1;
DELETE FROM 계좌 WHERE 번호 = 2;
ROLLBACK TRAN SP1; -- DELETE만 취소
COMMIT; -- UPDATE 확정
- SAVEPOINT와 ROLLBACK TO가 섞인 문장열을 주고 최종 행 수나 합계를 묻는 문제가 많다. ROLLBACK TO 저장점은 그 뒤의 변경만 취소하고, 저장점 없는 ROLLBACK은 마지막 COMMIT 이후 전체를 취소한다.
- Oracle에서 DDL(CREATE, ALTER, DROP, TRUNCATE)은 자동 커밋되므로 그 앞의 DML도 확정된다. DDL 뒤의 ROLLBACK으로 앞의 INSERT를 되돌릴 수 없다.
- SQL Server는 기본이 자동 커밋이다. BEGIN TRAN을 쓰지 않으면 문장마다 바로 확정된다.
- COMMIT 전에는 다른 세션이 변경 전 데이터를 보고, 변경된 행은 잠긴다. COMMIT 후에는 모든 세션이 변경된 값을 본다.
- 같은 이름의 저장점을 다시 만들면 앞의 것은 지워지고 뒤의 것만 남는다.