[오라클, Oracle] TCS 문 - 기본 문법

 

오라클 데이터베이스는 COMMIT 문, ROLLBACK 문, SAVEPOINT 문, SET TRANSACTION 문, SET CONSTRAINT 문 등의 TCS 문을 제공한다.

 

[COMMIT 문]

COMMIT 문은 현재 트랜잭션의 변경 내용을 데이터베이스에 영구적으로 저장하고 트랜잭션이 종료한다.

(COMMIT 문의 옵션은 대부분 성능과 관련된 기능이다.)

 

COMMIT [WORK] [[COMMENT string] | [WRITE [WAIT | NOWAIT] [IMMEDIATE | BATCH]]

| FORCE string [, integer]];

 

-- 아래 쿼리는 DELETE 문을 수행한 후 COMMIT 문을 수행했다.

-- CD가 3인 행이 영구적으로 삭제되었다.

 

DELETE FROM T1 WHERE CD = 3;

COMMIT;

 

아래는 잘못된 계좌이체 트랜잭션의 예시다.

1번 계좌의 잔고에서 10을 빼고 COMMIT 문을 수행했기 때문에 하나의 트랜잭션으로 처리되지 않았다.

트랜잭션의 원자성이 보장되지 않기 때문에 데이터 일관성이 위배될 수 있다.

 

UPDATE T1 SET VL = VL - 10 WHERE CD = 1;

COMMIT;

 

UPDATE T1 SET VL = VL + 10 WHERE CD = 2;

 

명령 프롬프트 창을 강제 종료한 후 다시 접속해보자.

(EXIT 명령어가 아니라 명령 프롬프트 창의 X 버튼을 눌러 종료시켜야 한다.)

T1 테이블을 조회해보면 2번 계좌의 잔고에 10을 더한 작업이 롤백된 것을 확인할 수 있다.

잔고의 합계가 90이 되어 데이터 일관성이 위배되었다.

 

SELECT * FROM T1;

 

-- 다음 예제를 위해 데이터 원상 복구

UPDATE T1 SET VL = 50 WHERE CD = 1;

COMMIT;

 

-- 아래 예제는 2개의 UPDATE 문을 하나의 트랜잭션으로 처리

-- COMMIT 문으로 트랜잭션을 종료했기 때문에 트랜잭션에 의한 변경 내용이 영구적으로 저장된다.

 

UPDATE T1 SET VL = VL - 10 WHERE CD = 1;

UPDATE T1 SET VL = VL + 10 WHERE CD = 2;

COMMIT;

 

-- 명령 프롬프트 창을 강제 종료한 후 다시 접속해보자.

-- T1 테이블을 조회해보면 트랜잭션에 의한 변경 내용이 저장된 것을 확인할 수 있다.

 

SELECT * FROM T1;

 

[ROLLBACK 문]

ROLLBACK 문은 현재 트랜잭션의 변경 내용을 모두 취소하고 트랜잭션을 종료한다.

 

ROLLBACK [WORK] [TO [SAVEPOINT] savepoint | FORCE string];

 

-- 아래와 같이 T1 테이블의 전체 행을 삭제해보자.

DELETE FROM T1;

 

-- T1 테이블을 조회해보면 행이 존재하지 않는 것을 확인할 수 있다.

SELECT * FROM T1;

 

아래와 같이 롤백을 수행해보자.

ROLLBACK;

 

T1 테이블을 다시 조회해보면 변경 사항이 모두 롤백된 것을 확인할 수 있다.

ROLLBACK 문은 언두 세그먼트에 저장된 변경 이전 데이터를 통해 데이터를 복구한다.

 

SELECT * FROM T1;

 

-- 아래 예제는 2개의 UPDATE 문을 하나의 트랜잭션으로 처리했다.

UPDATE T1 SET VL = VL - 10 WHERE CD = 1;

UPDATE T1 SET VL = VL + 10 WHERE CD = 2;

 

명령 프롬프트 창을 강제 종료한 후 다시 접속해보자.

T1 테이블을 조회해보면 변경 사항이 모두 롤백된 것을 확인할 수 있다.

 

SELECT * FROM T1;

 

[SAVEPOINT 문]

SAVEPOINT 문은 롤백할 수 있는 저장점을 생성한다.

 

SAVEPOINT savepoint;

 

-- 아래 쿼리는 SAVEPOINT 문으로 S1, S2 저장점을 생성한 후 DELETE 문으로 전체 행을 삭제했다.

 

UPDATE T1 SET VL = VL - 10 WHERE CD = 2;

 

SAVEPOINT S1;

 

UPDATE T1 SET VL = VL + 10 WHERE CD = 1;

 

SAVEPOINT S2;

 

DELETE FROM T1;

 

SELECT * FROM T1;

 

-- 아래 쿼리는 S2 저장점으로 트랜잭션을 롤백한다.

T1 테이블을 조회해보면 두 번째 UPDATE 문이 적용된 시점으로 데이터가 롤백된 것을 확인할 수 있다.

 

ROLLBACK TO SAVEPOINT S2;

 

-- 아래 쿼리는 S1 저장점으로 트랜잭션을 롤백한다.

T1 테이블을 조회해보면 첫 번째 UPDATE 문이 적용된 시점으로 데이터가 롤백된 것을 확인할 수 있다.

 

RLLBACK TO SAVEPOINT S1;

 

SELECT * FROM T1;

 

아래 쿼리는 에러가 발생한다.

특정 저장점(S1)으로 롤백하면 해당 저장점 이후에 생성된 모든 저장점(S2)이 제거되기 때문이다.

 

ROLLBACK TO SAVEPOINT S2;

 

ORA-01086: 'S2' 저장점이 이 세션에 설정되지 않았거나 부적합합니다.

 

-- 전체 트랜잭션 롤백. 데이터 원상 복구

ROLLBACK;

SELECT * FROM T1;

 

-- 아래 예제의 두 번째 UPDATE 문은 NUMBER 타입인 VL 열에 문자 값을 입력했기 때문에 오류가 발생했다.

UPDATE T1 SET VL = VL + 10 WHERE CD = 1;

 

UPDATE T1 SET VL = 'A' WHERE CD = 1;

 

ORA-01722: 수치가 부적합합니다.

 

T1 테이블을 조회해보면 첫 번째 UPDATE 문의 변경 내용이 롤백되지 않은 것을 확인할 수 있다.

오라클 데이터베이슨느 DML 문이 수행될 때마다 내부적으로 저장점을 생성하고, 에러가 발생하면 직전의 저장점으로 문장 단위 롤백(statement-level rollback)을 수행한다.

(이런 동작을 문장 수준 원자성(statement-level atomicity)이라고 한다.

 

SELECT * FROM T1;

 

다음 예제를 위해 롤백을 수행

ROLLBACK;

 

[암시적 커밋]

DDL 문은 수행 전에 트랜잭션을 커밋하고 종료 후 다시 트랜잭션을 커밋한다.

이런 동작을 암시적 커밋(implicit commit)이라고 한다.

 

-- DDL 문의 동작에 대한 슈도 코드다.

BEGIN

COMMIT;

DDL 문;

COMMIT;

EXCEPTION

WHEN OTHERS THEN ROLLBACK; RAISE;

END;

 

-- 예제 테이블 생성

DROP TABLE T2 PURGE;

CREATE TABL2 T2 (C1 NUMBER);

 

-- 아래와 같이 INSERT 문을 수행하고, DDL 문인 CREATE TABLE 문을 수행한 후, 롤백을 수행하자.

INSERT INTO T2 VALUES (1);

 

CREATE TABLE T2 (C1 NUMBER);

 

ORA-00955: 기존의 객체가 이름을 사용하고 있습니다.

 

ROLLBACK;

 

T2 테이블을 조회해보면 데이터가 롤백되지 않은 것을 확인할 수 있다.

CREATE TABLE 문에 의해 트랜잭션이 커밋되기 때문에 INSERT 문에 의한 변경이 롤백되지 않은 것이다.

 

SELECT * FROM T2;

 

불친절한 SQL 프로그래밍저자정희락출판디비안(DBian)발매2018.09.10.