MERGE INTO(UPSERT) 에서도 PK violation이 발생할 수 있다.

상황

• 요구사항 1) tbl에 없는 경우 INSERT. 있다면 에러 없이 스킵 처리
• 요구사항 2) 동시에 여러 스레드에서 쿼리 실행해야 함

MERGE INTO tbl
   USING dual
   ON (pk1 = '1')
   WHEN NOT MATCHED THEN
   INSERT (pk1) VALUES ('1')

; SQL []; ORA-00001: 무결성 제약 조건(TBL.IPK\_PK1)에 위배됩니다
   ; nested exception is java.sql.SQLIntegrityConstraintViolationException: ORA-00001: 무결성 제약 조건(TBL.IPK\_PK1)에 위배됩니다

• tbl에 없는 경우 INSERT임에도, PK violation이 발생했음.
  • (만약 constraint가 없었다면 같은 pk1=1 이 중복해서 저장되었을 것임)
• ThreadPool count 1로 주고 실행하면 dup key 에러 없이 실행됨.

원인 : race condition

• https://stackoverflow.com/questions/9871644/is-merge-an-atomic-statement-in-sql2008
• https://hrjeong.tistory.com/377
  • MERGE 구문은 ON 절에서 read lock, INSERT/UPDATE 에서 update lock을 건다
  • 즉, MERGE 구문은 all or nothing의 의미로 atomic하긴 하지만, 독점적 lock을 획득하고 중간에 끼어드는 것 없이 작업을 완수한다는 의미에서는 atomic하지 않다.
• 더 상세한 내용https://weblogs.sqlteam.com/dang/2009/01/31/upsert-race-condition-with-merge/

이러한 속성 때문에 MERGE INTO를 tbl에 없는 경우 INSERT, 있다면 에러 없이 스킵 처리에 사용하는 것은 적절하지 않을 수 있다.

해결방안

• 앱단에서 ORA-00001 catch 해서 dupkey 무시
• BEGIN… EXCEPTION… END; 구문 사용해서 쿼리 단에서 dupkey 무시
• ignore_row_on_dupkey_index 힌트 사용해서 dupkey 무시
  • 이 힌트는 특정한 경우 오라클 11g <> 19c 동작이 다르기 때문에 주의

비슷한 이슈

• [Transaction] lost update problem (isolation level, deadlock, update lock)