복수의 테이블을 사용하는 트랜잭션에서 deadlock 발생 예제

복수의 테이블을 사용하는 트랜잭션에서 deadlock 이 발생되는 경우

ORA-00060: 자원 대기중 교착 상태가 검출되었습니다
ORA-00060: deadlock detected while waiting for resource

유명한 deadlock 에러 메시지이다. deadlock 은 update 에서만 발생하는 것이 아니고 하나의 테이블에서만 발생하는 것도 아니다. 아래에 두개의 테이블에서 insert, update, delete 각각의 경우 deadlock 이 발생되는 예제를 담았다.

-- 환경구성

create table dl_tb1 (id number primary key);
create table dl_tb2 (id number primary key);

Insert deadlock test

--ses1
insert into dl_tb1 values(1);

--ses2
insert into dl_tb2 values(1);

--ses1
insert into dl_tb2 values(1);

--ses2
insert into dl_tb1 values(1);

Update deadlock test

insert into dl_tb1 values(1);
insert into dl_tb2 values(1);
commit;

--ses1
update dl_tb1 set id=2 where id=1;

--ses2
update dl_tb2 set id=2 where id=1;

--ses1
update dl_tb2 set id=2 where id=1;

--ses2
update dl_tb1 set id=2 where id=1;

Delete deadlock test

--ses1
delete dl_tb1 where id=1;

--ses2
delete dl_tb2 where id=1;

--ses1
delete dl_tb2 where id=1;

--ses2
delete dl_tb1 where id=1;

deadlock 을 예방하기 위해서는 insert, update, delete 대상이 되는 테이블들의 작업순서를 같게 해야 한다. 즉, 어떤 모듈에서는 tb1 -> tb2 순서로 insert 하고, 어떤 모듈에서는 tb2 -> tb1 순서로 insert 한다면 deadlock 이 발생될 가능성이 있는 것이다.
이렇게 다른 순서로 작업이 가능한 이유 중엔 FK 제약조건을 걸어놓지 않은 원인도 있다. 이 경우 insert 작업이라면 자식테이블이 먼저 insert 될수가 있기 때문이다.

또 하나 중요한 것, deadlock 에러를 받은 세션은 rollback 처리 해야 한다는 것이다. 왜냐면 이 세션은 에러를 받으면서 맨 마지막 문장만 rollback 되었지 트랜잭션은 여전히 살아있기 때문이다. 따라서 트랜잭션을 rollback 하기 위해서는 명시적인 rollback 이 필요하다.