DATABASE/SQL, PL-SQL

트랜잭션(Transaction) 제어

devstep88 2021. 1. 27. 22:40

데이터베이스의 트랜잭션은 ACID라고 불리는 네 가지 특성을 가진다.

특성 설명
원자성(Acomicity) 트랜잭션이 일부분만 실행되는 일이 발생하지 않을 것을 보장한다. 트랜잭션의 한 오퍼레이션이 실패하면 전체 트랜잭션이 실패하고 데이터베이스는 변경되지 않는다.
일관성(Consistency) 트랜잭션의 실행 전후에 데이터베이스는 항상 유효한 상태를 가질 것을 보장한다. 데이터베이스는 선언적인 업무 규칙(데이터 타입, 제약조건, 트리거 등)은 물론이고 응용 프로그램에서 의도하는 모든 업무규칙을 만족시킨다.
고립성(Isolation) 동시 실행된 트랜잭션의 결과는 트랜잭션이 순차적으로 실행되었을 때의 결과와 동일함을 보장한다. 트랜잭션의 수행에 다른 트랜잭션의 오퍼레이션이 끼어들어 처리결과를 바꿀 수 없다.
지속성(Durability) 성공적으로 수행된 트랜잭션은 데이터베이스에 영구적으로 반영됨을 보장한다. 전원이상, 데이터베이스 장애, 오류 등이 발생하더라도 데이터베이스는 데이터를 장애 발생 전 상태로 복귀할 수 있다.

트랜잭션의 일관성과 지속성은 사용자에 의해 제어될 수 있는 특성이 아니며 어떤 조건에서도 항상 규정된 대로 지켜져야 하는 특성이다. 트랜잭션의 원자성 단위는 COMMIT, ROLLBACK, SAVEPOINT를 사용하여 제어할 수 있다.

데이터베이스에서의 모든 처리는 트랜잭션이라는 묶음 단위로 연산 전체가 승인(COMMIT)되거나 전체가 취소(ROLLBACK)된다, 트랜잭션의 고립성 제어에는 SET TRANSACTION문을 사용한다.

 

트랜잭션 제어문

COMMIT

COMMIT은 현재의 트랜잭션을 완료하여 변경 사항을 데이터베이스에 영구히 저장하고, 다른 사용자들의 쿼리 수행 시에 해당 변경사항을 볼 수 있도록 하는 SQL이다. 커밋의 대상은 시간상으로 최근의 커밋 또는 롤백 이후로부터 현재까지 트랜잭션이 행한 모든 변경이다. 이 전에 커밋도 롤백도 없었다면 세션(데이터베이스 접속) 접속 직후의 모든 변경이 커밋 대상이 된다. 트랜잭션은 기본적으로 원자성을 가지므로 트랜잭션의 특정 부분만을 커밋할 수는 없다.

BEGIN
	DELETE FROM emp WHERE empno = 9000; -- 이전 예제에서 생성된 데이터 삭제
    COMMIT;
    INSERT INTO emp(empno, ename, hiredate, sal)
    		VALUES(9000, '홍길동', SYSDATE. 9000);
    UPDATE EMP SET sal = sal + 100 WHERE empno = 9000;
    COMMIT;
END;

위 프로그램에서 6번 줄의 COMMIT은 4번 줄과 5번 줄의 변경을 한 번에 COMMIT한다.

 

ANSI SQL 표준에서 COMMIT은 COMMIT WORK로도 사용할 수 있도록 되어 있다. 오라클도 ANSI표준을 지원한다.

다시 말해 COMMIT과 COMMIT WORK는 기능적으로 완전히 동일한 문장이다.

 

묵시적 COMMIT

트랜잭션 제어에서 한 가지 주의할 사항은 DDL(Data Definition Language)을 실행하는 경우이다.

오라클은 DDL을 수행할 때 명시적인 COMMIT이 없더라도 현재의 트랜잭션을 자동으로 커밋하도록 구현되었는데, 이와 같은 메커니즘을 묵시적 COMMIT이라고 한다.

 

-- DDL에 의한 묵시적 COMMIT
BEGIN
	DELETE FROM emp WHERE empno = 9000; -- 이전 예제에서 생성한 데이터 삭제
    COMMIT;
    INSERT INTO emp(empno, ename, hiredate, sal) VALUES (9000, '홍길동', SYSDATE, 9000);
    UPDATE EMP SET SAL = SAL + 100 WHERE EMPNO = 9000;
    EXECUTE IMMEDIATE 'CREATE TABLE t(C1 NUMBER)';  --DDL이 수행되면 자동으로 COMMIT이 수행된다.
    ROLLBACK;
    DECLARE
    	v_sal NUMBER;
    BEGIN
    	SELECT sal INTO v_sal FROM emp WHERE empno = 9000;
        DBMS_OUTPUT_PUT_LINE('SAL = '||v_sal);	-- 사번 9000에 대한 DML이 COMMIT됨
    END;
END;

위 프로그램에서 6번 줄의 DDL이 수행되면서 트랜잭션을 묵시적으로 COMMIT한다.

즉 4번 줄의 INSERT문과 5번 줄의 UPDATE문으로 인한 변경은 COMMIT이 없지만 6번 줄에서 DDL을 만나면서 묵시적으로 COMMIT된다. 따라서 7번 줄에서 ROLLBACK을 만나더라도 트랜잭션은 이미 COMMIT되어 ROLLBACK은 4번 줄과 5번 줄의 변경을 취소하지 못한다.

 

ROLLBACK

트랜잭션의 모든 변경을 취소하는 SQL

BEGIN
	DELETE FROM emp WHERE empno = 9000; -- 이전 예제에서 생성한 데이터 삭제
    COMMIT;
    INSERT INTO emp(empno, ename, hiredate, sal) VALUES (9000, '홍길동', SYSDATE, 9000);
    UPDATE EMP SET SAL = SAL + 100 WHERE EMPNO = 9000;
    EXECUTE IMMEDIATE 'CREATE TABLE t(C1 NUMBER)';  --DDL이 수행되면 자동으로 COMMIT이 수행된다.
    ROLLBACK;
    DECLARE
    	v_sal NUMBER;
    BEGIN
    	SELECT count(*) INTO v_cnt FROM emp WHERE empno = 9000;
        DBMS_OUTPUT_PUT_LINE('사번 9000 건수 = '||v_cnt);
    END;
END;

SAVEPOINT

SAVEPOINT를 사용하면 트랜잭션의 특정 지점에 표시를 한 후 특정 상황에서 트랜잭션 전체가 아니라 해당 지점 이후의 변경만 롤백할 수 있도록 해준다.

DECLARE
	v_org_sal NUMBER := 5000;
BEGIN
	DELETE FROM emp WHERE empno = 9000; -- 이전 예제 데이터 삭제
    COMMIT;
    INSERT INTO emp(empno, ename, hiredate, sal)
    	   VALUES(9000, '홍길동', SYSDATE, v_org_sal);
    SAVEPOINT p1;	-- 첫 번째 SAVEPOINT p1
    UPDATE emp SET sal = sal + 100 WHERE empno = 9000;
    SAVEPOINT p2;	-- 두 번째 SAVEPOINT p2
    BEGIN
    	INSERT INTO emp(empno, ename, hiredate, sal)
        	   VALUES(9000, '임꺽정', SYSDATE, v_org_sal);
       EXCEPTION WHEN OTHERS THEN
       	-- 12번 줄의 INSERT문이 실패하면 9번 줄의 UPDATE와 12번 줄의 INSERT는 취소하고
        -- 6번 줄의 INSERT문은 변경에 반영하도록 한다.
        DBMS_OUTPUT_PUT_LIME('오류 발생 감지: '||SQLERRM); -- 오류 메시지 출력
        ROLLBACK TO p1;	-- 트랜잭션을 p1 상태로 복귀
        END;
        COMMIT;
        DECLARE
        	v_sal NUMBER;
        BEGIN
        	SELECT sal INTO v_sal FROM emp WHERE empno = 9000;
            DBMS_OUTPUT_PUT_LINE('SAL = '||v_sal);	-- 6번 줄에서 INSERT된 급여가 출력
            IF v_org_sal <> v_sal THEN
            	DBMS_OUTPUT_PUT_LINE('원 급여가 변경되었습니다.');
            ELSE
           		DBMS_OUTPUT_PUT_LINE('원 급여가 변경되지 않았습니다.');
			END IF;
        END;
END;

위 코드에서 주의할 점은  18번 줄의 ROLLBACK TO문은 지정된 SAVEPOINT 이후의 트랜잭션을 취소하라는 문장이지 8번 줄로 되돌아가서 그 지점부터 다시 실행하라는 의미는 아니다. SAVEPOINT p1 이후의 트랜잭션만을 취소할 뿐이며 실행은 20번 줄로 계속 진행되어 롤백되지 않은 6번 줄의 INSERT문에 의한 변경을 커밋한다.

 

세이브포인트의 이름이 매 지점마다 달라야 할 필요는 없다.

동일하게 쓴다면 뒤에 쓴 세이브포인트의 위치로 재정의한 것뿐이다.

 

 

묵시적 ROLLBACK

오라클에서 보장해주는 원자성은 트랜잭션 레벨 원자성과 문장 레벨 원자성이 있다.

  • 트랜잭션 레벨 원자성: 트랜잭션 단위의 원자성을 보장한다. 트랜잭션의 졸료지점이 COMMIT문 또는 ROLLBACK문에 의해 결정되므로 COMMIT 또는 ROLLBACK이 실행될 때 그 시점까지 실행된 모든 변경이 전부 반영되거나 전부 취소된다.
  • 문장 레벨 원자성: SQL 문장 단위의 원자성을 보장한다. 하나의 DML 또는 DDL 문장이 수행될 때 오류 없이 실행에 성공하는 경우에는 모든 변경이 트랜잭션에 반영되며 도중에 오류가 발생하는 경우는 해당 문장이 수행되기 직전의 상태로 되돌아간다. 이 문장 레벨 원자성은 묵시적 ROLLBACK이라는 메커니즘을 통해 보장된다.

 

SET TRANSACTION

트랜잭션 속성을 설정하는 트랜잭션 제어문 SET TRANSACTION문으로 할 수 있는 것이 몇 가지 있지만 대표적으로 트랜잭션에서 DML을 불가능하게 하는 것을 들 수 있다.

-- READ ONLY 트랜잭션에서 DML을 사용하면 오류 발생
BEGIN
	DELETE FROM emp WHERE empno = 9000;
    COMMIT;
    SET TRANSACTION READ ONLY;	-- DML 금지
    -- 다음 INSERT문은 DML이므로 오류 발생
    INSERT INTO emp(empno, ename, hiredate, sal) VALUES(9000, '허균', SYSDATE, 9000);
END;
/

SET TRANSACTION READ ONLY는 리포트 생성 프로그램과 같이 변경이 없는 프로그램에서 의도치 않은 DML이 실행되는 것을 방지하기 위해 사용될 수 있다. DML을 다시 가능하게 만들려면 SET TRANSACTION READ WRITE를 실행하면 된다.