(1) 일련번호 채번 동시성 높이기

         . Locking을 최소화 하면서 채번 테이블로부터 일련번호를 채번 하고자 할때 사용
         . DBMS가 제공하는 Sequence 기능을 이용


 but, 그럴수 없을때,   


          1. 데이터가 삽입되는 시점에 실시간으로 현재의 MAX값을 취해 1만큼 증가시킨 값을 이용
                   -> 두개의 트랜잭션이 동시에 같은 값을 읽었을 경우, insert 하려는 순간 PK 제약에 위배되므로  예외처리를 통해 동시성 제어
           2. MAX 값을 관리하는 별도의 채번 테이블에 값을 가져오는 방식
                  -> 채번후 다음 처리로 진행하기 전에 채번 테이블 값을 1만큼 증가시키는 갱신을 수행해야 함

 

 

-------------------------------------------------------------------------------------------

 

--채번 테이블

 
create table seq_tab (
  gubun varchar2(1),
  seq   number,
  constraint pk_seq_tab primary key(gubun, seq)
)
organization index;

 
--채번함수 정의
create or replace function seq_nextval(l_gubun number) return number
as
  /**pragma autonomous_transaction**/; 
  l_new_seq seq_tab.seq%type;
begin
  update seq_tab
     set seq = seq + 1
   where gubun = l_gubun;

  select seq into l_new_seq
    from seq_tab
   where gubun = l_gubun;

  commit;-----------(A)
  return l_new_seq;
end;
/
--트랜잭션 예시
begin
  update tab1
     set col1 = :x
   where col2 = :y ;

  insert into tab2 -----------(B)
  values (seq_nextval(123), :x, :y, :z);

  loop
    -- do anything ...
  end loop;

  commit;

exception
  when others then
  rollback;
end;
/
 ------------------------------------------------------

 

         but,  (A) 라인에서 커밋을 한다면 어떤 이유에서 메인 트랜잭션이 (B)  isert이후에 롤백될 경우 

                  (A) 에서는 update가 이미 커밋된 상태가 되어 데이터 일관성이  깨짐

 

          seq_nextval 함수에서 라인 14에서 커밋을 하지 않음
                    -> 메인 트랜잭션이 모두 종료될 때까지 채번 테이블이 Lock이 걸린 상태가 유지 되므로
                         동시 채번이 빈번히 발생하는 상황에서 심각한 성능 저하 초래

                    -> 오라클은 메인 트랜잭션에 영향을 주지 않고 서브 트랜잭션만 따로 커밋하는 기능 제공 (autonomous 트랜잭션)
                         seq_nextval 함수에서 /**pragma autonomous_transaction**/를 사용

 

Pragma Autonomous_Transaction

- 자율 트랜잭션
- 부모 트랜잭션의 승인/롤백 여부에 상관없이 자신의 작업을 승인하거나 롤백할 수 있다.

 

(2) 선분 이력 정합성 유지
       . 선분이력을 추가하고 갱신할때 발생할수 있는 동시성 이슈

         (  고객의 변경이력을 관리할 때 이력의 시작시점만을 관리하는 것을 '점이력' 모델이라고 하고,
              시작시점과 종료시점을 함께 관리하는 것을 '선분이력' 모델이라고 한다.)

 

 

 

 

 ------------------------------------------------------

-- 기존 최종 선분이력을 끊고 새로운 이력 레코드를 추가하는 전형적인 처리 루틴

declare
  cur_dt varchar2(14);
begin
  select 고객ID
    from 부가서비스이력
   where 고객ID = 1
     and 종료일시 = to_date('99991231235959', 'yyyymmddhh24miss')
     for update nowait ;

  select 고객ID
    from 고객
   where 고객ID = 1
     for update nowait ;

  cur_dt := to_char(sysdate, 'yyyymmddhh24miss') ; -- ①

  update 부가서비스이력 -- ②
     set 종료일시 = to_date(:cur_dt, 'yyyymmddhh24miss') - 1/24/60/60
   where 고객ID = 1
     and 부가서비스ID = 'A'
     and 종료일시 = to_date('99991231235959', 'yyyymmddhh24miss') ;

  insert into 부가서비스이력(고객ID, 부가서비스ID, 시작일시, 종료일시) -- ③
  values (1, 'A', to_date(:cur_dt, 'yyyymmddhh24miss'),
         to_date('99991231235959', 'yyyymmddhh24miss')) ;

  commit; -- ④
end;

 ------------------------------------------------------

 

   . 신규 등록시  ->   update 문에서 실패, insert에서 1건

   . 첫번째 트랜잭션이 1을 수행하고 2로 진입하기 직전에 두번째 트랜잭션이 동일이력에 대해

     먼저 진행하면  선분 이력이 깨지게 됨

            -> 직렬화 장치를 마련해야 함   -> 1번 문장이 수행되기 이전에 select for update 문을 이용해 해당 레코드에 Lock 설정

 

 

 ------------------------------------------------------

-- 부가 서비스 이력에 Lock을 걸어 동시성 관리


  select 고객ID    from 부가서비스이력
   where 고객ID = 1

   and 부가서비스 ID = 'A'

   and 종료일시 = to_date('99999999999','yyyymmddhh24miss')

    for update nowait ;

 ------------------------------------------------------

       . 기존에 부가서비스 이력이 전혀 없든 고객일 경우 Lock이 걸리지 않음

             --> 동시에 두개의 트랜잭션이 3번 insert에 진입가능 ---> 시작일시는 다르면서 종료일시가 같은 두개의 이력 레코드 생성

 

 

 ------------------------------------------------------

-- 고객 이력에 Lock을 걸음


  select 고객ID    from 고객
   where 고객ID = 1

    for update nowait ;

 ------------------------------------------------------

      . 동시성을 완벽하게 제어