[Oracle] DBMS_JOB 실행 중 row lock contention 발생

1. 한줄 요약

DBMS_JOB 패키지 실행시 내부적으로 DML이 수행되니, COMMIT 또는 ROLLBACK 필수

 

2. 문제상황 

1) DB버전 : 11g인 (9i에서 마이그레이션된 DB) LEGACY 시스템에서 오라클 DB JOB을 통해 매일 실행되는 로직 존재

2) 개선을 위해 DBMS_JOB 패키지를 활용해 JOB을 REMOVE 하고 다른 로직을 적용하기로 함.

EXECUTE SYS.DBMS_JOB.REMOVE(JOB 아이디);

3) 실행 후 다른 세션으로 JOB을 확인했더니 (ALL_JOBS 또는 DBA_JOBS에서 확인) JOB이 존재.

4) REMOVE, BROKEN을 시도했더니 DBMS_JOB.REMOVE가 끝나지 않음 (무한 실행)

 

 

3. 문제원인파악 / 개선

1) 무한 실행되는 상황에서 Active Session History를 확인하니 enq: TX - row lock contention 발생

2) row lock contention 발생 SQL : SYS.JOB$를 SELECT하는 SQL

3) 오라클 내부에서 JOB을 관리하는 DML ROW LOCK이 발생한 것으로 추정

4) 구글링 통해 DBMS_JOB 패키지 실행시 DML이 수행된다는 내용 확인.

    오라클 DOCS에서 COMMIT이 필수라는 내용 확인.

    (You must issue a COMMIT statement immediately after the statement.)

5) COMMIT / ROLLBACK 후, DBMS_JOB 패키지가 정상적으로 반영되는 것 확인

EXECUTE DBMS_JOB.REMOVE(JOB 아이디);
COMMIT;
-- COMMIT 또는 ROLLBACK 필수

 

 

4. 얻어갈 것

1) 기본에 충실 : Active Session History를 통한 Event 확인이 문제해결의 단서

2) 다시 또 기본 : 오라클 패키지, 프로시저의 경우 공식 DOCS를 확인

3) 기타 : JOB 변경시 OWNER에서는 DBMS_JOB 패키지를 이용하고, OWNER가 아닌 계정에서는 DBMS_IJOB 사용

 EXECUTE DBMS_IJOB.REMOVE(JOB 아이디);
 COMMIT;

 

 

* 참고자료 : 

https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_job.htm#i1000622

DBMS_JOB