[오라클, Oracle] TCS 문 - 트랜잭션

 

TCS는 Transaction Control Statement의 약자다.

트랜잭션 제어문으로 해석할 수 있다.

트랜잭션과 TCS 문의 기본 문법을 살펴본 후, 트랜잭션과 관련된 데이터 동시성, 데이터 일관성에 대해 살펴보자.

 

-- 예제 테이블 생성

DROP TABLE T1 PURGE;

CREATE TABLE T1 (CD NUMBER, VL NUMBER) ROWDEPENDENCIES;

 

INSERT INTO T1 VALUES (1, 50);

INSERT INTO T1 VALUES (2, 50);

COMMIT;

 

[ROWDEPENDENCIES 절]

테이블 생성 시 ROWDEPENDENCIES 절을 기술하면 row-level dependency tracking 기능이 활성화된다.

해당 기능을 활성화하면 SCN이 행 수준으로 저장되며 행의 길이가 6바이트씩 증가한다.

기본값은 NOROWDEPENDENCIES로 SCN이 블록 수준으로 저장된다.

 

[트랜잭션]

트랜잭션(transaction)은 함께 수행해야 하는 작업의 논리적인 단위다.

계좌이체 트랜잭션은 출금 계좌의 잔고를 차감하는 작업과 입금 계좌의 잔고를 증가시키는 작업으로 구성된다.

두 작업은 반드시 하나의 트랜잭션으로 수행되어야 한다.

 

-- 아래 쿼리는 1번 계좌에서 2번 계좌로 10원을 이체하는 트랜잭션을 수행한다.

1번 계좌의 잔고에서 10원을 빼는 쿼리와 2번 계좌의 잔고에 10원을 더하는 쿼리가 하나의 트랜잭션으로 수행되었다.

두 쿼리가 하나의 트랜잭션으로 수행되지 않으면 잔고의 합계가 달라질 수 있다.

 

UPDATE 계좌 SET 잔고 = 잔고 - 10 WHERE 계좌번호 = 1;

UPDATE 계좌 SET 잔고 = 잔고 + 10 WHERE 계좌번호 = 2;

COMMIT;

 

[구조]

트랜잭션은 DML 문이나 SET TRANSACTION 문이 실행되면 시작되고, COMMIT 문이나 ROLLBACK 문이 실행되면 종료된다. 트랜잭션이 시작되면 내부적으로 언두 세그먼트 (undo segment)가 할당되고,

(언두 세그먼트는 변경 전의 데이터가 저장되는 테이블이다. 트랜잭션 롤백, 읽기 일관성, 트랜잭션 복구 등을 위해 사용된다.)

트랜잭션에 트랜잭션 ID(XID)가 부여된다. 트랜잭션 ID는 언두 세그먼트의 번호, 슬롯, 시퀀스의 조합으로 생성된다.

 

V$TRANSACTION 뷰에서 트랜잭션에 대한 정보를 조회할 수 있다.

아래 쿼리는 결과가 반환되지 않는다.

수행 중인 트랜잭션이 존재하지 않기 때문이다.

 

SELECT * FROM V$TRANSACTION;

 

아래와 같이 INSERT 문을 수행해보자.

INSERT INTO T1 VALUES (3, 50);

 

V$TRANSACTION 뷰를 다시 조회해보면 트랜잭션이 시작된 것을 확인할 수 있다.

START_SCN 열은 트랜잭션이 시작된 SCN을 반환한다.

 

SELECT XID, XIDUSN, XIDSLOT, XIDSQN, START_DATE, START_SCN FROM V$TRANSACTION;

 

COMMIT 문을 수행하면 트랜잭션이 종료된다.

COMMIT;

 

V$TRANSACTION 뷰를 다시 조회하면 결과가 반환되지 않는다.

SELECT * FROM V$TRANSACTION;

 

[SCN]

SCN(System Change Number)은 오라클 데이터베이스의 논리적 TIMESTAMP다. 데이터베이스 내부의 작업 순서를 식별하는 용도로 사용된다. 트랜잭션도 내부적으로 SCN을 사용한다.

 

ORA_ROWSCN 슈도 칼럼은 행의 SCN을 반환한다.

CD가 3인 행의 ORA_ROWSCN 값이 앞서 살펴본 V$TRANSACTION 뷰의 START_SCN 값과 동일한 것을 확인할 수 있다.

SCN_TO_TIMESTAMP 함수를 사용하면 SCN 값을 TIMESTAMP 값으로 변환할 수 있다.

 

SELECT CD, VL, ORA_ROWSCN, SCN_TO_TIMESTAMP (ORA_ROWSCN) AS C1 FROM T1;

현재 SCN은 V$DATABASE 뷰의 checkpoint_change# 열에서 확인할 수 있다.

 

SELECT checkpoint_change# FROM V$DATABASE;

 

[ORA-08181 에러]

SCN_TO_TIMESTAMP 함수는 SYS.SMON_SCN_TIME 테이블을 참조한다.

해당 테이블은 데이터베이스가 시작된 시간으로부터 최대 120시간(5일) 동안의 SCN을 저장한다.

(버전에 따라 저장되는 행의 개수가 상이할 수 있다.)

5일 이전의 SCN에 SCN_TO_TIMESTAMP 함수를 사용하면 "ORA-08181: 지정된 번호는 적합한 시스템 변경 번호가 아님" 에러가 발생할 수 있다.

 

아래는 SYS.SMON_SCN_TIME 테이블을 조회한 결과다.

 

SELEC TIME_DP, SCN_BAS FROM SYS.SMON_SCN_TIME ORDER BY SCN_BAS DESC;

 

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