[DBMS] 트랜잭션 (transaction)

트랜잭션이란

트랜잭션(transcation)이란 일관된 상태의 데이터베이스가 다른 일관된 상태로 넘어가기 위한 논리적인 작업 단위입니다. 트랜잭션이 종료되면 트랜잭션에서 일어난 모든 변경(작업)을 저장하거나 아무것도 저장하지 않는다는 것을 보장합니다. (보장하지 못하는 경우도 있으나 이 경우는 아래에서 설명) 더하여, 데이터 무결성을 지키기 위한 체크 조건과 규칙들이 구현되어있습니다.

이러한 보장이 비로소 파일 시스템이 가진 치명적인 단점을 보완하게 된 것입니다. 파일 시스템은 시스템 운영체제가 갑자기 중단되면 사용하던 파일의 안전을 보장하지 못합니다(훼손 가능성). 연관 파일 2개 이상의 동기화가 늘 유지되어도 갑작스러운 shutdown과 같은 장애 발생 시 그 동기화를 보장하지 못합니다. 그러나 데이터베이스는 트랜잭션이라는 논리적인 하나의 단위로 작업을 진행하고 그 작업을 반영하거나, 안 하거나 둘 중에 하나만을 보장하기 때문에 파일 시스템의 단점을 보완해냅니다.

트랜잭션의 특징

원자성 : 트랜잭션 내의 모든 작업이 반영되거나 반영되지 않아야 함
일관성 : 데이터베이스는 COMMIT을 통해 하나의 일관된 상태에서 다른 일관된 상태로 넘어가야 함
고립성 : COMMIT 시에 다른 트랜잭션이 그 결과를 확인할 수 없어야 함
지속성 : COMMIT 된 트랜잭션의 결과는 영속적이어야 함

트랜잭션 제어 문장

오라클은 묵시적으로 TRANSCATION을 엽니다. (=TX lock을 얻음) SET TRANSCATION 또는 DBMS_TRANSACTION 패키지를 이용하여 시작을 명시할 수도 있습니다. DML의 경우 종료 시에 명시적으로 제어문 COMMIT 또는 ROLLBACK을 선언하도록 합니다. (DDL은 auto commit) 특히, 트랜잭션 종료 시에는 commit or rollback을 명시적으로 선언하여 끝내지 않고 종료할 시에는 auto commit 옵션, 접속 툴 기능 등에 따라 의도와 다르게 트랜잭션이 반영될 수도 있으므로 반드시 명시하여 종료하도록 합니다.

오라클에 한하여 트랜잭션의 원자성(atomic)은 문장단위로 적용됩니다. 따라서 문장 하나가 전적으로 반영되거나 반영되지 않습니다. 트랜잭션이 반영되던 중 문장 하나가 실패해도, 이전 문장들은 보존되며 사용자에게 해당 문장들의 반영 여부 (commit or rollback)를 정하도록 합니다. 일단 원자성을 문장단위로 가진다는 것을 기억하고 이어 나가 봅시다.

트랜잭션 제어문은 4개가 있습니다. 하나씩 살펴보겠습니다.

COMMIT : 트랜잭션 반영
ROLLBACK : 트랜잭션 미반영
SAVEPOINT
ROLLBACK TO <SAVEPOINT>

COMMIT (COMMIT WORK)

트랜잭션을 종료하고, 트랜잭션에서 발생한 변경사항을 영속적으로 반영하는 것입니다. 영속성 개념을 위반하는 비동기 COMMIT도 존재합니다. 비동기 COMMIT에 대해서는 아래에서 설명하겠습니다.

ROLLBACK (ROLLBACK WORK)

트랜잭션을 종료하고, 트랜잭션에서의 변경사항을 언두(UNDO)합니다. 트랜잭션 시작 이전에 정보를 저장 (언두 데이터)해 두고, 그 상태로 되돌림으로써 언두를 수행합니다.

SAVEPOINT

트랜잭션 내의 save point. SAVEPOINT를 명시적으로 사용할 일은 드무나, 내부적으로 오라클은 자주 SAVEPOINT를 사용합니다.

ROLLBACK TO SAVEPOINT

앞에 SAVEPOINT 제어문과 같이 사용됩니다. 해당 SAVEPOINT 이전까지의 작업은 보존하고 ROLLBACK 할 수 있습니다.

SET TRANSACTION

트랜잭션 고립 수준, 속성 등을 설정합니다.

원자성 (atomicity)

문장 수준의 원자성
프로시저 수준의 원자성
트랜잭션 수준의 원자성
DDL 수준의 원자성

문장 수준의 원자성

오라클의 경우 문장 수준의 원자성을 보장하기 위해 특별히 조치할 필요가 없습니다. 내부적으로 SAVEPOINT를 만들어가며 작업하기 때문입니다.

INSERT INTO TB_ONE VALUES (1);
INSERT INTO TB_ONE VALUES (2);
INSERT INTO TB_ONE VALUES (3);

위와 같이 3 row를 연달아 INSERT 하던 중 세 번째 INSERT 문에서 에러가 났습니다. 오라클은 문장 수준의 원자성을 가지기 때문에 두 번째 INSERT까지는 보존을 할 수 있습니다. 내부적으로 아래와 같이 작업하기 때문입니다.

SAVEPOINT ST1;
INSERT INTO TB_ONE VALUES (1);
-- If error then ROLLBACK  TO ST1;
SAVEPOINT ST2;
INSERT INTO TB_ONE VALUES (2);
-- If error then ROLLBACK  TO ST2;
SAVEPOINT ST3;
INSERT INTO TB_ONE VALUES (3);
-- If error then ROLLBACK  TO ST3;

따라서 사용자는 세 번째 INSERT문에서 오류 발생 시 아래와 같이 실행하면 됩니다.

...
-- error!!!

--ROLLBACK  TO ST3 두번째 INSERT까지 보존
COMMIT; -- 두번쨰 INSERT까지는 COMMIT (사용자 선택)

INSERT에 대한 트리거에서 오류가 발생하였더라도 그 트리거를 포함하여 원자성을 보장할 수 있습니다. 위 세 번째 INSERT에는 문제가 없었으나, 세 번째 트리거에서 오류가 발생했다면 역시 세 번째 INSERT 자체가 모두 취소가 되고, ST2로 돌아갈 수 있습니다. INSERT 문장 하나를 원자적으로 만들었기 때문에 이 INSERT에 따른 트리거 실행문 역시 INSERT문의 일부분으로 봅니다.

프로시저 수준의 원자성

CREATE TABLE TB_ONE (X INT CHECK (X > 0)); //TABLE CREATED.

CREATE OR REPLACE  PROCEDURE P_ONE
AS
BEGIN
    INSERT INTO TB_ONE VALUES (1);
    INSERT INTO TB_ONE VALUES (-2);
end;

//PREOCDURE CREATED.

프로시저 P_ONE은 항상 실행 실패할 것입니다. 컬럼의 체크 조건에 부합하지 못하기 때문입니다. 실패하게 되면 첫 번째 INSERT문은 어떻게 될까요. 오라클은 PL/SQL의 익명 블록(anonymous block)을 하나의 원자로 취급하기 때문에 프로시저 전체가 실패하게 됩니다. 저장 프로시저 호출 문 앞 뒤로 SAVEPOINT로 감싸는 것입니다. 그리고 실패하게 되면 프로시저 호출 전 SAVEPOINT로 복구합니다.

프로시저 내부의 명령문 각각에 원자성을 갖도록 하는 방안도 있습니다. 내부에서 COMMIT, ROLLBACK을 명시해주거나, WHEN OTHERS 절을 사용하는 것입니다. 하지만 이에 대해서 토마스 카이트는 아래와 같이 말합니다.

...
PL/SQL에는 일반적으로 커밋과 롤백을 사용해서는 안 된다고 생각한다.
왜냐하면 트랜잭션 완료 시점은 PL/SQL 저장 프로시저를 호출한 사람만 알 수 있기 때문이다.
커밋과 롤백을 포함하도록 P/SQL 루틴을 개발하는 것은 좋지 않은 프로그래밍 습관이다.
...

출처 : "전문가를 위한 오라클 데이터베이스 아키텍처" chapter 08 트랜잭션

트랜잭션 수준의 원자성

거듭 설명하듯 트랜잭션은 작업의 최소 논리적인 단위로서, 트랜잭션의 목적은 데이터베이스를 하나의 일관성에서 다른 일관성으로 상태를 변경시키는 것입니다. 따라서 데이터베이스에서 트랜잭션도 그 자체로 원자적으로 처리되어야 합니다. 트랜잭션도 하나의 문장처럼 데이터베이스를 변경시키거나, 트랜잭션 이전으로 돌리거나 둘 중의 하나입니다. 변경시켰다면 (COMMIT) 그 변경은 반드시 영속적이어야 합니다.

DDL과 원자성

DDL은 조금 특별한 원자성을 지닙니다. 일단 문장을 시작하면 작업의 완료 여부와 상관없이 트랜잭션 무조건 종료합니다. 그리고 그 DDL문이 성공했으면 즉시 COMMIT, 실패했으면 ROLLBACK 합니다. 사용자에게 commit 여부를 묻지 않습니다.

영속성

트랜잭션을 COMMIT 할 때에는 동기적으로 처리되며 트랜잭션 이후의 데이터베이스에 영구적으로 반영됩니다. (영속성) 일반적으로는 그렇지만 영속적이지 못하는 예외가 존재하기도 합니다.

COMMIT WRITE NOWAIT을 사용하여 COMMIT 하는 경우 (비동기 COMMIT, 오라클 10g 릴리즈 2 이상)
비분산 환경에서 PL/SQL 코드 블록 내부에서 COMMIT 하는 경우

COMMIT WRITE NOWAIT

기본적으로 애플리케이션은 COMMIT을 호출하고 나서 완료될 때까지 기다리고 이때 데이터베이스는 트랜잭션에 의해 생성된 리두를 디스크에 쓰고 나서 (물리적 I/O) 애플리케이션에게 처리 결과를 반환합니다.

그러나 오라클 10g 릴리즈 2 이상부터 COMMIT에 대한 동기/비동기 옵션을 줄 수 있도록 하였고 그에 따라 비동기를 사용한다면 COMMIT은 영속적이라고 확신할 수 없습니다.

.... 
(transcation)
...
COMMIT WRITE WAIT; --(동기, 일반적인 경우)
COMMIT  WIRTE NOWAIT; --(비동기, 영속적이지 않을 가능성)

비동기 COMMIT은 아래와 같은 절차를 밟으면 영속성을 확신할 수 없게 됩니다.

애플리케이션은 비동기 COMMIT을 한 뒤 COMMIT이 잘 이루어졌을 것이라고 예상
다음 비즈니스 처리
나중에 실제로 COMMIT이 되지 않았음을 알게 됨
이미 이후의 비즈니스를 진행했기 때문에 늦음

비동기 COMMIT을 하는 환경에서 갑작스러운 shut down에 의해 쉽게 경험할 수 있습니다.

단점만 있는 것은 아닙니다. 비동기 COMMIT은 성능 때문에 만들어졌습니다. 트랜잭션 COMMIT 작업 시 물리적 I/O는 상대적이긴 하나 그 속도가 느립니다. 비동기 COMMIT을 하게 되면 이 물리적인 작업시간을 기다리지 않아도 되기 때문에 COMMIT을 자주 하는 애플리케이션 단에서는 성능이 빠르게 보일 수 있습니다. 이런 방식은 대화식이 아닌 백그라운드 애플리케이션 (혹은 배치 프로그램)에서 유용합니다. 이런 애플리케이션은 사용자 간의 상호작용이 없기 때문에 COMMIT의 여부가 서로에게 영향을 주지 않습니다.

그러나 이와 같은 환경이어도 트랜잭션이 영속적이지 않아야 된다는 것은 아닙니다. 따라서 COMMIT 이후의 영속성을 보장할 수 없다는 것을 인지하고, 언제든 COMMIT이 되지 않았을 수도 있다는 리스크를 안아야 하기 때문에 지양하는 것이 바람직합니다.

비분산 환경 PL/SQL 코드블럭 내부 COMMIT

PL/SQL 프로그램 자체가 배치성을 띄기 때문에 PL/SQL은 항상 비동기 COMMIT을 사용해 왔습니다. 사용자는 프로시저가 완전히 종료될 때까지 COMMIT의 결과를 알지 못합니다. 그러나 데이터베이스가 2개 이상이 연관되어있다면 (분산 환경) 동기 프로토콜을 해야 합니다. 데이터베이스 서로가 COMMIT 이후 영속적이라고 믿어야 하기 때문입니다.

PL/SQL은 마지막 COMMIT 시에는 그 간 발생한 리두들이 모두 디스크에 기록 (물리적 I/O)될 때까지 기다렸다가 제어권을 애플리케이션에 반환합니다. 프로시저 내부에 여러 COMMIT이 있었어도 마지막에 최종적으로 한번 기다리는 것입니다.

CREATE OR REPLACE  PROCEDURE P_ONE
AS
BEGIN
    FOR I IN 1..10
        LOOP
        INSERT INTO TB_ONE VALUES (I);
        COMMIT; --사실은 COMMIT WRITE NOWAIT; (비동기)
    END LOOP;
    
    --REDO가 다 쓰일때까지 기다림
END;

트랜잭션에서 무결성 제약조건 검사

트랜잭션 반영 시 무결성 제약조건 검사시기는 일반적으로 SQL 문 (SQL statement) 처리한 직후입니다.

IMMEDIATE 제약조건 (default)

기본 모드 (default)입니다. SQL 문이 처리된 직후 바로 무결성 제약조건을 검사합니다. 따라서 많은 SQL 문이 포함된 PL/SQL의 경우 각각의 매 SQL문마다 제약조건을 검사 (프로시저 종료 직후 X)하게 됩니다. row마다 검사하지 않고, SQL 문이 처리된 직후 검사하는 이유는 간단합니다.

INSERT INTO TB_ONE VALUES (CL_X) (1);
INSERT INTO TB_ONE VALUES (CL_X) (2);

COMMIT;

UPDATE TB_ONE SET CL_X = CL_X + 1;

테이블 TB_ONE의 컬럼 CL_X에 UNIQUE 제약조건이 있다고 가정해봅시다. 만일 row마다 제약조건 검사를 한다면 위 UPDATE문은 실패할 것입니다. 두 번째 ROW를 UPDATE 할 때 UNIQUE 제약조건 위배이기 때문입니다. 하지만 오라클은 SQL 문이 완료된 시점에서 최종적으로 제약조건을 검사하기 때문에 위와 같은 UDPATE문이 성공할 수 있게 됩니다.

DEFERRABLE 제약조건, CASACADE

일반적으로는 SQL 처리 직후 검사하지만, 오라클 8.0부터 옵션을 사용하여 검사시기를 뒤로 연기할 수 있습니다. 미리 말하자면 꼭 필요할 때만 사용(지양)하셔야 합니다. SQL문 실행계획 자체에 영향을 주기 때문입니다.

그럼에도 이러한 기능이 필요해진 이유는 기본키에 대한 CASCADE UPDATE 작업을 할 때입니다.

* 기본키를 UPDATE 하는 일은 없어야 합니다. (토마스 카이트는 이를 아주 나쁜 습관으로 간주) UPDATE가 필요한 기본키는 기본키의 본질을 훼손하는 것이며, 애플리케이션 단에서 기본키를 지속적으로 UPDATE 한다면, 그 설계가 적합한지 다시 고려해볼 필요가 있습니다.

CREATE TABLE PARENT
(
    PARENT_NO INT PRIMARY KEY
)

CREATE TABLE CHILD
(
    PARENT_NO INT CONSTRAINT FK_CHILD_PARENT_NO
            REFERENCES PARENT (PARENT_NO)
    DEFERRABLE          --제약조건 검사 시점을 연기 가능
    INITIALLY IMMEDIATE -- 기본적으로 SQL문 완료 시 제약조건 검사
)


INSERT INTO PARENT (PARENT_NO) VALUES (1);
INSERT INTO CHILD (PARENT_NO) VALUES (1);

테이블 PARENT, CHILD를 만들고, 둘은 PARENT_NO 컬럼으로 참조 중입니다. DEFERRABLE 제약을 이용하여 이 테이블에 대한 제약조건 검사를 연기할 수 있음을 명시했습니다. 이제 아래와 같이 실행해보겠습니다.

UPDATE PARENT SET PARENT_NO = 2;-- ERROR!! ORA-02292

당연히 에러가 납니다. 기본적으로 IMMEDIATE 모드이기 때문입니다.

CREATE 시 사용한 DEFERRABLE을 이용하여 보겠습니다.

SET CONSTRAINT FK_CHILD_PARENT_NO DEFERRED; --DEFERRED 모드 전환

UPDATE PARENT SET PARENT_NO = 2; -- UPDATE 성공!

--IMMEDIATE 모드로 전환
SET CONSTRAINT  FK_CHILD_PARENT_NO IMMEDIATE;
-- 전환 실패
-- ERROR! ORA-02291

-- CHILD도 마찬가지로 UDPATE
UPDATE CHILD SET PARENT_NO = 2; --UPDATE 성공!

--IMMEDIATE 모드로 전환
SET CONSTRAINT  FK_CHILD_PARENT_NO IMMEDIATE; 
-- 전환 성공

COMMIT; -- 트랜잭션 반영

성공적으로 UDPATE를 했습니다. 제약조건 검사를 미룬 덕택입니다. 이런 경우에는 효과적으로 사용이 가능합니다. 이 제약조건은 미리 지정되어있어야 하고, 추후 필요하다면 제약조건을 DROP 하고 다시 만들어서 사용해야 합니다.

그렇다고 해서 외래 키 컬럼을 만들 때마다 DEFERRABLE 조건을 주어서는 안 됩니다. 필요할 때만 사용하여야 합니다. 실행계획에 큰 영향을 주기 때문입니다. 예를 들어 DEFERRABLE 조건과 함께 UNIQUE 제약조건을 준다면, 내부적으로 이 컬럼에 대해서는 NON-UNIQUE INDEX를 생성합니다. 상황에 따라 제약조건을 무시하는 값 (중복 값)이 들어올 수 있기 때문입니다. 그리고 SELECT 시 UNIQUE 제약조건이 있음에도 INDEX FULL SCAN을 하지 않습니다. INDEX의 SELECT 성능 향상을 전혀 누릴 수 없습니다.

나쁜 트랜잭션 습관

트랜잭션을 설계할 때 주의해야 할 점들은 아래와 같습니다.

업무 단위에 따라 적절한 시점에서 COMMIT 할 것
즉, 트랜잭션 안에서 COMMIT을 빈번하게 하지 말 것
업무 요구사항에 따라 충분히 길게 트랜잭션을 가져가도 됨
트랜잭션의 크기는 데이터 무결성 (비즈니스 규칙)이 좌우함. (트랜잭션의 DML 양이 좌우하지 않음)
트랜잭션은 데이터를 보호하기 위해 존재 (고립 수준을 유지하여 일관된 뷰를 제공)
SQL로 처리 가능하면 SQL로 할 것. SQL로 처리가 안될 때 PL / SQL을 고려해볼 것

트랜잭션을 짧게 가져가려는 은연 중의 압박을 벗으라는 것입니다. 견고한 트랜잭션을 설계하여 하나의 일관된 데이터베이스를 만들 수 있는 트랜잭션을 수행했을 때 비로소 종료 (COMMIT or ROLLBACK)하여 데이터베이스의 상태를 변화시키면 되는 것입니다.

위를 어기고 개발자들이 흔히 하는 실수 2가지를 소개하겠습니다.

루프에서 커밋

많은 ROW를 UPDATE 하려 할 때 ROW마다 COMMIT 하려 (혹은 자주 COMMIT) 합니다. 이는 언두 공간이 부족할 거라는 걱정이나, 건건이 COMMIT 하는 것이 업무의 효율성 (성능, 속도)를 증대할 수 있을 거라는 기대를 가지는 것인데요. 사실은 그렇지 않습니다.

자주 COMMIT 한다고 해서 속도가 빨라질 것이라고 기대하지 말고, 단일 SQL문으로 처리하세요. 단일 SQL문으로 작업하는 것이 더 효율적인 방법이기 때문에 단일 SQL문으로 처리하도록 하는 것이 낫습니다.

또한, 작업 중 오류가 발생하면 재시작할 수 없을 수도 있습니다. 단일 SQL문은 성공하거나, 실패하거나 둘 중의 하나이기 때문에 실패 시에 다시 실행할 수 있는 여지가 있지만, 루프에서 ROW마다 COMMIT 하다가 오류가 발생하게 되면 이미 COMMIT 된 ROW와 아닌 ROW를 선별하는 작업이 필요해지고, 이게 불가능한 상황이라면 실행하려던 작업은 미지의 세계에 들어가게 됩니다.

-- commit in loop
BEGIN
    FOR X IN (SELECT ROWID RID, ROWNUM R,  MOBILE_PHONE 
                FROM TB_ONE 
                WHERE GENDER = 'M')
    
    LOOP
        UPDATE TB_ONE SET MOBILE_PHONE = REPLACE(X.MOBILE_PHONE, '-', '') WHERE X.ROWID = X.RID;
            IF (MOD(X.R, 50) = 0 )  --50건마다 COMMIT
                THEN COMMIT;
            END IF;
    END LOOP;

    COMMIT;
END
    
-- one SQL statement
UPDATE TB_ONE SET MOBILE_PHONE = REPLACE(MOBILE_PHONE, '-', '') WHERE GENDER = 'M';
COMMIT;

COMMIT 함으로써 기존에 만든 언두 데이터는 다른 세션 (트랜잭션)에 의해 언제든 덮어씌워진 수 있는 위험에 노출됩니다. 이는 읽기 일관성을 해치며, 이때 ORA-01555: snapshot too old 오류가 발생합니다. 루프에서 COMMIT 하는 것이 이 오류의 원인 중 대부분입니다. COMMIT 하는 순간 내가 만든 언두 데이터는 언제든지 덮어 씌어줘도 되는 상태가 됩니다. 즉 다른 트랜잭션이 나의 언두 세그먼트를 덮어 씌어버릴 수 있습니다. 이는 별도로 ORA-01555: snapshot too old 오류와 그 시나리오, 해결책 등에 대해서 포스팅하도록 하겠습니다. 핵심은 다음에 필요로 하는 나의 언두 데이터를 누군가 지울 수도 있다는 것입니다.

자동 커밋 사용

DB 접속 프로그램 설정이나 DBMS 설정에 따라 또는 API ODBC, JDBC를 사용할 때 자주 보게 되는 설정입니다. 특히 웹 개발자들이 사용하는 JDBC 드라이버 등에서는 autocommit이 기본값입니다. 아래와 같이 애플리케이션단에서 단일 메서드 안에서 재고 데이터를 1개를 빼고 1개를 증가하는 작업을 가져간다고 했을 때

UPDATE TB_ONE SET STOCK_TOTAL = STOCK_TOTAL - 1 WHERE PRODUCT_ID = 123;
UPDATE TB_ONE SET STOCK_TOTAL = STOCK_TOTAL + 1 WHERE PRODUCT_ID = 123;

두 번째 SQL 문에서 오류가 발생하면 첫 번째만 commit 된 이후 실행이 멈추고, 최종적으로 의도와 다르게 재고는 -1로 남게 됩니다. autocommit을 해제하고, 작업 중 오류 (예외)가 발생하면 트랜잭션 전체를 ROLLBACK 하게 만드는 것이 더 바람직합니다.

...


Connection conn = null;

PreparedStatement pstmt1 = null;
PreparedStatement pstmt2 = null;

DBCon db = new DBCon();

try {

    conn = db.connect();

    conn.setAutoCommit(false); //autoCommit 사용 안함

    String sql1 = "UPDATE TB_ONE SET STOCK_TOTAL = STOCK_TOTAL - 1 WHERE PRODUCT_ID = 123";
    String sql2 = "UPDATE TB_ONE SET STOCK_TOTAL = STOCK_TOTAL + 1 WHERE PRODUCT_ID = 123";


    pstmt1 = conn.prepareStatement(sql1);
    pstmt1.setInt(1, a);
    pstmt1.executeUpdate();


    pstmt2 = conn.prepareStatement(sql2);
    pstmt2.setInt(1, a);
    pstmt2.executeUpdate();

    conn.commit(); //최종적으로 COMMIT;

} catch (SQLException se) {

    conn.rollback(); //예외 발생시 트랜잭션 전체 rollback;
    se.printStackTrace();

} catch (Exception e) {

    e.printStackTrace();
} finally {

    db.close(pstmt1, conn);
    db.close(pstmt2, conn);

}

...

분산 트랜잭션

오라클의 경우 분산 트랜잭션 (distributed transcation)을 제공합니다. 하나의 트랜잭션에서 1개 이상의 데이터베이스 (인스턴스)에 대해서 작업을 진행할 수 있습니다. 이는 데이터베이스 링크를 통해 가능합니다.

데이터베이스 링크 (database link) : 다른 인스턴스로 로그인할 수 있는 데이터베이스 객체

SELECT *
FROM TB_OTHER@ANOTHER_DATABASE;

UPDATE TB_LOCAL SET STOCK_TOTAL = 1; -- LOCAL DB UPDATE

UPDATE TB_OTHER@ANOTHER_DATABASE SET STOCK_TOTAL = 1; -- OTHER DB UPDATE by databaselink

CREATE SYNONYM TB_OTHER FOR TB_OTHER@ANOTHER_DATABASE; --SYNONYM 선언
UPDATE TB_OTHER SET STOCK_TOTAL = 1; -- SYNONYM 을 이용한 UPDATE

COMMIT;

보통 SYNONYM을 이용하여 다른 DB의 테이블을 숨기기도 합니다. 다만, SYNONYM의 남용은 SQL 코드들이 복잡해지고, 설계자가 아닌 사람이 레거시에 대하여 분석할 때 여러 가지 어려움을 주기도 하니, 적절한 정책 (네이밍 등)을 세워 사용하기 바랍니다.

위 코드에서 마지막에 COMMIT을 하게되면 2개의 데이터베이스에 COMMIT이 됩니다. 분산 트랜잭션에서 특정 데이터베이스에만 COMMIT을 하는 것은 불가합니다. 그리고 트랜잭션 종료 시도 시에 하나의 데이터베이스라도 종료가 불가한 상황이면 해당 트랜잭션은 종료될 수 없습니다. 그 외에도 몇 가지 제한사항이 있습니다.

데이터베이스 링크를 통해 commit을 전달할 수 없음 (특정 데이터베이스에 대하여 commit 불가)
링크를 통해 DDL, 트랜잭션 제어문 전송 불가

트랜잭션에 대한 제어는 링크를 오픈한 트랜잭션이 가지게 됩니다. 따라서 트랜잭션에서 오픈한 데이터베이스 링크를 통해 다른 데이터베이스에 대한 별도 트랜잭션 제어는 할 수 없습니다.

자율 트랜잭션

트랜잭션(부모) 안에 트랜잭션 (자식)을 생성함으로서 부모 트랜잭션으로부터 독립적으로 COMMIT 하거나 ROLLBACK 할 수 있습니다. PL / SQL에서 로깅을 위한 로직을 넣을 때 유용하게 쓰일 수 있는데, 책에서는 이 용도 외에는 추천하지 않는 것이 바로 자율 트랜잭션 (autonomous transcation)입니다.

프로시저 하나를 선언해보겠습니다.

CREATE OR REPLACE PROCEDURE AUTONOMOUS_INSERT
AS
    PRAGMA AUTONOMOUS_TRANSCATION; -- 자율 트랜잭션 선언

BEGIN
    INSERT INTO TB_ONE VALUES ('AUTONOMOUS');
    COMMIT;
END;

프로시저 AUTONOMOUS_INSERT 는 자율 트랜잭션으로 처리합니다. 위 PRAGMA 선언부가 자율 트랜잭션이며, 이 뜻은 프로시저를 실행할 때 부모 트랜잭션으로부터 독립적인 새로운 트랜잭션에서 수행하도록 하겠다는 것입니다. 이제 실행해보겠습니다.

BEGIN

INSERT INTO TB_ONE VALUES ('Anonymous Block');
AUTONOMOUS_INSERT;

ROLLBACK;

END;

결과로 'AUTONOMOUS' VALUE는 TB_ONE에 INSRET 되었습니다. 마지막의 ROLLBACK 여부와 상관없이 프로시저를 자율 트랜잭션으로 처리했기 때문입니다.

오라클은 자율 트랜잭션을 recursive SQL로 간주합니다. 트랜잭션에서 시퀀스 객체를 조회하면, 값이 하나 늘어납니다. 이 때 ROLLBACK과 상관없이 트랜잭션의 값은 +1 상태로 남게 됩니다. 시퀀스 값을 증가시키기 위해 recursive 트랜잭션으로 수행되었기 때문입니다.

자율 트랜잭션은 PL / SQL에서 로깅 로직을 넣을때만이 거의 유일하게 유용하다고 했습니다. 오라클은 프로시저 수준에서도 원자적이어서 프로시저가 실패하면 프로시저 전체가 ROLLBACK 되기 때문에 어디까지 실행되었는지 알 수가 없습니다. 그 안에서 로깅 로직을 넣더라도 로깅까지 다 ROLLBACK 됩니다. 그러나 로깅 로직만을 자율 트랜잭션으로 선언하여 놓는다면, 프로시저 수준의 원자성에도 불구하고 로깅은 COMMIT 되어 나중에 확인할 수 있게 됩니다.

--로깅 프로시저
CREATE OR REPLACE PROCEDURE ERR_LOG(ERR_POINT IN NUMBER)
AS
    PRAGMA AUTONOMOUS_TRANSCATION; -- 자율 트랜잭션 선언

BEGIN
    INSERT INTO TB_ERR_LOG (ERR_POINT_NUM, DT_ERR) VALUES (ERR_POINT, SYSDATE);
    COMMIT;
END;


--실행 프로시저
CREATE OR REPLACE PROCEDURE PL_WITH_ERR_LOG
AS
BEGIN
    INSERT INTO TB_ONE VALUES (1);
    INSERT INTO TB_ONE VALUES (2);
    INSERT INTO TB_ONE VALUES (2); --ORA-00001: unique constraint violated
    COMMIT;

EXCEPTION
    WHEN OTHERS
        THEN
            ERR_LOG(1); --에러 로그 삽입
            RAISE;
END;

참고

전문가를 위한 오라클 데이터베이스 아키텍처 (저 : 토마스 카이트)

http://www.yes24.com/Product/Goods/5239961

전문가를 위한 오라클 데이터베이스 아키텍처 - YES24

오라클의 대부로 불리는 토마스 카이트는 오라클 데이터베이스와 아키텍처에 관한 모든 것을 이 책에 담고 있다. 이 책은 독자가 ODBC를 이용하는 Visual Basic 프로그래머이거나, EJB와 JDBC를 이용하

www.yes24.com

http://wiki.hash.kr/index.php/%ED%8A%B8%EB%9E%9C%EC%9E%AD%EC%85%98

트랜잭션 - 해시넷

트랜잭션(transaction)이란 "쪼갤 수 없는 업무 처리의 최소 단위"를 말한다. 거래내역이라고도 한다. '트렌젝션'이 아니라 '트랜잭션'이 올바른 표기법이다. 영어로 간략히 Tx라고 표기하기도 한다.

wiki.hash.kr

https://stackoverflow.com/questions/56206641/why-dml-commands-executed-before-and-after-a-ddl-command-are-auto-committed

Why DML commands executed before and after a DDL command are auto committed?

I know that DDL commands are auto committed but why are DML commands? This is what I did: inserted data into an already existing table called table1. created a table called table2. inserted some ...

stackoverflow.com

저작자표시 비영리 변경금지

'Programming > Database System' 카테고리의 다른 글

[ORACLE] Lock 1편 - Lock의 개념과 매커니즘 (0)	2022.10.12
DISTINCT, GROUP BY 무엇을 쓸까 (1)	2022.09.26
[RDBMS] Transaction Isolation Level (트랜잭션 고립 수준) (0)	2022.06.24
정규화 (0)	2022.05.02
SQL (2) - 데이터 검색, SELECT (0)	2022.04.04

kghworks

[DBMS] 트랜잭션 (transaction)

목차

트랜잭션이란

트랜잭션의 특징

트랜잭션 제어 문장

COMMIT (COMMIT WORK)

ROLLBACK (ROLLBACK WORK)

SAVEPOINT

ROLLBACK TO SAVEPOINT

SET TRANSACTION

원자성 (atomicity)

문장 수준의 원자성

프로시저 수준의 원자성

트랜잭션 수준의 원자성

DDL과 원자성

영속성

COMMIT WRITE NOWAIT

비분산 환경 PL/SQL 코드블럭 내부 COMMIT

트랜잭션에서 무결성 제약조건 검사

IMMEDIATE 제약조건 (default)

DEFERRABLE 제약조건, CASACADE

나쁜 트랜잭션 습관

루프에서 커밋

자동 커밋 사용

분산 트랜잭션

자율 트랜잭션

참고

'Programming > Database System' 카테고리의 다른 글

댓글

티스토리툴바

[DBMS] 트랜잭션 (transaction)

목차

트랜잭션이란

트랜잭션의 특징

트랜잭션 제어 문장

COMMIT (COMMIT WORK)

ROLLBACK (ROLLBACK WORK)

SAVEPOINT

ROLLBACK TO SAVEPOINT

SET TRANSACTION

원자성 (atomicity)

문장 수준의 원자성

프로시저 수준의 원자성

트랜잭션 수준의 원자성

DDL과 원자성

영속성

COMMIT WRITE NOWAIT

비분산 환경 PL/SQL 코드블럭 내부 COMMIT

트랜잭션에서 무결성 제약조건 검사

IMMEDIATE 제약조건 (default)

DEFERRABLE 제약조건, CASACADE

나쁜 트랜잭션 습관

루프에서 커밋

자동 커밋 사용

분산 트랜잭션

자율 트랜잭션

참고

'Programming > Database System' 카테고리의 다른 글

관련글

댓글

티스토리툴바