메뉴 건너뛰기

bysql.net

제3절_동시성_제어

2012.05.08 08:23

운영자 조회 수:6036

 

 

* 동시성 제어(Concurrency Control)란 

   :  동시에 작동하는 다중 트랜잭션의 상호 간섭 작용에서 데이터베이스 보호

 

- 모든 DBMS가 공통적으로 Lock 기능 제공 (여러 사용자가 데이터를 동시에 액세스하는 것처럼 보이지만 내부적으로는 하나씩 실행되도록 트랜잭션을 직렬화하는 것이다. )

 

- set transaction 명령어를 이용해 트랜잭션 격리성 수준을 조정할 수 있는 기능 제공 (DBMS마다 구현 방식이 다름)

* SQL Server :  기본 트랜잭션 격리성 수준인 Read Committed 상태에선 레코드를 읽고 다음 레코드로 이동하자마자 공유 Lock을 해제하지만, Repeatable Read로 올리면 트랜잭션을 커밋될 때까지 공유 Lock을 유지한다.

 

 

동시성(Concurrency)과 일관성(Consistency)의 관계  

      - Lock 사용 최소화  :  동시성 ↑ , 일관성 유지 어려움

      - Lock 사용 최대화  :  일관성 ↑ , 동시성 저하

 

* 동시에 실행되는 트랜잭션 수를 최대화하면서도 입력, 수정, 삭제, 검색 시 데이터 무결성이 유지되도록 한다.

 

 

 * DBMS가 제공하는 set transaction 명령어로써 모든 동시성 제어 문제를 해결할 수 없다 ( n-Tier 아키텍처가 지배적인 요즘 같은 애플리케이션 환경에서 특히)

 

예를 들어, 사용자가 자신의 계좌에서 잔고를 확인하고 인출을 완료할 때까지의 논리적인 작업 단위를 하나의 트랜잭션으로 처리하고자 할 때, 잔고를 확인하는 SQL과 인출하는 SQL이 서로 다른 연결(Connection)을 통해 처리될 수 있기 때문이다. DB와 연결하기 위해 사용하는 라이브러리나 그리드(Grid) 컴포넌트가 동시성 제어 기능을 제공하기도 하지만, 많은 경우 트랜잭션의 동시성을 개발자가 직접 구현해야만 한다.

 

1. 비관적 동시성 제어 vs. 낙관적 동시성 제어  :  동시성 제어 기법

가. 비관적 동시성 제어

사용자들이 같은 데이터를 동시에 수정할 것이라고 가정 → 데이터를 읽는 시점에 Lock을 걸고 트랜잭션이 완료될 때까지 유지

 

select 적립포인트, 방문횟수, 최근방문일시, 구매실적
from 고객
where 고객번호 = :cust_num for update;

 

-- 새로운 적립포인트 계산

 

update 고객 set 적립포인트 = :적립포인트 where 고객번호 = :cust_num;

 

      - 시스템 동시성을 심각하게 떨어뜨릴 우려가 있으므로 wait 또는 nowait 옵션을 함께 사용하는 것이 바람직

 

 for update nowait → 대기없이 Exception을 던짐
for update wait 3 → 3초 대기 후 Exception을 던짐
 

      * SQL Server  :  for update절을 사용할 수 있지만 커서를 명시적으로 선언할 때만 가능. holdlock이나 updlock 힌트를 사용하는 것이 편리

 

나. 낙관적 동시성 제어

사용자들이 같은 데이터를 동시에 수정하지 않을 것이라고 가정 → 데이터를 읽을 때는 Lock을 설정하지 X

수정 시점에 다른 사용자에 의해 값이 변경됐는지를 반드시 검사

  

select 적립포인트, 방문횟수, 최근방문일시, 구매실적 into :a, :b, :c, :d
from 고객
where 고객번호 = :cust_num;

 

-- 새로운 적립포인트 계산

 

update 고객 set 적립포인트 = :적립포인트
where 고객번호 = :cust_num
and 적립포인트 = :a
and 방문횟수 = :b
and 최근방문일시 = :c
and 구매실적 = :d ;

 

if sql%rowcount = 0 then
     alert('다른 사용자에 의해 변경되었습니다.');
end if;

 

최종 변경일시를 관리하는 칼럼이 있다면, 아래와 같이 좀 더 간단하게 구현할 수 있다.

 
select 적립포인트, 방문횟수, 최근방문일시, 구매실적, 변경일시
into :a, :b, :c, :d, :mod_dt
from 고객
where 고객번호 = :cust_num;
-- 새로운 적립포인트 계산
update 고객 set 적립포인트 = :적립포인트, 변경일시 = SYSDATE
where 고객번호 = :cust_num
and 변경일시 = :mod_dt ;    →   최종 변경일시가 앞서 읽은 값과 같은지 비교
 
 

2. 다중버전 동시성 제어

가. 일반적인 Locking 메커니즘의 문제점

- 읽기 작업에 공유 Lock을 사용  :  읽기 작업과 쓰기 작업이 서로 방해해 동시성, 데이터 일관성에 문제 발생 

  해결하려면 Lock을 더 오랫동안 유지하거나 테이블 레벨 Lock을 사용해야 하므로 동시성을 더 심각하게 떨어뜨리는 결과를 낳는다.

 

< 비일관성 읽기 문제 >

 

- 잔고 총합을 구하는 아래 쿼리가 TX1 트랜잭션에서 수행

TX1> select sum(잔고) from 계좌 ;

- 잠시 후, 계좌이체를 처리하는 아래 TX2 트랜잭션도 작업 시작

TX2> update 계좌 set 잔고 = 잔고 + 100 where 계좌번호 = 7; -- ①
TX2> update 계좌 set 잔고 = 잔고 - 100 where 계좌번호 = 3; -- ②
TX2> commit;

1. TX1 : 2번 계좌까지 읽는다. 현재까지의 잔고 총합은 2,000원이다.
2. TX2 : ①번 update를 실행한다. 7번 계좌 잔고는 1,100원이 되었고, 아직 커밋되지 않은 상태다.
3. TX1 : 6번 계좌까지 읽어 내려간다. 현재까지의 잔고 총합은 5,000원이다.
4. TX2 : ②번 update를 실행함으로써 3번 계좌는 900원, 7번 계좌는 1,100인 상태에서 커밋한다.
5. TX1 : 10번 계좌까지 읽어 내려간다. 7번 계좌 잔고를 1,100으로 바꾼 TX2 트랜잭션이 커밋되었으므로 이 값을 읽어서 구한 잔고 총합은 10,100이 된다.

 

==> 기본 트랜잭션 격리성 수준(Read Committed)  :  값을 읽는 순간에만 공유 Lock을 걸었다가 다음 레코드로 이동할 때 Lock을 해제함으로 인해 위와 같은 현상이 발생

 

 

< 해법 문제 >

 

트랜잭션 격리성 수준을 상향 조정 (TX1 쿼리가 진행되는 동안 읽은 레코드는 공유 Lock이 계속 유지되며, 심지어 쿼리가 끝나고 다음 쿼리가 진행되는 동안에도 유지)

→ 일관성이 높아지지만, Lock이 더 오래 유지됨으로 인해 동시성을 저하시키고 교착상태가 발생할 가능성도 커진다.

 

TX2가 ①번 update를 통해 7번 레코드에 배타적 Lock을 설정하고 TX1은 3번 레코드에 공유 Lock을 설정한다.

TX2는 ②번 update를 실행하는 단계에서 3번 레코드에 걸린 공유 Lock을 대기하게 되고, TX1이 7번 레코드를 읽으려는 순간 영원히 Lock이 풀릴 수 없는 교착상태에 빠진다. 이 때문에 테이블 레벨 Lock을 사용해야만 할 수도 있고, 이는 동시성을 더 심하게 저하시킨다.

 

 

 

나. 다중버전 동시성 제어        :   일반적 Locking 메커니즘의 문제점을 해결하려고 사용

  

  • 데이터를 변경할 때마다 그 변경사항을 Undo 영역에 저장해 둔다
  • 데이터를 읽다가 쿼리(또는 트랜잭션) 시작 시점 이후에 변경된(변경이 진행 중이거나 이미 커밋된) 값을 발견하면,
  • Undo 영역에 저장된 정보를 이용해 쿼리(또는 트랜잭션) 시작 시점의 일관성 있는 버전(CR Copy)을 생성하고 그것을 읽는다.

     - 장점  : 변경이 진행 중인 레코드(배타적 Lock)를 만나더라도 대기하지 않기 때문에 동시성 측면에서 매우 유리.

                   사용자에게 제공되는 데이터의 기준 시점이 쿼리(또는 트랜잭션) 시작 시점으로 고정(일관성 측면에서도 유리)

     

     - 단점  : Undo 블록 I/O, CR Copy 생성, CR 블록 캐싱 같은 부가적인 작업 때문에 생기는 오버헤드도 무시할 수 없다.

     

    * Oracle : 버전 3부터 / MS SQL Server : 2005 버전부터 / IBM DB2 :  9.7 버전부터 사용

    * 참고)   Oracle : Undo 데이터를 Undo 세그먼트에 저장  /  SQL Server  :  tempdb에 저장

     

     

     

    다. 문장수준 읽기 일관성(Statement-Level Read Consistency)

    : 다른 트랜잭션에 의해 데이터의 추가, 변경, 삭제가 발생하더라도 단일 SQL문 내에서 일관성 있게 값을 읽는 것을 말한다.

      (일관성 기준 시점 : 쿼리 시작 시점)

     

    [그림 Ⅲ-2-4]는 10023 시점에 시작된 쿼리가 10023 시점 이후에 변경된 데이터 블록을 만났을 때, Rollback(=Undo) 세그먼트에 저장된 정보를 이용해 10023 이전 시점으로 되돌리고서 값을 읽는 것을 표현하고 있다.

     

     

     

    * SQL Server에서 문장수준 읽기 일관성 모드로 DB를 운영하려면 아래 명령을 수행해 주면 된다.

     

    alter database <데이터베이스 이름> set read_committed_snapshot on;


    라. 트랜잭션 수준 읽기 (Transaction-Level Read Consistency)

    다른 트랜잭션에 의해 데이터의 추가, 변경, 삭제가 발생하더라도 트랜잭션 내에서 일관성 있게 값을 읽는 것을 말한다.

     

    기본 트랜잭션 격리성 수준(Read Committed)에서 완벽한 문장수준의 읽기 일관성을 보장하는 MVCC 메커니즘도 트랜잭션 수준의 읽기 일관성은 보장하지 않는다. 물론 일반적인 Locking 메커니즘도 트랜잭션 수준의 읽기 일관성은 보장하지 않는다.

     

    트랜잭션 수준으로 완벽한 읽기 일관성을 보장받으려면 격리성 수준을 Serializable Read로 올려주어야 한다.

    트랜잭션 격리성 수준을 Serializable Read로 상향 조정하면, 일관성 기준 시점은 트랜잭션 시작 시점이 된다. 물론 트랜잭션이 진행되는 동안 자신이 발생시킨 변경사항은 그대로 읽는다.

     

    * SQL Server에서 트랜잭션 읽기 일관성 모드로 DB를 운영하려면 먼저 아래 명령을 수행해 주어야 한다.

     

    lter database <데이터베이스 이름> set allow_snapshot_isolation on;

     

    그리고 트랜잭션을 시작하기 전에 트랜잭션 격리성 수준을 아래와 같이 ‘snapshot’으로 변경해 주면 된다.

     

    set transaction isolation level snapshot
    begin tran
         select ... ;
         update ... ;
    commit;


    마. Snapshot too old (MVCC에서 발생)

    - Undo 영역에 저장된 Undo 정보가 다른 트랜잭션에 의해 재사용돼 필요한 CR Copy을 생성할 수 없을 때 발생

      (좀 더 세부적인 메커니즘으로 들어가면 블록 클린아웃에 실패했을 때도 발생하는데, 이에 대한 설명은 생략하기로 한다.)

     

    - 발생 가능성을 줄이기 위해 DBMS 벤더 측의 노력이 계속되고 있지만 아직 완벽한 해결책은 마련되지 못하고 있다.

      따라서 이를 회피하기 위한 DBA 또는 개발자의 노력이 여전히 필요한 상태

     

    * Snapshot too old 에러 발생 가능성을 줄이는 방법

     

    1. Undo 영역의 크기를 증가시킨다.
    2. 불필요하게 커밋을 자주 수행하지 않는다.
    3. fetch across commit 형태의 프로그램 작성을 피해 다른 방식으로 구현한다. ANSI 표준에 따르면 커밋 이전에 열려 있던 커서는 더는 Fetch 하면 안 된다. 다른 방식으로 구현하기 어렵다면 커밋 횟수를 줄여본다.
    4. 트랜잭션이 몰리는 시간대에 오래 걸리는 쿼리가 같이 수행되지 않도록 시간을 조정한다.

    5. 큰 테이블을 일정 범위로 나누어 읽고 단계적으로 실행할 수 있도록 코딩한다. Snapshot too old 발생 가능성을 줄일 뿐 아니라 문제가 발생했을 때 특정 부분부터 다시 시작할 수도 있어 유리하다. 물론 그렇게 해도 읽기 일관성에 문제가 없을 때에만 적용해야 한다.
    6. 오랜 시간에 걸쳐 같은 블록을 여러 번 방문하는 Nested Loop 형태의 조인문 또는 인덱스를 경유한 테이블 액세스를 수반하는 프로그램이 있는지 체크하고, 이를 회피할 수 있는 방법(조인 메소드 변경, Full Table Scan 등)을 찾는다.
    7. 소트 부하를 감수하더라도 order by 등을 강제로 삽입해 소트연산이 발생하도록 한다.
    8. 대량 업데이트 후에 곧바로 해당 테이블 또는 인덱스를 Full Scan 하도록 쿼리를 수행하는 것도 하나의 해결방법이 될 수 있다.

    select /*+ full(t) */ count(*) from table_name t  
    select count(*) from table_name where index_column > 0


     
     
    번호 제목 글쓴이 날짜 조회 수
    58 Week1_박우창 [1] balto 2012.07.17 9892
    57 Week1_이주영 suspace 2012.07.17 5025
    56 Week1_승대수 보라빛고양이 2012.07.17 3053
    55 Week1_이진우 [5] ljw 2012.07.16 46262
    54 Week1_위충환 [1] 실천하자 2012.07.16 9272
    53 문제작성 실천하자 2012.07.16 5794
    52 제5절_배치_프로그램_튜닝 보라빛고양이 2012.07.04 6906
    51 제4절_파티션_활용 오예스 2012.06.26 5753
    50 제3절_DML_튜닝 balto 2012.06.13 9073
    49 제1절_고급_SQL_활용 실천하자 2012.06.11 6285
    48 제1절 고급 SQL 활용 실천하자 2012.06.11 8397
    47 제4절_고급_조인_기법 suspace 2012.06.05 31231
    46 제2절_소트_튜닝 file ljw 2012.06.04 12024
    45 제3절 조인 기본 원리 보라빛고양이 2012.05.30 6980
    44 제1절_인덱스_기본_원리 오예스 2012.05.22 7100
    43 제2절_인덱스_튜닝 balto 2012.05.18 9904
    42 제2절_쿼리변환 ljw 2012.05.14 5613
    41 제1절_옵티마이저 실천하자 2012.05.12 7185
    » 제3절_동시성_제어 운영자 2012.05.08 6036
    39 제1절_Lock balto 2012.05.05 25258