꿈꾸는 개발자, DBA 커뮤니티 구루비

1. 트렌젝션 동시성 제어
2. 트렌젝션 수준 읽기 일관성
3. 비관적 vs 낙관적 동시성 제어
4. 동시성 구현 사례
5. Oracle Lock

h1.1. 트렌젝션 동시성 제어

동시성 제어란?

 동시에 실행되는 트렌젝션 수를 최대화 + CRUD 시 데이터의 무결성 유지
 동시성 제어가 어려운 이유는 동시성과 일광성이 트레이드 오프 관계이기 때문

트렌젝션이란?
 일관성 있는 데이터 처리를 하려면
  여러개의 수정작업이 하나의 작업처럼 전부 처리되거나 아예 전부 처리가 안되도록 구현되어야 함
  이러한 일의 최소 단위를 트렌젝션이라 함

모든 처리가 성공 아니면 실패가 되어야 함
트렌젝션 처리 중 다른 트렌젝션에 의한 간섭에 대한 고려 필요
업무 처리의 기본 단위를 정의하는 것
ex>주문 transaction : 상품정보 확인 - 주문정보 입력 - 최종결제 완료

트렌젝션의 특징(ACID)

A: 더 이상 분해가 불가능한 업무의 최소 단위
C: 성공적으로 완료했을 때 언제나 일관성 있는 상태로 변환함
I: 트렌젝션 실행 중에는 다른 트렌젝션의 간섭을 받지 않음
D: 트렌젝션이 성공하면 결과는 영구적으로 저장 됨

h1.2. 트렌젝션 수준 읽기 일관성

 트렌젝션 시작 시점을 기준으로 일관성있게 읽어들이는 것
 오라클은 문장수준의 읽기 일관성을 보장 함
 -> 트렌젝션 수준으로 구현하려면 고립화 수준을 높여야 함

h3.ANSI/ISO 에서 정의하는 개념은 다음과 같다

h5.1) Level 0: Read commited
아직 커밋되지 않은 데이터를 다른 트렌젝션이 있는 것을 허용

발생 가능한 현상
a. Dirty read
b. Non Repeatable read
c. Phantom read

h5.2) Level 1: Read commited
커밋 된 데이터만 읽는 것을 허용

방지할 수 있는 현상
a. Dirty read
발생가능한 현상
a. Non Repeatable read
b. Phantom read

오라클은 Undo 를 이용해 구현 가능 함

h5.3)Level 2: Repeatable read
선행 트렌젝션이 읽은 데이터는 종료 시 까지 다른 트렌젝션이 갱신 삭제 불허

방지할 수 있는 현상
a. Dirty read
b. Non repeatable read
발생 가능 현상
a. Phantom read

오라클은 명시적으로 지원하지 않지만 for update 기능으로 구현 가능 함

h5.4)Level 3: Serializable read
선행 트렌젝션이 읽은 데이터를 후행 트렌젝션이 갱신, 삭제, 삽입 불가

방지할 수 있는 현상
. a. Dirty read
b. Non Repeatable read
c. Phantom read

h5.트렌젝션 고립화 수준 조정기능을 사용하려면


SQL > set transaction isolation level serializable ;

- 낮은 단계 고립화 수준(Level 0, 1, 2) 에서 발생할 수 있는 세 가지 현상

  Dirty read (uncommited dependency)
  Non repeatable read (inconsistent analysis)
  Phantom read

h5.1) Dirty read

커밋 안 된 데이터를 다른 트렌젝션이 읽음
트렌젝션이 록백되면 최종 결과값이 비 일관적으로 될 가능성이 있음
오라클은 다중 버전 읽기 일관성 모델을 채택하므로써(멀티비저닝) 락을 사용하지 않고도 Dirty read 를 피할 수 있으므로
  레벨 0 으로 고립화 수준을 낮추는 방법을 아예 제공하지 않음

h5.2) Non repeatable read
한 트렌젝션 내 같은 쿼리를 두 번 수행할 때 그 사이에 다른 트렌젝션이 값을 수정/삭제 하므로 두 쿼리의 결과가 상이하게 나타나는 비 일관성
Lost update 발생

h5.3) Phantom read

한 트렌젝션 안에서 일정범위의 레코드를 두 번 읽을 때 처음 쿼리에 없던 레코드가 두 번째에서는 나타남
ORACLE 은 문장 수준의 읽기 일관성을 보장하기 때문에 T4 시점에 T3 에 커밋 된 데이터도 삭제한다.
만일 T3 의 TX2 커밋이 T5 이후일 경우 TX1 에서는 T5 이후 TX2 가 커밋 시 T2 에 입력 한 데이터가 보임

h1.3. 비관적 vs 낙관적 동시성 제어

비관적 동시성 제어
  같은 데이터를 동시에 수정 할 것으로 가정
  읽는 시점에 락을 걸고 조회/갱신 완료시까지 유지

낙관적 동시성 제어
  같은 데이터를 동시에 수정하지 않을 것으로 가정
  읽는 시점에 락은 걸지 않지만 수정하는 시점에 읽은 데이터가 다른 사용자에 의해 변경되었는지 재 검사 필요
  ex> 주문-결재 Transaction 에서 주문 진행하는 동안 상품 금액이 변경되었으면 최종 결재 버튼 클릭 시 가격의 변경여부 체크하고 처리방향을 결정하는 프로세스

h3.1) 오라클의 비관적 동시성 제어 방법

a. for update 문으로 구현

동시성 저하가 발생할 수 있음
wait/nowait 옵션으로 무한정 대기하지 않아도 됨
   for update nowait - 대기 없이 에러 발생(ORA-00054)
   for upeate wait 3 - 3초 대기 후 에러 발생(ORA-30006)
   -> 에러 메시지 받아 예외처리 로직 구현 가능함

h3.2) 오라클의 낙관적 동시성 제어 구현 방법

a. 읽은 데이터 컬럼 전체를 where 조건으로 update 구현

읽은 데이터가 많다면 번거롭다

b. 최종변경일시를 관리하는 컬럼이 있다면 조건절에 넣어 갱신여부 판단 가능

c. for update nowait 으로 낙관적 동시성 제어 가능

d. Oracle 의 pseudo 컬럼으로 활용 가능

scn_to_timestamp(ora_rowscn) 으로 변경 후 커밋된 시점을 추적 가능함
테이블이 rowdependencies 모드로 만들어져 있어야 함(row 단위로 scn 기록 함)
   * 기본은 norowdependencies - 블록 단위로 scn 변경 기록 함
smon 이 scn 과 timestamp 간 매핑정보를 관리 (테이블 sys.smon_scn_time) 하며 보관주기는 5일
   -> 5일 이전의 정보 찾을때난 ORA-08181 에러 발생
   * 버그 5270479, 7338384 로 인해 사용할 수 없다

h1.4. 동시성 구현 사례

일련번호 채번을 통한 동시성을 높일 수 있다

h3.1) max + 1

두 트렌젝션이 동시에 같은 값을 읽었을 경우 인서트 순간 PK 제약에 위배
PK 위배 에러를 익셉션을 통해 어플리케이션에서 처리 가능하다

h3.2) 채번 테이블

h5.펑션 구현 시 autonomus transaction 사용 필요 함

채번 테이블 생성
{cpde:xml}
create table seq_tab (
gubun varchar2(1),
seq number,
constraint pk_seq_tab primary key(gubun, seq)
organization index
)
;


\\

*채번 펑션 생성
\\
{code:xml}
create or replace function seq_nextval(l_gubun_number) 
return number 

-- pragma autonomoust 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; 

return l_new_seq; 
end;

채번 함수를 정의하는 트렌젝션 예시*


begin 

update tab1 
set col1 :x 
where co12 :y ;

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

loop
...
end loop; 

commit;

exception 
when others then
rollback; 

end;
/

이 경우 커밋 시점이 중요 포인트이며
seq_nextval 펑션 안에서 commit 을 하도록 구현했을 시
메인 트렌젝션이 인서트 이후 롤백 될 경우 인서트 전 Update 는 펑션 내 commit 에 의해 이미 커밋이 되어 버린 상태
이 경우 데이터 일관성이 깨짐
seq_nextval 펑션 안에서 commit 을 하지 않도록 구현했을 시
메인 트렌젝션이 모두 종료 될 때까지 채번 테이블에 Lock 상태 유지
메인 트렌젝션의 Loop 에서 지연이 발생 할 경우 동시 채번이 빈번하게 발생하면 동시성 저하
autonomus transaction
PL/SQL 내에서 별도의 sub transaction 을 생성 하여 처리하도록 구현 가능한 기능

h3.3) 선분이력 테이블의 동시성 사례



declare  
  cur dt varchar2(14); 
begin 

--<1>--
cur dt := to char(sysdate, 'yyyymmdd hh24miss') ;   
 
--<2>--
update 부가서비스이력 
set 종료일시 to_date(:cur_dt,'yyyymmdd hh24miss') - 1/24/60/60 
where 고객ID = 1 
and 부가서비스ID 'A' 
and 종료일시 to date ('99991231235959' , 'yyyymmdd hh24miss')
;

--<3>--
insert into 부가서비스이력  (고객ID， 부가서비스ID， 시작일시, 종료일시) 
values (
1, 'A' , to_date(:cur_dt, 'yyyymmdd hh24miss'), 
to_date( '99991231235959', 'yyyymmdd hh24miss')
); 

--<4>--
commit;

end;

이력을 입력하는 세션들의 트렌젝션이 시작/종료일시가 역전 될 경우 이력이 깨지게 됨
ex> 첫 세션이 <1> 을 수행 후 <2> 로 가기 직전, 두 번째 세션이 <1> ~ <4> 를 먼저 진행
   이 경우 두 이력의 순서가 뒤바뀜
트렌젝션이 순차적으로 진행될 수 있도록 직렬화 장치를 마련해야 함

이력 테이블에 select for update 문을 사용하면 가능한가
ex> 기존에 이력이 없던 고객에 대해 동시에 이력을 입력하는 트렌젝션이 생길 수 있음
   락이 걸리지 않으므로 시작 일시는 다르고 종료일시가 Max 인 두개의 이력 존재

이력 테이블의 상위 테이블에 Lock 을 걸면 문제 해결
고객 테이블은 업무적으로 여러 사용자가 동시에 접근 할 가능성이 낮으므로 동시성 저하 희박


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

h1.5. Oracle Lock

다양한 락/래치 매커니즘이 있으나 이번 장에서는 DML 과 관련 된 락을 설명한다
9가지 내용의 락은 아래와 같다

Euqueue Lock
TX Lock
TX Lock(무결성위배/비트맵인덱스 갱신)
TX Lock(ITL 슬롯 부족)
TX Lock(인덱스 분할)
TX Lock(기타 트렌젝션 락)
TX Lock(DML 로우 락)
TM Lock(DML 테이블 락)
커밋

h3.1) Enqueue lock

h5.공유 리소스에 대한 엑세스 관리를 목적으로 하는 락

Table, transaction, tablespace, sequence, 등등..

h5.순서가 보장되는 큐 구조를 사용
h5.락 획득하려면 먼저 Enqueue 리소스를 할당받아야 함

Enqueue resource 란?
-소유자/대기자 목록을 관리할 수 있는 구조체
-각 리소스에는 고유한 식별자가 부여 됨
-식별자는 Type-ID1-ID2 로 구성
Type 은 TX, TM, TS 처럼 2문자
ID1, ID2 는 락 종류에 따라 다른 정보를 가짐
 ex, TM Lock - Type: TM, ID1: Object ID, ID2: 0
    TX Lock - Type: TX, ID1: undo segment 번호+트렌젝션 슬롯 번호, ID2: 트렌젝션 슬롯 sequence

오라클은 Euqueue resource 를 통합 관리하는 array 를 가지고 있음
각 리소스를 찾으려면 해시 알고리즘 사용
Linked list 로 연결된 해시체인을 가지며, 리소스 구조체 연결 됨

락 획득 과정은 아래와 같다
1. 리소스 테이블에서 리소스 구조체를 찾는다
2. 리소스 구조체를 찾지 못하면 새 구조체를 할당받아 해시 체인 연결 리스트에 연결
3. 리소스 구조체의 소유자 목록에 자신을 등록
4. 호환되지 않는 모드로 먼저 락을 획득한(소유자 목록에 등록 된) 세션이 있으면
대기자 목록에 등록 후 대기 or 작업을 포기

소유자가 Exclusive 모드일 때는 한 번에 하나의 세션만 락 획득 가능
Shared mode 일 때는 여러 세션이 동시에 락 획득 가능
Shared - Shared 는 호환 되나, Shared - Exclusive / Exclusive - Exclusive 는 호환되지 않는다

h3.2) TX Lock (트렌젝션 락)

변경 중인 레코드나 리소스를 동시에 변경하려는 트렌젝션은 엑세스를 직렬화해야 하며, 그 목적으로 사용
첫 번째 트렌젝션을 시작할 때 얻고, 커밋/롤백 시 해제
Type+ID1+ID2 를 가지는 리소스 구조체를 Enqueue resource 테이블 해시체인에 연결, 소유자 목록에 트렌젝션 등록
대기중인 Transaction 은 3초에 한번씩 진행중인 트렌젝션이 설정한 락 상태를 확인(deadlock 발생 여부를 확인하기 위함)

v$lock 뷰를 이용해 경합상황 모니터링 가능
원인을 파악하려면 대기 이벤트 발생 현황을 관찰해야 함
대기 이벤트 명에 따라 TX Lock 은 아래와 같이 구분 가능

이벤트가 row lock contention 일 경우 lock 모드에 따라 발생 원인을 판단해야 함
Lock 모드는 이벤트와 함께 기록되는 P1 parameter 로 확인 가능 함
상세 내용은 책 참조

h3.3) TX Lock (무결성 제약 위배 혹은 비트맵 인덱스 갱신) - enq: TX - row lock contention

Unique 인덱스나 FK가 있을 경우 insert 에 의한 로우 락 가능
선행 트렌젝션이 아직 진행 중이라면 값의 중복 여부가 확정되지 않았으므로 후행 트렌젝션은 대기

비트맵 인덱스도 하나의 엔트리가 여러 레코드와 매핑 되므로 엔트리 갱신 시 매핑 전체 row 에 락 발생

h3.4) TX Lock (ITL 슬롯 부족) - enq: TX allocate ITL entry

블록에 CUD 작업 시 ITL 슬롯 할당 후 transaction id 를 기록해야 함
빈 슬롯이 없다면 ITL 을 사용하는 다른 트렌젝션이 커밋/롤백 전까지 대기
ITL 슬롯의 갯수는 initrans 에 의해 설정 됨
ITL 슬롯의 Max 갯수는 Maxtrans 에 의해 결정 되며 pctfree 공간에 생성
--> Maxtrans 를 초과하거나 PCT free 부족 시 락 대기 현상 발생

인서트 시에는
테이블은 ITL 슬롯 부족 시 새 블록 할당 해 수행 가능함
인덱스는 정렬 상태를 유지해야 하므로 새 블록 할당 못하고 여전히 경합 발생

Update, Delete 일 경우 테이블/인덱스를 불문하고 ITL 경합 발생 함
ORACLE 10G 부터는 Maxtrans 는 255 로 고정(사용자가 값 지정해도 무시함)
--> pctfree 부족에 의한 경합은 여전히 발생 가능

트렌젝션 빈번한 세그먼트에 대해서는 initrans 를 늘려주어야 함


SQL> select ts#, obj#, dataobj#, sum(value) itl_waits 
from v$segstat where statisticname = 'ITL waits'
group by ts#, obj#, dataobj # having sum(value) > 0 
order by sum(value) desc
;

Initrans 값을 변경하더라도 슬롯 갯수는 변함 없고 새로 할당하는 블록에서만 적용
--> 기존 블록의 경합이 많을 때는 전체 재 생성 필요

h3.5) TX Lock (인덱스 분할) - enq: TX - index contention

인덱스는 정렬을 유지해야 하므로 값을 입력 할 위치에 빈 공간이 없으면 Split
인덱스 분할이 진행되는 동안 그 블록에 새 값을 입력하려는 다른 트렌젝션이 있을 수 있으며
이 경우 선행 트렌젝션 완료 전까지 대기

인덱스 분할 시에는 autonomus 트렌젝션으로 subtransaction 을 발생시킴
인덱스 분할 발생시킨 transaction 이 오래 트렌젝션을 유지하더라도 다른 트렌젝션이 인덱스 블록 사용 가능하도록

인덱스 분할을 최소화 하기 위해 pctfree 를 증가시켜도 효과 미비함
테이블의 pctfree 는 Update 를 위해 남겨두는 공간이나 인덱스는 Insert 를 위한 공간이다
인덱스를 주기적으로 재 생성하지 않는 한 언젠가는 pctfree 공간이 채워지게 되므로 Split 현상을 줄이지는 못함

h3.6) TX Lock (기타)

shared mode 의 enq: TX contention
분산 transaction 에서 2-phase 커밋을 위함
읽기 전용 테이블스페이스 전환 시에도 발생

h3.7) TX Lock (DML row lock)

다중 사용자에 의한 데이터 무결성 보호
오라클은 row 단위 lock 과 TX lock 을 조합해서 구현 함
Undo segment 에서 transaction slot 을 할당
enqueue resource 를 통해 TX Lock 을 획득
이후 갱신되는 각 로우마다 exclusive 모드로 row 단위 락 획득

TX lock 은 트렌젝션 시작 시 한 번만 획득함

row lock 에 대해 1장에서 설명 한 내용을 복습하면
1. block 헤더 ITL 슬롯에 트렌젝션 ID 기록
2. 로우 헤더에 이를 가리키는 Lock byte 설정
3. 동일 레코드를 엑세스 하는 다른 트렌젝션은 lock byte 를 통해 ITL 슬롯을 찾고
  ITL 슬롯이 가리키는 undo segment 헤더의 transaction slot 에서 트렌젝션 상태 정보를 확인
4. 선 트렌젝션이 진행중일 때 이를 읽으려는 후 트렌젝션은 전 트렌젝션 상태를 확인하고 CR 블록 생성해서 읽기 작업 완료
  이를 갱신하려는 후 트렌젝션은 선 트렌젝션 완료시까지 대기 필요
  이를 위해 TX lock 설정

Row lock 과 TX lock 에 대한 구분
Row lock: 블록 헤더 ITL 과 로우 헤더 Lock byte 설정을 의미
  로우를 갱신 중인 트렌젝션 상태를 확인하고 엑세스 가능 여부 결정 함
TX lock: enqueue 리소스 통해 TX lock 설정, Lock 이 설정 된 레코드를 갱신하고자 할 때 enqueue 리소스에서 대기 함

h3.8)TM lock (DML 테이블 락) - enq: TM - contention

row lock 획득 시 해당 테이블에 대한 락도 동시에 획득하는데 이를 TM Lock 이라고 한다
현재 트렌젝션이 갱신 중인 테이블에 대해 호환되지 않는 DDL 을 방지한다

명시적으로 table lock 명령어를 사용할 수 있음


        lock table table_name in row share mode  
        lock table table_name in row exclusive mode
        lock table table_name in share mode
        lock table table_name in share row exclusive mode
        lock table table_name in exclusive mode

테이블 락은 여러가지 모드가 사용되며, 호환성은 아래 표와 같다

Insert, Update, Delete, Merge 를 위해 row lock 설정하려면 테이블에 RX(SX) 모드 락 획득 필요
select for update 를 위해 row lock 설정하려면 RS(SS) 모드 테이블 락 먼저 획득 필요
RS-RX 간은 호환이 되므로 select for update 와 DML 문 수행 시 테이블 락 경합은 발생하지 않음
  row lock 에 의한 경합은 발생

오라클의 테이블 락은 선행 트렌젝션이 어떤 작업을 수행 중인지에 대한 일종의 푯말

여러 모드가 있고 이에 따라 후행 트렌젝션이 작업 가능한 범위가 결정 됨
ex>
DDL 로 구조 변경하려는 세션은 TM 락이 설정되어 있는지 먼저 확인
DDL 문이 먼저 수행되고 DML 이 동일 테이블에 수행 될 경우 DML 세션이 enq: TM - contention 이벤트 발생하며 대기

테이블 락은 DML-DML 간 동시성을 제어하기 위해서도 사용
  병렬 DML이나 Direct path insert 방식 작업 수행할 때
  이 때 lock 을 모니터링 하면 테이블 락을 Row exclusive(SX) 가 아닌 Exclusive 모드로 설정 한 것을 확인 할 수 있음
  타 세션이 이 테이블 다른 row 에 update 시도하더라도 blocking 됨
  -> 로우 락 호환성 확인 이전에 테이블 락 호환성을 먼저 확인하고 수행 됨을 알 수 있다
TX lock 은 트렌젝션마다 한 개 씩 획득 - TM Lock 은 트렌젝션에 의해 변경이 가해진 오브젝트 수 만큼 획득

락을 얻고자 리소스 사용 중일 때 아래 3가지 방법중 하나를 택함
  락 해제될 때까지 대기
  일정시간만 기다리다 포기
  기다리지 않고 포기

DDL 문을 수행할 때도 내부적으로 Lock 획득하려 하는데 이때는 nowait 옵션이 자동으로 지정 됨

h3.9) Commit

불필요하게 트렌젝션을 길게 유지하지 않도록 적절한 시점의 Commit 필요

Transaction 을 너무 길게 가져가면

동시성 저하 되거나
롤백 시 시간이 많이 걸리거나
Undo segment 가 고갈되거나 경합 유발 가능

Transaction 이 너무 짧으면(잦은 Commit)

Snapshot too old 에러를 유발 할 가능성이 높음
LGWR 이 로그 버퍼를 비우는 log file sync 이벤트로 성능 저하 가능

10g 부터 등장 한 커밋 방식 제어 명령어로 잦은 커밋에 대한 대응을 할 수 있다고 함

wait/nowait

wait
LGWR 이 로그버퍼를 파일에 기록했다는 완료 메시지를 받을 때 까지 대기
그 전까지 log file sync 대기 이벤트 발생

nowait
LGWR 의 완료메시지를 기다리지 않고 바로 다음 트렌젝션 진행하므로 log file sync 대기 이벤트 없다

immediate/batch

immediate
커밋 명령을 받을 때마다 LGWR 이 로그 버퍼를 파일에 기록한다

batch
세션 내부에 트렌젝션 데이터를 일정 량 쌓아뒀다가 일괄 처리한다

이를 조합 해 아래 4가지 커밋을 활용 가능

a. commit write immediate wait(기본)

b. commit write immediate nowait

c. commit write batch wait

d. commit write batch nowait

저자의 각 방식에 대한 테스트 결과는

nowait 에 의한 성능 개선 효과는 크다
batch 옵션의 영향력은 미미하다
batch 옵션을 사용했을 때 PGA 메모리 영역에 트렌젝션 데이터를 버퍼링했다 사용하는 것으로 추정된다

비동기식 커밋은
커밋 직후 인스턴스 문제가 생기거나 redolog 에 문제가 생겨 쓰기를 완료하지 못할 때 문제가 생길 수 있음

commit_write 파라메터를 통해 시스템/세션 레벨에서 기본 설정 변경 가능