[oracle] deadlock 테스트 ORA-00060

 

- deadlock 발생 시 오라클은 마지막 deadlock 발생 명령문만 롤백함(교착상태만 풀림)
  트랜잭션이 종료된 것이 아니기 때문에 commit/rollback을 진행해줘야 함
- insert, update, delete 모든 상황에서 발생 가능
- deadlock 을 예방하기 위해서는 insert, update, delete 대상이 되는 테이블들의 작업순서를 동일하게 해 주는 게 좋음
  모듈 1은 tab1 -> tab2로, 모듈 2는 tab2 -> tab1 이런 식으로 하면 deadlock 발생 가능성 있음

 

테스트 테이블 생성

SQL>  create table sk.tab1(c1 number, c2 number);
Table created.

SQL> insert into sk.tab1 values(1 , 1);
1 row created.

SQL> insert into sk.tab1 values(2 , 2);
1 row created.

SQL> commit ;

 

1개 테이블 deadlock 발생 테스트

한개의 테이블에서 deadlock 발생 테스트

순서	세션1	세션2	설명
1	SQL> update sk.tab1 set c2 = 2 where c1 = 1; 1 row updated.
2		SQL> update sk.tab1 set c2 = 2 where c1 = 2; 1 row updated.
3	SQL> update sk.tab1 set c2 = 2 where c1 = 2; -- lock wait		세션2의 2번작업 완료를 기다림
4		SQL> update sk.tab1 set c2 = 2 where c1 = 1; -- lock wait	세션1의 1번작업 완료를 기다림
5	ORA-00060: deadlock detected while waiting for resource		deadlock 감지하여 1번 마지막 명령 종료
6	SQL> rollback ; Rollback complete.		트랜잭션이 종료된 것은 아니여서 세션2의 3번 작업은 아직 waiting
7		1 row updated.	세션1 트랜잭션 종료 시 세션2번 작업 진행

 

.trc 파일 내용

session 29, session 15 사이에 데드락 발생

데드락이 발생한 오브젝트 번호는 83212, 파일번호는 7, 블록번호는 2547127

첫 번째 로우의 아이디(ROWID)는 AAAUUMAAHAAJt23AAA, 두 번째 로우의 아이디는 AAAUUMAAHAAJt23AAB

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
                                          ------------Blocker(s)-----------  ------------Waiter(s)------------
Resource Name                             process session holds waits serial  process session holds waits serial
TX-000E0002-0000041B-00000000-00000000         54      29     X        35568      64      15           X  22779
TX-000F001E-00000553-00000000-00000000         64      15     X        22779      54      29           X  35568

----- Information for waiting sessions -----
Session 29:
  sid: 29 ser: 35568 audsid: 4294967295 user: 0/SYS
    flags: (0x8100041) USR/- flags2: (0x40009) -/-/INC
    flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
  pid: 54 O/S info: user: oracle, term: UNKNOWN, ospid: 5879
    image: oracle@svr1 (TNS V1-V3)
  client details:
    O/S info: user: oracle, term: pts/1, ospid: 5878
    machine: svr1 program: sqlplus@svr1 (TNS V1-V3)
    application name: sqlplus@svr1 (TNS V1-V3), hash value=1880491583
  current SQL:
  update sk.tab1 set c2 = 2 where c1 = 2

Session 15:
  sid: 15 ser: 22779 audsid: 4294967295 user: 0/SYS
    flags: (0x100041) USR/- flags2: (0x40009) -/-/INC
    flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
  pid: 64 O/S info: user: oracle, term: UNKNOWN, ospid: 10668
    image: oracle@svr1 (TNS V1-V3)
  client details:
    O/S info: user: oracle, term: pts/2, ospid: 10667
    machine: svr1 program: sqlplus@svr1 (TNS V1-V3)
    application name: sqlplus@svr1 (TNS V1-V3), hash value=1880491583
  current SQL:
  update sk.tab1 set c2 = 2 where c1 = 1

----- End of information for waiting sessions -----
...
...
----- VKTM Time Drifts Circular Buffer -----
session 29: DID 0001-0036-0000002B      session 15: DID 0001-0040-00000034
session 15: DID 0001-0040-00000034      session 29: DID 0001-0036-0000002B

Rows waited on:
  Session 29: obj - rowid = 0001450C - AAAUUMAAHAAJt23AAB
  (dictionary objn - 83212, file - 7, block - 2547127, slot - 1)
  Session 15: obj - rowid = 0001450C - AAAUUMAAHAAJt23AAA
  (dictionary objn - 83212, file - 7, block - 2547127, slot - 0)

2022-06-13 11:55:05.699*:ksq.c@13392:ksqdldnl(): Information for THIS session:

----- Current SQL Statement for this session (sql_id=9xqqdp68h3un8) -----
update sk.tab1 set c2 = 2 where c1 = 2

 

2개 테이블, INSERT, UPDATE, DELETE 작업별 deadlock 발생 테스트

테스트 테이블 생성

create table sk.tab10 (c1 number primary key);
create table sk.tab20 (c1 number primary key);

 

반응형

insert

2개 테이블 간 insert 진행 시 deadlock 발생 테스트

순서	세션1	세션2	설명
1	SQL> insert into sk.tab10 values(1); 1 row created.
2		SQL> insert into sk.tab20 values(1); 1 row created.
3	SQL> insert into sk.tab20 values(1); -- lock wait		세션2의 2번작업 완료를 기다림
4		SQL> insert into sk.tab10 values(1); -- lock wait	세션1의 1번작업 완료를 기다림
5	ORA-00060: deadlock detected while waiting for resource		deadlock 감지하여 1번 마지막 명령 종료
6	SQL> rollback ; Rollback complete.		트랜잭션이 종료된 것은 아니여서 세션2의 3번 작업은 아직 waiting
7		1 row created.	세션1 트랜잭션 종료 시 세션2번 작업 진행

 

테스트 데이터 적재

update, delete 테스트를 위한 데이터 적재

insert into sk.tab10 values(1);
insert into sk.tab20 values(1);
commit;

 

update

2개 테이블 간 update 진행 시 deadlock 발생 테스트

순서	세션1	세션2	설명
1	SQL> update sk.tab10 set c1=2 where c1=1; 1 row updated.
2		SQL> update sk.tab20 set c1=2 where c1=1; 1 row updated.
3	SQL> update sk.tab20 set c1=2 where c1=1; -- lock wait		세션2의 2번작업 완료를 기다림
4		SQL> update sk.tab10 set c1=2 where c1=1; -- lock wait	세션1의 1번작업 완료를 기다림
5	ORA-00060: deadlock detected while waiting for resource		deadlock 감지하여 1번 마지막 명령 종료
6	SQL> rollback ; Rollback complete.		트랜잭션이 종료된 것은 아니여서 세션2의 3번 작업은 아직 waiting
7		1 row updated.	세션1 트랜잭션 종료 시 세션2번 작업 진행

 

delete

2개 테이블 간 delete 진행 시 deadlock 발생 테스트

순서	세션1	세션2	설명
1	SQL> delete sk.tab10 where c1=1; 1 row deleted.
2		SQL> delete sk.tab20 where c1=1; 1 row deleted.
3	SQL> delete sk.tab20 where c1=1; -- lock wait		세션2의 2번작업 완료를 기다림
4		SQL> delete sk.tab10 where c1=1; -- lock wait	세션1의 1번작업 완료를 기다림
5	ORA-00060: deadlock detected while waiting for resource		deadlock 감지하여 1번 마지막 명령 종료
6	SQL> rollback ; Rollback complete.		트랜잭션이 종료된 것은 아니여서 세션2의 3번 작업은 아직 waiting
7		1 row deleted.	세션1 트랜잭션 종료 시 세션2번 작업 진행