deadlock 발생 테스트 ORA-00060

OS환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 11.2.0.4

에러 : ORA-00060: deadlock detected while waiting for resource

해결 방법 : 오라클이 자동으로 한세션 트랜젝션을 중지시킴

원인 : 교착상태

테스트 : deadlock 발생 테스트

#세션 1
create table emp2 as select * from emp;
create table emp3 as select * from emp;
update emp2 set empno = 9999;
 
14 rows updated.
 
#세션 2
update emp3 set empno = 9999;
 
14 rows updated.
 
#세션 1
update emp3 set empno =9999;
#lock 걸려서 멈춰있음
 
#세션 2
update emp2 set empno = 9999;
#lock 걸려서 멈춰있음
 
#세션1
#몇초 뒤 확인하면(싱글db에선 바로) ORA-00060 발생
update emp3 set empno =9999
       *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

alert log(RAC)

1 2 3

Thu Dec 06 09:39:43 2018 Global Enqueue Services Deadlock detected. More info in file  /orcl/oracle/app/oracle/diag/rdbms/orcldb/ORCLDB1/trace/ORCLDB1_lmd0_27422.trc.

alert log(Single)

1 2	Thu Dec  6 09:43:36 2018 ORA-00060: Deadlock detected. More info in file /oracle/app/oracle/admin/ORCL/udump/orcl_ora_14911.trc.

추가 결과

#세션1
select * from emp2;
 
     EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      9999 KING       PRESIDENT (null)       17-NOV-81       5000 (null)           10
      9999 BLAKE      MANAGER          7839 01-MAY-81       2850 (null)           30
      9999 CLARK      MANAGER          7839 09-JUN-81       2450 (null)           10
      9999 JONES      MANAGER          7839 02-APR-81       2975 (null)           20
      9999 SCOTT      ANALYST          7566 19-APR-87       3000 (null)           20
      9999 FORD       ANALYST          7566 03-DEC-81       3000 (null)           20
      9999 SMITH      CLERK          7902 17-DEC-80        800 (null)           20
      9999 ALLEN      SALESMAN          7698 20-FEB-81       1600        300       30
      9999 WARD       SALESMAN          7698 22-FEB-81       1250        500       30
      9999 MARTIN     SALESMAN          7698 28-SEP-81       1250       1400       30
      9999 TURNER     SALESMAN          7698 08-SEP-81       1500      0       30
      9999 ADAMS      CLERK          7788 23-MAY-87       1100 (null)           20
      9999 JAMES      CLERK          7698 03-DEC-81        950 (null)           30
      9999 MILLER     CLERK          7782 23-JAN-82       1300 (null)           10
 
14 rows selected.
 
#세션2
#계속 lock 걸려서 멈춰있음

결론 : 세션 1, 세션2 모두 세션이 kill 되진 않고 

교착상태만 풀림 

각자 먼저 실행했던 dml은 그대로 가지고 있음

참조 :