KH_ORACLE_09_트랜잭션 관리, 데이터읽기일관성과 락(Lock)

트랜잭션

오라클에서 발생하는 여러 개의 SQL 명령문들을 하나의 논리적인 작업 단위로 처리하는데 이를 트랜잭션이라고 한다.

하나의 트랜잭션은 ALL - OR - Nothing 방식으로 처리된다. 즉, 여러 개의 명령어의 집합이 정상적으로 처리되면 정상 종료되도록 하고 여러 개의 명령어 중에서 하나의 명령어라도 잘못되었다면 전체를 취소해버린다.

데이터베이스에서 작업의 단위로 트랜잭션이란 개념을 도입한 이유는 데이터의 일관성을 유지하면서 안정적으로 데이터를 복구시키기 위해서이다.

예를들어 은행 현금인출기(ATM)에서 돈을 인출하는 과정으로 트랜잭션을 설명해 보면 아래와 같다.

트랜잭션 제어를 위한 명령어 : COMMIT, SAVEPOINT, ROLLBACK

 

COMMIT과 ROLLBACK

COMMIT과 ROLLBACK의 개념

DML 작업이 성공적으로 처리되도록 하기 위해서는 COMMIT 명령을, 작업을 취소하기 위해서는 ROLLBACK 명령으로 종료해야 한다.

COMMIT

 - COMMIT은 모든 작업들을 정상적으로 확정하는 명령어로 트랜잭션의 처리 과정을 데이터베이스에 모두 반영하기 위해서 변경된 내용을 모두 영구 저장한다.

 - COMMIT 명령어를 수행하게 되면 하나의 트랜잭션 과정을 종료하게 된다.

 

 

ROLLBACK

 - ROLLBACK은 작업 중 문제가 발생되어서 트랜잭션의 처리 과정에서 발생한 변경사항을 취소하는 명령어이다. 즉, 트랜잭션으로 인한 하나의 묶음 처리가 시작되기 이전의 상태로 되돌린다.

 - ROLLBACK 명령어 역시 트랜잭션 과정을 종료하게 된다.

COMMIT 명령어와 ROLLBACK 명령어의 장점

 - 데이터 무결성이 보장된다.

 - 영구적인 변경 전에 데이터의 변경 사항을 확인할 수 있다.

 - 논리적으로 연관된 작업을 그룹화 할 수 있다.

 

COMMIT 명령어

 - Transaction(INSERT, UPDATE, DELETE) 작업 내용을 실제 DB에 저장한다.

 - 이전 데이터가 완전히 UPDATE 된다.

 - 모든 사용자가 변경된 데이터의 결과를 볼 수 있다.

 

ROLLBACK 명령어

 - Transaction 작업 내용을 취소한다.

 - 이전 COMMIT 한 곳까지만 복구한다.

 

자동 COMMIT 명령과 자동 ROLLBACK 명령이 되는 경우

 - SQL* PLUS가 정상 종료되었다면 자동으로 COMMIT 되지만, 비정상 종료되었다면 자동으로 ROLLBACK 한다.

 - DDL과 DCL(Data Control Language, User 관리) 명령문이 수행된 경우 자동으로 COMMIT 된다.

 - 정전이 발생했거나 컴퓨터 Down시(컴퓨터의 전원이 끊긴) 자동으로 ROLLBACK 된다.

 

자동 커밋

DDL문에는 CREATE, ALTER, DROP, RENAME, TRUNCATE 등이 있다. 이러한 DDL문은 자동으로 커밋을 실행한다.

(AUTO COMMIT)

 

트랜잭션을 작게 분할하는 SAVEPOINT

 - SAVEPOINT 명령을 써서 현재의 트랜잭션을 작게 분할할 수 있다.

 - 저장된 SAVEPOINT는 ROLLBACK TO SAVEPOINT 문을 사용하여 표시한 곳까지 ROLLBACK할 수 있다.

 - 여러 개의 SQL문의 실행을 수반하는 트랜잭션의 경우, 사용자가 트랜잭션 중간 단계에서 세이브포인트를 지정할 수 있다.

 - 이 세이브포인트는 차후 롤백과 함께 사용해서 현재 트랜잭션 내의 특정 세이브포인트까지 롤백할 수 있게 됩니다.

-- 형식
SAVEPOINT LABEL_NAME; -- 특정 위치 지정

ROLLBACK TO LABEL_NAME; -- 특정 위치로 되돌아가기

-- 예 : 트랜잭션 중간 단계에서 세이브포인트 지정하기

DROP TABLE DEPT01;

CREATE TABLE DEPT01
AS
SELECT * FROM DEPT;

DELETE FROM DEPT01 WHERE DEPTNO=40;

COMMIT;

SELECT * FROM DEPT01;

DELETE FROM DEPT01
WHERE DEPTNO=30;

SAVEPOINT C1;

DELETE FROM DEPT01
WHERE DEPTNO=20;

SELECT * FROM DEPT01;

SAVEPOINT C2;

DELETE FROM DEPT01
WHERE DEPTNO=10;

SELECT * FROM DEPT01;

ROLLBACK TO C2;

SELECT * FROM DEPT01;

ROLLBACK TO C1;

SELECT * FROM DEPT01;

ROLLBACK;

SELECT * FROM DEPT01;

 

데이터 읽기 일관성과 락

 - 오라클이 데이터 읽기의 일관성을 제공해 준다는 것을 증명을 하기 위해서 우선 다음과 같은 가정을 하겠다.

 - 오라클 서버가 서울 본사에 설치되어 있고 이 데이터베이스를 서울 본사 직원과 대전 지사 직원이 공용하고 있다고 하자.

어느 날 서울 본사 직원과 대전 지사 직원이 동일한 테이블을 같은 시간에 접근해서 사용할 경우 어떻게 일이 일어날 수 있는지 살펴보도록 하자.

 

데드 락

데드락이란 한마디로 무한 교착상태이다. 하나의 상자에 들어 있는 물건을 서로 가지려고 두 사람이 자기만의 자물쇠로 상자를 잠궈 버린 경우라고 할 수 있다. 이렇게 되면 서로 상자의 물건을 어느 누구도 가질 수 없게 된다.

 

[데드락 실습]

-- 1. 왼쪽 사용자가 SCOTT 사원의 정보를 변경하려고 시도한다.
UPDATE EMP01 SET SAL=100
WHERE ENAME='SCOTT';

-- 2. 오른쪽 사용자가 SMITH 사원의 정보를 변경하려고 시도한다.
UPDATE EMP01 SET SAL=20
WHERE ENAME='SMITH';

-- 3. 왼쪽 사용자가 SMITH 사원의 정보를 갱신하려고 시도한다.
UPDATE EMP01 SET SAL=300
WHERE ENAME='SMITH';

-- 4. 왼쪽 사용자가 대기 상태인데 오른쪽 사용자가 커밋이나 롤백을 하지 않고 왼쪽 사용자가 사용 중인 SCOTT의 정
보를 변경하려고 시도한다.
UPDATE EMP01 SET SAL=400
WHERE ENAME='SCOTT';

-- 5. 오른쪽 사용자가 ROLLBACK을 입력하여 트랜잭션을 마무리 짓고,

 

SET UNUSED

DDL 작업을 하는 동안에도 락이 발생하게 되는데, 이럴 경우에 SET UNUSED라는 키워드를 사용하여 해결한다.

SET UNUSED는 해당 컬럼을 실제로 삭제하는 것이 아니라 삭제된다는 표시만 하는 논리적 삭제만 일어나게 하므로 실제 수행 시간은 극히 짧다. 따라서 락이 걸리는 시간도 짧으므로 다른 사용자의 사용 제한 시간이 상대적으로 짧다.

DROP TABLE EMP02;

CREATE TABLE EMP02
AS
SELECT * FROM EMP;

ALTER TABLE EMP02
SET UNUSED (JOB);

SELECT * FROM EMP02; -- 실제로 JOB 컬럼이 존재하지만 논리적으로 사용을 제한하기 위해서 UNUSED 속성을 지정함.

 

DDL 명령의 롤백

DDL은 자동 커밋이 일어나므로 이전 상태로 되돌리기 위해서 롤백할 수 없다.

DDL 작업을 수행하기 전에 원본 테이블을 복사해 놓고 롤백이 필요할 경우에 복사본을 원본 테이블로 대체한다.

DROP TABLE EMP01;

CREATE TABLE EMP01
AS
SELECT * FROM EMP; -- 원본 테이블(EMP01)

DROP TABLE EMP02;

CREATE TABLE EMP02
AS
SELECT * FROM EMP01; -- DDL 작업을 수행하기 전에 원본 테이블의 복사본(EMP02)을 생성한다.

ALTER TABLE EMP01
DROP COLUMN JOB; -- 원본 테이블에서 컬럼을 삭제한다.

SELECT * FROM EMP01;

SELECT * FROM EMP02;

DROP TABLE EMP01;

RENAME EMP02 TO EMP01; -- 원본 테이블을 삭제하고 복사본을 원본 테이블명으로 변경한다. (롤백과 같은 효과)

SELECT * FROM EMP01;

 

TRUNCATE와 DELETE의 차이

TRUNCATE

 - 모든 행을 삭제한다

 - DDL 명령어로 자동으로 커밋이 발생한다.

DELETE

 - WHERE 절을 추가하여 조건에 만족하는 행만 삭제할 수 있다.

 - DML 명령어로 롤백이 가능하다는 장점이 있지만 롤백을 위해서 무수히 많은 BEFORE IMAGE가 생성되어야 하므로 삭제하는 속도도 늦고 이를 위한 자원이 마련되어야 한다.