==================================================================================
ORA-00060 : DEADLOCK과 INITRANS (같은 TABLE내의 다른 범위의 DATA처리시 ORA-60)
==================================================================================

 deadlock에  관한  일반적인  사항은  <Bul:11742>에  정리되어  있다.  같은  data를  동시에   변경하는
 transaction의 경우  deadlock이 발생하는  것은 application  logic을 수정하여  해결해야 하는  경우가
 대부분이다.

 그런데 같은 table에 대해서 동시에 수행되는  transaction이 각자 서로 다른 data를 처리하는  경우에도
 ora-60(deadlock detected while waiting for resource)이 발생할 수 있다.

 예를 들어 한 transaction은 A table의 1월 data를 처리하고, 동시에 다른 transaction은 같은 A  table의
 2월 data를 처리하는 것과 같은 경우이다.

 이러한 경우에도 initrans가  작게 설정되어 있으면,  ora-60이 발생할 수  있는데, 이 자료에서는  이와
 같이 다른 data를 처리하는 transaction들 사이에서의 ora-60이 발생하는 경우와 조치사항을 확인한다.

  1. transaction entry에 대해서
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 table이나 index에 포함된 모든 block에 update/delete/insert와 같은 dml을 수행하기 위해서는 일단  그
 block에 transaction정보를 저장시킬 transaction entry를 확보한 후에 원하는 작업이 수행가능하다.

 이  transaction entry의  크기는 os  dependent하기는 하나  대부분 23  bytes이며, table이나   index의
 initrans  option에 의해,  미리 확보되는  block당 transaction  entry의 갯수가  결정된다. default는
 table이 1, index가 2이다.

 이  transaction  entry가  특정 transaction에  할당되면  그  transaction이 commit이나   rollback되기
 전까지는 다른 transaction에서  사용할 수 없다.  같은 block에 다른  transaction이 dml을  수행하려면,
 남은 공간중에서 23 bytes의 transaction entry를 새로 할당하거나, 공간이 없으면 앞에서 먼저 사용중인
 transaction이 commit/rollback되기를 기다려야 한다.

  2. ORA-60이 발생하는 경우
  ~~~~~~~~~~~~~~~~~~~~~~~~~
 deadlock을 유발시키는 transaction이 서로간에 완전히 다른 data(같은 table)를 처리하더라도 그  data가
 같은 block에  함께 들어가  있는 경우라면  ORA-60이 발생할  수 있다.  즉, transaction  entry를  잡는
 과정에서  block내에 남은  space가 부족한   경우, 서로  상대방의 transaction이  종료되기를  기다리는
 deadlock이 발생가능하다는 것이다. 아래에 실제 예를 들어 자세한 발생 시나리오를 정리하였다.

① ORDER table은  날짜별로 data가 추가,    변경, 삭제되는 100만건  이상의 data를 가진  table이다. 이
 table에   대해서   월별로   통계작업을  수행하는데,   6개월씩   처리하기   위해  6개  transaction을
 동시에 수행하였다. 즉, T1은 1월 data, T2는 2월 data, T6는 6월 data를 처리하는 식이다.

② ORDER table은 initrans값이 1로 지정되어 있고 현재 1000개의 block이 이 table에 할당되어 있다.

③  100번지  block에  2월,  3월,  5월  data가  함께  저장되어  있다.  200번지  block에는  2월,   3월
 data가 저장되어 있다.

④ T2  transaction이  100번지  block에  이미  확보되어  있는  1개의  transaction   entry를  사용하여
 transaction정보를 저장하였다. 그리고, 2월달 data에 대한 작업을 수행하였다.

⑤ T3 transaction이 200번지 block에 initrans  1에 의해 확보되어 있는 23 bytes의  transaction entry를
 이용하여 transaction정보를 저장한 후 3월달 data에 대한 처리를 수행하였다.

⑥ T2 transaction이 2월달 data중 나머지 부분을  처리하기 위해 200번지를 access하여 23 bytes의 T2  를
 위한   transaction     entry를   확보하려고      하였으나,   block에     남은   공간이      없어서,
 T3 transaction이  commit할때까지   기다린다.   5번  단계에서,    initrans에  의해  미리    확보되어
 있는 공간을  사용하는 T3 transaction이 종료되면, 그 부분을 T2가 사용할 수 있게 되는 것이다.

⑦ T3  transaction도  100번지에  있는 3월  data를  처리하기  위해  100번지내에 transaction   entry를
 추가적으로   확보하려 하였으나,    공간이 없어서,    마찬가지로  미리   100번지 block을    사용하고
 있는 T2 transaction이 종료되기를 기다리게 된다.

⑧ 6과 7상황에  의해 T2와 T3  transaction은 서로 상대방이  종료되기를 기다리는, deadlock이  발생하게
 되므로   deadlock상황을  유발시킨     T3   transaction이   ORA-60을    발생시키면서   종료   되고,
 T3  transaction은   rollback된다.  200번지에   확보한    transaction   entry부분도  반환하여   다른
 transaction이 사용가능한 상태가 된다.

⑨ 8번에서 release된 200번지  block내의 transaction entry 23  bytes를 6번 단계에서 기다리고  있던 T2
  transaction이 확보하고 작업을 진행한다.

  3. deadlock을 피하기 위한 방법
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

결론적으로,  같은  table에 대해서  다른  data를 처리하는  transaction이라  하더라도 동시에  수행하는
transaction이 많은 경우라면 initrans 값이 작은 경우 ora-60이 발생할 수 있게 된다.

하나의 table에 대해서  performance등의 이유로 동시에  다른 data를 처리하는  transaction을 수행하고자
한다면,  table의  initrans  값을  크게하여,  하나의  block에  여러  개의  transaction이  dml  처리를
수행하더라도 space가 부족하여 기다리는 상황은 없도록 하여야 한다.

initrans를 n으로 지정한다면 23*n bytes가 항상 transaction entry로 미리 확보되어 있는 것이다.  이렇게
transaction entry로 초기에 확보된  공간은 data를 저장할 수  없는 공간이 되므로, 너무  크게 하는 것은
space 낭비가 초래된다. 하나의 block에 대해서  동시에 여러 transaction이 처리되지 않는 경우라면  미리
확보된  transaction entry는  사용도 되지  않고 낭비되어,  full table  scan 등의  작업에 성능  악화만
초래하게 된다.

initrans값은 하나의 table에 대해서 동시에  처리하는 transaction의 갯수 이하로 지정하도록  한다. 해당
table에  대한  동시  transaction의  갯수만큼 initrans를  지정하면  앞에서  설명한  상황의 deadlock은
발생하지 않는 것이 보장되나 space를 고려할 때 그 보다는 약간 작게 하는 것이 일반적인다.

initrans는 table이나 index에  대해서 create나 alter문장시  지정, 변경이 가능하나,  alter의 경우 이미
확보된 block에는 영향을 미치지 못하므로 export/import를 이용하여 새로 지정하는 것이 필요하다.

다음에 scott.dept table에 대해서 예를 들었다. column정의 storage등은 임의의 값을 예로 사용한 것이며,
initrans도 예로 3을 지정하였다.

  os> exp scott/tiger file=test.dmp tables=dept

  os> sqlplus scott/tiger
  SQL> drop table dept;
  SQL> create table dept (deptno number(2), dname varchar2(10))
	   initrans 3
	   storage(initial 10m next 2m pctincrease 0);

  os> imp scott/tiger file=test.dmp tables=dept ignore=y

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

(0) (0)