MERGE 문의 동시성

MERGE 문은 WHEN 절의 기술 순서와 무관하게 UPDATE, INSERT 순서로 동작한다. 이때 ON 절은 UPDATE 문의 WHERE 절과 유사하게 동작한다. 이로 인해 예상치 못한 에러가 발생할 수 있다.

테스트를 위해 아래의 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 VARCHAR2(1), c2 NUMBER);
ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (c1);

S1 세션에서 아래의 MERGE 문을 수행하자. 최초 건은 INSERT 문을 수행하고, 이후 동일 건에 대해 UPDATE 문을 수행하려는 의도로 작성된 MERGE 문이다.

-- 2: S1
MERGE
 INTO t1
USING DUAL
   ON (c1 = 'A')
 WHEN MATCHED THEN
      UPDATE SET c2 = c2 + 1
 WHEN NOT MATCHED THEN
      INSERT VALUES ('A', 1);

1 행이 병합되었습니다.

S2 세션에서 동일한 MERGE 문을 수행하면 블로킹이 발생한다.

-- 3: S2
MERGE
 INTO t1
USING DUAL
   ON (c1 = 'A')
 WHEN MATCHED THEN
      UPDATE SET c2 = c2 + 1
 WHEN NOT MATCHED THEN
      INSERT VALUES ('A', 1);

-- 블로킹

S1 세션에서 커밋을 수행하자.

-- 4: S1
COMMIT;

커밋이 완료되었습니다.

S2 세션은 UPDATE 문이 수행되지 않고 ORA-00001 에러가 발생한다.

-- 5: S2
ORA-00001: 무결성 제약 조건(SCOTT.T1_PK)에 위배됩니다

예제의 MERGE 문은 아래 코드와 유사하게 동작한다. UPDATE 문의 WHERE 절은 consistent read로 동작한다. S2 세션의 MERGE 문은 UPDATE 대상이 없어 INSERT 문이 수행했지만, S1 세션이 동일 PK로 INSERT 문이 수행하고 있어 블로킹이 발생한 것이다.

-- 6
BEGIN
    UPDATE t1 SET c2 = c2 + 1 WHERE c1 = 'A';

    IF SQL%ROWCOUNT = 0 THEN
        INSERT INTO t1 VALUES ('A', 1);
    END IF;
END;

의도대로 구문을 동작시키려면 아래 코드를 사용해야 한다. S1 세션에서 아래의 코드를 수행하자.

-- 7: S1
BEGIN
    INSERT INTO t1 VALUES ('B', 1);
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        UPDATE t1 SET c2 = c2 + 1 WHERE c1 = 'B';
END;
/

PL/SQL 처리가 정상적으로 완료되었습니다.

S1 세션에서 동일한 코드를 수행하면 블로킹이 발생한다.

-- 8: S2
BEGIN
    INSERT INTO t1 VALUES ('B', 1);
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        UPDATE t1 SET c2 = c2 + 1 WHERE c1 = 'B';
END;
/

-- 블로킹

S1 세션에서 커밋을 수행하자.

-- 9: S1
COMMIT;

커밋이 완료되었습니다.

S2 세션은 에러가 발생하지 않고 UPDATE 문이 수행된다. 에러는 발생하지 않지만 UPDATE 문으로 인해 동시성 저하는 여전히 발생할 수 있다는 점에 주의해야 한다.

-- 10: S2

PL/SQL 처리가 정상적으로 완료되었습니다.

SELECT * FROM t1;

C1 C2
-- --
A   1
B   2

2 행이 선택되었습니다.