9i 부터 제공되는 Merge 문 사용시 PK Violation 발생에 대한 분석

참고 하세요

스터디 + 제가 겪은거....

merge 문 사용시 왜 violation 이 발생하는지... 누가 묻는다면... 멋지게 답해주세요

전... 대답 못했어요.. 저도 단순하게 생각해서 오라클 버그인줄 알았음

( 지난주 발표 내용 - 대상은 consistent mode 로 가져오고, 작업(DML) 시에는 Current Mode 로 )

-- MERGE TEST 환경 구성
-- Source Table
 drop table merge_target ;
 create table merge_target
 ( a1 number ,a2 number, a3 number, a4 varchar2(100) ) ;
 alter table merge_target add constraint XPKMERGE_TARGET primary key ( a1, a2 ) ;

-- Source Table Insert 
 begin
 for i in 1 .. 100000 loop
 insert into merge_target values ( i, i, 100000, 'SOURCE');
 end loop ;
 end ;
 /
 commit ;

-- Target Table 
 drop table merge_source ;
 create table merge_source
 ( a1 number ,a2 number, a3 number, a4 varchar2(100) ) ;
 alter table merge_source add constraint XPKmerge_source primary key ( a1, a2) ;

-- Target Table Insert
 begin
 for i in 1 .. 200000 loop
 insert into merge_source values ( i, i, 200000, 'TARGET');
 end loop ;
 end ;
 /
commit ;

-- MERGE Error Table

SQL> exec dbms_errlog.CREATE_ERROR_LOG ('MERGE_TARGET','MERGE_TARGET_ERROR') ;
PL/SQL procedure successfully completed.


-- TEST
가)
Session 1 ) Insert 작업 ( Commit 하지 않는다 )
SQL>  insert into merge_target values (100100,100100,300000,'Merge Error Check');
1 row created.

나)
Session 2 )
SQL> merge into merge_target t
  2  using merge_source s
  3  on ( t.a1 = s.a1 and t.a2 = s.a2 )
  4  when matched then
  5       update set t.a3 = s.a3, t.a4= s.a4
  6  when not matched then
  7        insert  values ( s.a1, s.a2, s.a3, s.a4 )
  8  log errors into MERGE_TARGET_ERROR  reject limit 1000 ;

다)
Session 1 )
SQL> commit ;
Commit complete.
 
라) 
Session 2 )
SQL> merge into merge_target t
  2  using merge_source s
  3  on ( t.a1 = s.a1 and t.a2 = s.a2 )
  4  when matched then
  5       update set t.a3 = s.a3, t.a4= s.a4
  6  when not matched then
  7        insert  values ( s.a1, s.a2, s.a3, s.a4 )
  8  log errors into MERGE_TARGET_ERROR  reject limit 1000 ;
199999 rows merged.
==> 중복이 나는 한 Row 만 Insert 가 되지 않고,
    나머지 데이타는 Insert 된다.
    에러 내용 조회
SQL>  select ORA_ERR_NUMBER$, ORA_ERR_MESG$  from MERGE_TARGET_ERROR ;

ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
              1
ORA-00001: unique constraint (OPS$ORACLE.XPKMERGE_TARGET) violated       

SQL> merge into merge_target t
  2  using merge_source s
  3  on ( t.a1 = s.a1 and t.a2 = s.a2 )
  4  when matched then
  5       update set t.a3 = s.a3, t.a4= s.a4
  6  when not matched then
  7        insert  values ( s.a1, s.a2, s.a3, s.a4 );
merge into merge_target t
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$ORACLE.XPKMERGE_TARGET) violated
==> MERGE_TARGET_ERROR 테이블을 생성하지 않고, Log Error 절을 명시 하지 않으면,
    모든 작업이 Rollback 된다.
   
결론 : 오라클 성능 고도화 책에서 언급된것 처럼
       DML(Merge 포함하여), 대상을 가져올때는 Consistent Read 모드로 대상을 가져오고,
       실제 Action 시에는 Current Mode 로 작업을 하기 때문에 이와 같은 오류가 발생한다.
       책 내용을 잘 보여주는 예라고 할수 있습니다.
       Merge나 Lock 에 대해서 잘 몰라서, 오라클 오류 인줄 알았네요 ^^;