출처 : http://blog.naver.com/imdkkang/120087840004
참고 : http://database.sarang.net/?inc=read&aid=38554&criteria=oracle
        

 데이터베이스의 존재 이유 중 하나는 수많은 사용자가 동시에 데이터에 접근, 수정하고 입력하는 작업을 수행하는 환경을 제공하는 데 있다. 자고로 많은 RDBMS 벤더에서 동시에 데이터에 접근하고 정합성을 보장하는 방법에 대해서 끊임없는 경쟁을 펼치고 새로운 방법을 내놓았다. 결국에는 많은 유저가 정합성을 보장하면서 데이터에 접근하는 환경을 제공하는 능력이 데이터 처리 능력과 트랜잭션 최대값으로 직결되고, 이것이 곧 각 RDBMS의 우열을 가르는 잣대라고 봐도 과언이 아니다. 이 글에서는 오라클 데이터베이스를 중심으로 LOCKING 메커니즘에 대해 설명한다.

 

<그림 1>과 같이 동시 유저수가 많을수록 트랜잭션 개수는 일정 수준 이상이 되면 줄어들고 정합성과 동시성 관계 역시나 반비례 관계에 있다. 데이터의 일관성을 높이기 위해서는 반드시 락(LOCK)을 사용해야 하지만, 동시성을 높이기 위해서는 락의 사용을 최소화해야 한다는 점에서 이 둘의 관계 제어(CON CURRENCY CONTROL)가 상당히 힘든 일임을 알 수 있다. 따라서 이번 글을 통해 동시성 제어를 향상시킬 수 있는 방법에 대해 알아보기로 하겠다.

 

<그림 1> 동시성과 정합성 관계

 

동시성 향상의 방법

 

LOST UPDATE 방지

① A 유저가 데이터를 조회한다.

② B 유저가 같은 로우를 조회한다.

③ A 유저가 애플리케이션을 통해 로우를 업데이트하고 COMMIT을 수행한다.

④ B 유저가 같은 로우를 업데이트한다.

 

결국은 A 유저가 업데이트한 데이터는 B 유저에 의해 오버라이트되어 버린다. 따라서 A가 수정한 데이터는 잃어버리게 된다. 이것이 전형적인 LOST UPDATE의 예이다. 이러한 오라클의 SELECT에 대한 non-blocking의 특성(select에 락모드가 null)으로 인한 세션에서의 LOST UPDATE를 방지하기 위해 정합성이 중시되는 프로그램 작성 시에 SELECT를 수행하는 로우에 SELECT FOR UPDATE 문을 사용해 명시적으로 락을 걸어주고 해당 로우에 대해 데이터 수정을 하는 경우가 많다.


그러나 이러한 Pessimistic Lock을 설정한 후 락의 보유 시간이 길거나 중간에 수행되는 로직이 복잡하면 해당 데이터를 동시에 변경하는 유저수가 늘어나고 늘어날수록 동시성은 떨어지게 된다. 물론 FOR UPDATE 문에 nowait , wiat n [second]와 같은 옵션이 존재해 대기시간을 조금 줄이는 효과를 낼 수 있지만 그리 큰 효과는 없을 것이다. 현장 애플리케이션 개발에서 오라클 locking 메커니즘을 제대로 이해하지 못하거나 믿지 못해 Pessmistic Locking을 과도하게 사용해 동시성이 저하되는 경우를 많이 목격했다.


그렇다면 Optimistic Locking만으로 동시성과 정합성이란 두 마리 토끼를 잡을 수는 없을까? 많은 방법들이 존재하지만 최적으로 꼽히는 방법 가운데 tomas kyte의 asktom 사이트에서도 수차례 언급했던 글을 토대로 방법을 알아보기로 하자.

 

Version 컬럼과 Pseudo 컬럼을 이용한 체크

 

그렇다면 일반적인 UPDATE 문, 즉 Optimistic Lock만으로 동시성을 확보하면서 여러 사용자가 동일한 데이터를 수정하더라도 정합성을 보장하는 방법에 무엇이 있는지를 살펴보도록 하자.

 

● Version 필드를 통한 체크

첫 번째 방법은 변경하는 테이블에 변경시간을 기록하는 컬럼을 추가하는 것이다. <리스트 1>은 DEPT 테이블에 last_mod라는 timestamp 형식의 컬럼을 추가해 SELECT FOR UPDATE로써 레코드 락을 걸지 않고 변경 컬럼 값만을 체크해 해당 컬럼이 변경되었다면 update되지 않도록 수행하는 처리 절차를 구사한 예이다. 이 방법은 명시적인 락을 걸지 않고도 정합성을 유지할 수 있다.

 

 

● Pseudo 컬럼을 이용한 변경 체크

앞의 변경 컬럼을 사용하는 것은 추가적인 테이블 변경 작업을 수반하기 때문에 사용할 수 없는 테이블이 수정 불가능한 환경에서는 부적합할 수도 있다. 여기서 기존의 테이블에 컬럼 추가 없이 Pseudo 컬럼을 이용한 방법을 사용할 수 있다.

 

이는 10g에서의 SCN 번호를 가져올 수 있는 ORA_ROWSCN 함수를 사용해서 체크하는 방법을 이용하면 간단히 구현될 수 있다. 10g에서는 SCN 값의 변경사항 중 하나가 종래의 block level SCN에서 Row level SCN을 지원하게 되었다는 것이다(SCN은 System Change Number 또는 System Commit Number라고 병행해서 사용된다. 이 값은 커밋시마다 부여되는 오라클의 내부시계와 같은 역할을 수행한다).

 


기존에 BLOCK 레벨로 부여하던 SCN 값이 로우 레벨에 따라 다른 번호를 가질 수 있게 된 것이다. 따라서 이러한 ora_ rowscn 값을 이용해 테이블의 로우가 언제 변경되었는지에 대한 정보도 뽑아볼 수 있다. 그러나 모든 테이블에 다 적용되는 것이 아닌 ROW LEVEL SCN을 적용하게끔 테이블 생성시 ROWDEPENDENCIES 옵션으로 생성해야 한다는 것이다.

 

UPDATE 여부 확인 

CREATE TABLE T_LOCK (
    ID NUMBER PRIMARY KEY
)
ROWDEPENDENCIES

INSERT INTO T_LOCK (ID) VALUES (1);

COMMIT;

INSERT INTO T_LOCK (ID) VALUES (2);

COMMIT;

SELECT ID
           , ORA_ROWSCN
           , SCN_TO_TIMESTAMP(ORA_ROWSCN) 
   FROM T_LOCK;

SELECT TABLE_NAME
   , DEPENDENCIES
 FROM USER_TABLES
WHERE TABLE_NAME = 'T_LOCK';

 

SEQUENCE와 채번 테이블을 이용한 동시성 향상

 

일반적으로 해당 테이블의 최대값을 읽어와서 INSERT를 수행하는 경우에 동시에 많은 유저가 수행된다면 PK 중복에러가 발생할 여지가 크다. 따라서 일반적으로 max 값을 읽어오는 select되는 시점에서 for update 문을 사용해 해당 레코드에 락을 거는 방법을 사용한다. 그러나 역으로는 동시성이 저하되는 부작용이 나타난다.

 

<그림 2> 다른 세션에서 최대값을 획득한 후 입력 시 에러 발생

 

● SEQUENCE 객체의 사용

단순 일련번호로 PK가 구성되어 있다면 시퀀스의 경우에는 동시다발적으로 여러 명의 유저가 시퀀스를 부여 받더라도 절대 중복이 발생하지 않는다. 중복 방지에만 초점을 맞춘다면 적합할지 모르지만 CACHE 값만큼 메모리에 고정(keeping)되어 있는 경우 데이터베이스 서버의 갑작스런 다운시나 메모리 영역에서 ageout되어 다음에 시퀀스를 발행할 경우 이전에 caching되어 있는 시퀀스의 수만큼 중간을 건너뛰고 발행될 수도 있다. 따라서 일련번호가 타이트하게 관리되어야 할 필요성이 있다면 적합하지 않다.

 

● 채번 테이블의 이용

시퀀스를 사용할 수 없는 특수한 환경에서는 채번 테이블을 사용하는 경우 동시 유저에 의해 채번 테이블이 사용될 수 있다. 다만 채번 테이블의 경우에는 여러 명이 동시에 접근할 경우 해당되는 채번테이블이 UPDATE 되는 경합을 유의해야 한다.

 

 

● 시퀀스를 이용한 update 분산

<리스트 4>의 경우 동시다발적인 유저가 많다면 해당되는 상태인 데이터의 첫 번째 로우를 업데이트하는 LOCKING 부하가 집중되는 문제가 발생한다.

 

 

따라서 시퀀스의 최대값을 객체를 동시에 수정하는 최대 유저수만큼 생성한 후 <리스트 5>와 같은 쿼리로 수정하면 시퀀스의 수만큼 LOCKING이 분산되는 효과를 볼 수가 있다.

 

 

Deadlock의 방지

 

프로세스 A는 테이블 T1에 락을 걸고 있고 테이블 T2의 데이터를 변경하려 하며, 프로세스 B는 테이블 T2에 락을 걸고 있고 테이블 T1의 데이터를 변경하려는 상황에서 더 이상 진행되지 않고 HANG이 발생하는 경우가 여러분들이 잘 아는 전형적인 Deadlock이라고 할 수 있다. 오라클에서 Deadlock이 발생하면 뒤에 락을 획득한 쪽(프로세스B)이 실패해 모든 락을 해제하면서 에러가 발생하고 트랜잭션이 rollback된다.

 

수년간 데이터베이스 운영과 프로젝트를 수행했지만 이러한 데이터베이스 관리 시에 Deadlock은 굉장히 드물게 발생했다. 다만 신규로 개발된 프로시저나 애플리케이션의 단일 테이블에서 일반적으로 아주 드물게 발생하는 경우가 있고 오라클에서도 자체적으로 발생하는 경우보다는 애플리케이션 설계상의 문제로 발생되는 경우가 많다. 특히 데이터베이스 애플리케이션뿐만 아니라 자바의 synchronized 등의 다양한 상황에서 만날 수 있는 경우가 많다. 프로젝트 초기에 이러한 Deadlock의 상황을 만난다면 원인을 찾아내기가 상당히 까다로운 게 사실이다. 그렇다면 이러한 Deadlock을 방지하는 방법에는 어떤 것들이 있을까?

 

<그림 3> Deadlock의 도식화

 

오라클의 락(Lock) 종류

 

오라클에서는 락의 종류가 크게 세 가지로 구분된다.

락의 종류
설명
참조 뷰
DML Lock INSERT, UPDATE,MERGE, DELETE
등의 DML 작업 시 발생. 지정된 로우
레벨 또는 테이블 레벨(테이블의 모든
로우의 Lock을 잡는 경우)로 발생		 dba_dml_locks
DDL Lock CREATE, ALTER,COMPLIE 등의
DDL 작업에 의해 발생(일반적으로
오브젝트 구조 정의를 보호하기 위한
LOCK)		 Dba_ddl_locks
Latch, Mutex 오라클의 메모리 구조를 보호하기
위한 가벼운 lock(latch),
(10g R2)

V$latch,

Wait event 뷰

 

 

● INITRANS의 추가

동일 블록 당 진행할 수 있는 트랜잭션의 개수를 지정하는 옵션인 INTRANS 값이 부족하면 Deadlock이 발생할 수 있다. 이 값이 부족하면 서로 다른 트랜잭션이 각기 동일 테이블의 각기 다른 데이터를 수정하더라도 그 데이터가 동일 블록에 위치한다면 Deadlock이 발생할 수 있다. 따라서 하나의 테이블에 대해 퍼포먼스 등의 이유로 동시에 다른 데이터를 처리하는 트랜잭션을 수행하고자 한다면, 테이블의 INTRANS 값을 크게 해서, 하나의 블록에 여러 개의 트랙잭션이 DML 처리를 수행하더라도 공간이 부족해서 기다리는 상황은 없도록 해야 한다.

 

● 적은 블록 사이즈와 블록당 적은 데이터

너무 큰 블록 사이즈(표준 블록 사이즈 8K보다 큰)를 사용한다거나 하나의 블록에 많은 데이터가 들어 있다면 BLOCK LEVEL 락이 발생할 가능성이 크다. 따라서 pctfree를 약간 크게 잡음으로써 의도적으로 블록 당 로우수를 줄이는 방법도 그 대안이 될 수 있다. 될 수 있으면 표준 블록사이즈를 사용하거나 필요하다면 오라클의 멀티블록 사이즈를 이용해 2K, 4K 적은 블록 사이즈의 이용을 고려해본다.

>> 각주:PCTFREE: 업데이트 시 사이즈가 늘어날 것을 대비한 BLOCK의 여유공간 비율. 일반적으로 10%. 이 값을 크게 잡는다면 블록당 들어가는 로우 사이즈는 줄어들 것이다.

 

● 해당 테이블에 적절한 인덱스의 존재 여부 확인

오라클 데이터베이스에는 해당되지 않지만 SQL 서버의 경우에는 INDEX SCAN이 아닐 경우 Lock escalation(락의 확대)울 수행해서 테이블 락으로 확대해 버린다. 오라클은 Lock escalation은 발생하지 않지만 FK, PK 관계의 부모 자식 테이블에서 부모 테이블의 로우를 삭제할 경우 인덱스가 없다면 자식 테이블 전체가 Shared Lock이 발생해버린다(이러한 사항은 8i 이전의 문제였고 Oracle 9i 이상이라면 FK에 INDEX가 존재하지 않아도 어떠한 락도 발생하지 않는 알고리즘으로 변화).

 

● 트랜잭션 처리의 최적화

SELECT FOR UPDATE로 LOCKING된 레코드의 처리에 너무 많은 로직이 들어간다거나 처리되는 DML 문이 최적화되지 못해 락의 보유시간이 길어지는 것을 방지해야 한다. 또한 정합성을 지나치게 중시해 과도하게 넓은 범위를 LOCKING하는 경우를 가능하면 줄이도록 한다.

● 애플리케이션 튜닝

업데이트와 관련된 싱글 스레딩으로 변경하는 것을 고려할 수 있다. 또한 업데이트 작업과 관련된 배치작업을 리스케줄링하는 것도 고려할 수 있다. 가능한 조치를 했음에도 불구하고 이러한 상황이 발생했다는 것은 제대로 된 애플리케이션 프로세스 모델링과 데이터베이스 모델링이 있지 않다는 증거이다. 가급적 이러한 상황을 막기 위해서는 무엇보다도 업무단위에 대한 철저한 분석과 설계가 선행되어야 한다.

 

 

Deadlock 분석을 위한 팁

 

Deadlock이 발생한다면 alert.log에 해당 로그가 기록되지만 원인과 주체를 분석하기에는 정보가 부족한 것이 사실이다. 따라서 해당되는 Deadlock의 자세한 사항을 수집할 수 있도록 10027 이벤트를 추가적 으로 걸어줘서 해당주체를 분석할 수 있다. 레벨에 따라서 call stack, action, process state, SQL 문장 등의 정보를 획득할 수 있다. 다만 DBA 권한을 가진 유저만이 수행할 수 있다. 다음은 4 레벨로 call stack trace를 생성하게끔 수행한 예이다.

 

SQL>ALTER SYSTEM SET EVENTS‘ 10027 trace name context
forever, level 4’

 

 

● SELECT FOR UPDATE(비관적 잠금)의 동시성 증대

일반적으로 동시성 향상보다도 정합성에 초점을 두거나 LOST UPDATE를 방지하기 위한 목적으로 FOR UPDATE 옵션으로서 SELECT되는 해당 로우에 대한 잠금을 명시적으로 실행하는 방법을 많이 쓰고 있으리라고 생각된다. 그러나 이러한 명시적인 잠금은 잘 쓰면 정합성을 유지하는데 약이 되지만 잘못 쓸 경우 엄청난 동시성 저해 요소가 될 수 있다.

 

만일 다중 로우에 대한 락을 걸 때 한 건의 로우가 기존에 락이 걸린 상태면 전체가 잠금을 시도하는데 있어서 에러가 발생한다. 따라서 넓은 범위의 로우를 Locking하고 싶은 경우 이 한 건의 락이 해제될 때까지 기다려야 하는 상황이 발생한다.

리조트 예약시스템의 경우를 예로 들어보자. 단체 손님을 받아서 10층의 룸을 전부 락을 건 채 예약 작업을 시도한다고 가정하자. 그러나 옆의 다른 직원이 동일 층의 개인 손님 예약을 이미 락을 걸고 작업 중이면 앞의 직원은 Locking 작업은 실패할 것이며 이전 직원의 작업이 끝나기를 기다려야 할 것이다.

여기에 이러한 상황에 적합한 SELECT FOR UPDATE SKIP LOCKED라는 기존 Locking 범위를 Skip하는 추가적인 옵션이 존재한다. 이 방법은 9i부터 지원되는 undocumented된 방법이며 과거부터 메시지 처리를 위한 AQ(Advanced Queuing) 기능을 위해 존재했던 기능이기도 하다. 이 기능을 이용한 예제를 살펴보자.

 

 

<그림 4> SKIP LOCKED를 사용한 LOCK의 확대

 

유의해야 할 것은 이 기능은 Oracle 11g부터 정식으로 매뉴얼 상에 등장하는 기능이므로 이전 버전에서는 몇 가지 불안정한 면이 존재했다. 따라서 오라클 데이터베이스의 동시성 향상을 위한 예로서 소개한 기능이므로 실 환경에서의 적용보다는 테스트에 적용해 사용하길 바란다.

 

짧게나마 간단한 예제로서 동시성 제어와 관련된 사항과 이에 대한 방법론에 대해 살펴봤다. 동시성 제어는 매우 중요함에도 불구하고 프로젝트 수행 중에 만났던 많은 개발자들이 아직 많이 간과하는 부분이었다.

 

DBMS_LOCK을 이용한 사용자 정의 lock(User-defined lock)

 

사용자가 임의로 특정 LOCK을 재현하기 위해서는 오브젝트 생성과 특정 트랜잭션이 필요하지만 DBMS_LOCK을 사용해 사용자 정의 lock을 구현할 수 있다.
몇 가지 UL을 잘 사용한다면 특정 사항을 재현해낸다거나 procedure 내에서 특정 구간의 작업을 지연시켜 변동사항을 확인 가능한 유용한 방법이 될 수 있다.

 

- DBMS_LOCK.SLEEP(n) : n초 만큼 대기
- DBMS_LOCK.REQUEST : 지정된 lockmode로 LOCK을 획득
- DBMS_LOCK.RELEASE : 락을 해제
- DBMS_LOCK.ALLOCATE_UNIQUE : LOCK 명을 LOCK HANDLE로 컨버전

 

 

 

 

필자소개

 

김도근 kilgw@naver.com｜OracleAce, OCM, MCDBA ‘데이터베이스란 OS 위에 올라가 있는 애플리케이션에 불과하다' 라는 생각으로 항상 데이터베이스를 생각하면서 데이터베이스를 공부하는 것을 낙으로 삼고 있다. 스터디 및 온라인, 오프라인 기고 등의 활동을 전개 중이다.

 

출처 : 한국 마이크로 소프트웨어 [2009년 7월호]

제공 : DB포탈사이트 DBguide.net


=================================================================================================================
=================================================================================================================

출처 : http://www.gurubee.net/pages/viewpage.action?pageId=22904839

동시성 구현 사례

학습 목표
  • 일련번호 채번, 선분이력 등의 동시성 구현 사례를 살펴보고 완벽하게 이해한다

일련번호 채번 동시성 높이기

DBMS가 제공하는 Sequence 기능을 사용하는 것이 가장 좋으나, 이를 사용할 수 없는 경우 별도 구현해야 함

(1) 데이터가 삽입되는 시점에 실시간으로 현재의 MAX 값을 취해 1만큼 증가시킨 값을 이용하는 방식
  • 두 개의 트랜젝션이 동시에 같은 값을 읽었을 경우 insert 하려는 순간 PK 제약 위배됨
  • Exception 처리를 통해 동시성 제어
(2) MAX 값을 관리하는 별도의 채번 테이블에서 값을 가져오는 방식
  • 채번 테이블 생성 및 채번 함수 정의 
    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;
  return l_new_seq;
end;
/
  • 앞서 정의한 테이블 및 함수를 사용한 트랜젝션 예시 
    begin
  update tab1
     set col1 = :x
   where col2 = :y ;

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

  loop
    -- do anything ...
  end loop;

  commit;

exception
  when others then
  rollback;
end;
/
  • pragma autonomous_transaction 옵션을 사용하지 않은 경우는?
    • 메인 트랜젝션의 insert 구문 이후에 롤백이 되는 경우 앞의 update문까지 이미 커밋된 상태로 되어 데이터 일관성이 깨짐
    • DB2 9.7 버전부터 지원
    • SQL Server에서는 지원하지 않음. Savepoint 활용 권고.
  • seq_nextval 함수에서 커밋을 안하는 경우는?
    • 메인 트랜젝션이 종료될 때까지 채번 테이블에 Lock이 걸린 상태가 되어 성능저하 초래

선분이력 정합성 유지

선분이력을 추가하고 갱신할 때 발생할 수 있는 동시성 이슈를 해결한 사례

  • 선분이력모델은 여러 장점이 있지만 잘못하면 데이터 정합성이 쉽게 깨질 수 있는 단점 존재
  • 정확히 핸들링하는 방법을 알아야 한다

  • 기본 최종 선분이력을 끊고 새로운 이력 레코드를 추가하는 전형적인 처리 루틴 
    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 됨
  • select for update문이 없다면?
    • 첫 번째 트랜젝션이 ①을 수행하고 ②로 진입하기 직전에 두 번째 트랜젝션이 동일 이력에 대해 ①~④를 먼저 진행해 버린다면 선분이력이 깨짐
    • 트랜젝션이 순차적으로 진행할 수 있도록 직렬화 장치 필요 : select for update문을 이용해 해당 레코드에 Lock을 설정
  • 부가서비스이력 테이블에만 select for update로 Lock을 거는 경우?
    • 기존에 부가서비스이력이 전혀 없던 고객인 경우 Lock이 걸리지 않음
    • 동시에 두 개 트랜젝션이 ③번 insert문으로 진입하여 시작일시는 다르면서 종료일시는 같은 두 개의 이력 레코드 생성
    • 상위엔티티인 고객 테이블에 select for update로 Lock을 걸어 완벽하게 동시성 제어
    • 다른 상위엔티티인 부가서비스 테이블에 Lock을 걸 수도 있지만, 여러 사용자가 동시에 접근할 가능성이 크기 때문에 동시성이 나빠질 수 있으므로 고객 테이블에 Lock 설정

 

 

+ Recent posts

티스토리툴바