DA 가이드

DA, SQL, DB보안 등 실무자를 위한 위한 DB기술 바이블!

트랜잭션

DB설계와 이용
데이터베이스 이용
트랜잭션
작성자
admin
작성일
2021-02-10 16:08
조회
3294

트랜잭션은 ATM이나 데이터베이스 등의 시스템에서 더 이상 나눌 수 없는 업무 처리의 단위로, 하 나 이상의 SQL문으로 구성된다. 여기에서 더 이상 나눌 수 없다는 것은 실제로 나눌 수 없다기보다는 나눌 경우 시스템이나 데이터에 심각한 오류를 초래할 수 있다는 의미이다. 이러한 개념의 기능을 데이 터베이스에서 제공하는 것을 트랜잭션이라고 하며, ACID와 같은 기술적인 요건을 충족해야만 한다.

그런데 다중 사용자 환경의 데이터베이스에서 트랜잭션의 개념만 충족한다고 데이터의 일관성을 유 지할 수 있는 것은 아니다. 다중 사용자 환경의 데이터베이스 관리 시스템들은 여러 사용자의 질의나 프 로그램을 동시에 수행하므로 Dirty Read, Non-Repeatable Read, Phantom Read 등의 문제가 발 생할 수 있다. 때문에 트랜잭션들이 동시에 수행될 경우 각 트랜잭션이 고립적으로 수행된 것과 동일한 결과를 내려면 트랜잭션들이 서로 간섭을 일으키는 현상을 최소화하고 데이터의 일관성과 무결성을 보 장하도록 트랜잭션을 제어해야 하는데, 이러한 기능을 트랜잭션의 동시성 제어라 한다.

이 외에 데이터의 일관성을 유지하기 위해서는 트랜잭션 처리 중 장애가 발생했을 경우 데이터를 트 랜잭션이 시작되기 이전 상태로 되돌려 놓는 기능이 필요한데, 이러한 기능을 고장 회복(Recovery)이 라 한다.


트랜잭션 관리

트랜잭션은 하나의 논리적 작업 단위를 구성하는 하나 이상의 SQL문으로 구성되며, 모든 트랜잭 션은 두 가지 상황으로 종료된다. 실행한 논리적 작업 단위 전체가 성공적으로 종료되면 그 트랜잭션 은 영구적으로 데이터베이스에 저장된다. 이것을 COMMIT이라 한다. 다른 한 가지는 실행한 SQL 중 하나라도 정상 종료되지 않으면 논리적인 작업 단위 전체를 이전 상황으로 rollback한다.

다중 사용자 환경에서 트랜잭션은 동시성 제어(Concurrency control)와 고장 회복(recovery) 기 법에 의하여 관리된다. 동시성 제어는 한 사용자의 작업이 다른 사용자의 작업에 의해 방해 받지 않 도록 하는 조치들로 구성되고, 고장 회복은 데이터 처리 중 통신, 하드웨어, 소프트웨어 오류 발생 등 예기치 않은 예외 상황에 대한 조치들로 구성된다.


트랜잭션 특성

원자성(Atomicity)

하나의 트랜잭션은 하나의 원자적 수행이다. 트랜잭션은 완전히 수행하거나 전혀 수행되지 않은 상태로 회복되야 한다. 계좌이체에서 송신 계좌에서 출금과 수신 계좌에 입금은 전체가 완전하게 수행되어야 한다.


일관성 유지(Consistence)

트랜잭션을 실행하면 데이터베이스를 하나의 일관된 상태에서 또 다른 일관된 상태로 바뀐다. 일관성은 프로그래머나 무결성 제약 조건을 시행하는 DBMS에서 처리된다.


고립성(Isolation)

하나의 트랜잭션은 완료될 때까지 자신이 갱신한 값을 다른 트랜잭션들이 보게 해서는 안된다. 고립성이 시행되므로 임시 갱신 문제를 해결하며 트랜잭션들의 연쇄 복귀는 불필요하다. 고립성은 갱신에 따른 손실이 없어야 하며 오손 판독이 없고 반복 읽기 성질을 갖는다.


영속성(Durability)

단 한 트랜잭션이 데이터베이스를 변경시키고 그 변경이 완료되면 결과는 이후의 어떠한 고장에도 손실되지 않아야 한다. 지속성을 보장하는 것은 회복 기법의 책임이다.


트랜잭션의 일관성

트랜잭션 수준 읽기 일관성(Transaction-Level Read Consistency)은 트랜잭션이 시작된 시점 을 기준으로 일관성 있게 데이터를 읽는 것을 말한다. 트랜잭션이 진행되는 동안 다른 트랜잭션에 의 해 변경이 발생하더라도 이를 무시하고 트랜잭션 내에서 계속 일관성 있는 데이터를 보고자 하는 업 무 요건이 있을 수 있다. 물론 트랜잭션이 진행되는 동안 자신이 발생시킨 변경 사항은 읽을 수 있어 야 한다.

대부분 DBMS가 기본적으로 트랜잭션 수준 읽기 일관성을 보장하지 않으며, 트랜잭션 수준으로 읽기 일관성을 강화하려면 고립화 수준을 다음과 같이 높여 주어야 한다.

set transaction isolation level serializable;


낮은 단계 트랜잭션 고립화 수준에서 발생할 수 있는 현상들
Dirty Read(= Uncommitted Dependency)

다른 트랜잭션이 변경 중인 데이터를 읽었는데, 그 트랜잭션이 최종 롤백됨으로써 현재 트랜잭션이 비일관성(inconsistency) 상태에 놓이는 것을 말한다.


Non-Repeatable Read(= Inconsistent Analysis)

한 트랜잭션 내에서 같은 쿼리를 두 번 수행할 때 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제 함으로써 두 쿼리의 결과가 상이하게 나타나는 현상을 말한다.


Phantom Read

한 트랜잭션 내에서 같은 쿼리를 두 번 수행할 때 첫 번째 쿼리에서 없던 유령(Phantom) 레코드가 두 번째 쿼리에서 나타나는 현상을 말한다.


트랜잭션 고립화 수준(Transaction Isolation Level)

ANSI/ISO SQL standard(SQL92)에서 정의하고 있는 네 가지 트랜잭션 고립화 수준을 요약하면 다음과 같다.


레벨 0(= Read Uncommitted)

트랜잭션에서 처리 중인 아직 커밋되지 않은 데이터를 다른 트랜잭션이 읽는 것을 허용한다. Dirty Read, Non-Repeatable Read, Phantom Read 현상 발생


레벨 1(= Read Committed)

대부분의 DBMS가 기본 모드로 채택하고 있는 일관성 모드로서, 트랜잭션이 커밋되어 확정된 데 이터만 읽는 것을 허용한다. Non-Repeatable Read, Phantom Read 현상 발생


레벨 2(= Repeatable Read)

선행 트랜잭션이 읽은 데이터는 트랜잭션이 종료될 때까지 후행 트랜잭션이 갱신하거나 삭제하는 것을 불허함으로써 같은 데이터를 두 번 쿼리했을 때 일관성 있는 결과를 리턴한다. Phantom Read 현상 발생


레벨 3(= Serializable Read)

선행 트랜잭션이 읽은 데이터를 후행 트랜잭션이 갱신하거나 삭제하지 못할 뿐만 아니라 중간에 새로운 레코드를 삽입하는 것도 막아줌으로써 완벽한 읽기 일관성을 제공한다. 참고로 오라클은 잠김을 사용하지 않고 Undo 데이터를 이용해 Serializable Read를 구현한다.


동시성 제어(Concurrency Control)

동시성 제어(Concurrency Control)란 다수의 사용자가 데이터베이스에 동시에 접근하여 같은 데이터를 조회 또는 갱신을 할 때 데이터 일관성을 유지하기 위한 일련의 조치를 의미한다. 여기서 데이터 동시성(Data Concurrency)이란 다수의 사용자가 동시에 데이터에 접근할 수 있어야 한다 는 의미이고, 데이터 일관성(Data Consistency)이란 각각의 사용자가 자신의 트랜잭션이나 다른 사람의 트랜잭션에 변경된 내용을 포함하여 일관된 값을 본다는 의미이다. 동시성 제어는 낙관적 동 시성 제어(Optimistic Concurrency Control)와 비관적 동시성 제어(Pessimistic Concurrency Control)로 나뉜다. 낙관적 동시성 제어 알고리즘은 다수 사용자가 동시에 같은 데이터에 접근할 경 우가 적다고 보고 구현한 알고리즘이고, 비관적 동시성 제어는 다수 사용자가 동시에 같은 데이터에 접근할 경우가 많다고 보고 구현한 알고리즘이다.


낙관적 동시성 제어

낙관적 동시성 제어(Optimistic Concurrency Control)는 사용자들이 같은 데이터를 동시에 수 정하지 않을 것이라고 가정한다. 따라서 데이터를 읽을 때는 잠김을 설정하지 않는다. 그러나 낙관적 입장에 섰더라도 동시 트랜잭션에 의한 데이터의 잘못된 갱신에 주의를 기울여야 한다. 읽는 시점에는 잠김을 사용하지 않지만 데이터를 수정하고자 하는 시점에 앞서 읽은 데이터가 다른 사용자에 의해 변경되었는지를 반드시 검사해야 하는 것이다.



  • select 적립포인트, 방문횟수, 최근방문일시, 구매실적, 변경일시
  • into :a, :b, :c, :d, :mod_dt
  • from 고객
  • where 고객번호 = :cust_num;
  • -- 새로운 적립포인트 계산
  • update 고객 set 적립포인트 = :적립포인트, 변경일시 = SYSDATE
  • where 고객번호 = :cust_num
  • and 변경일시 = :mod_dt ; → 최종 변경 일시가 앞서 읽은 값과 같은지 비교
  • if sql%rowcount = 0 then
  • dbms_output.put_line('다른 사용자에 의해 변경되었습니다.');
  • end if;

낙관적 동시성 제어를 사용하면 잠김이 유지되는 시간이 매우 짧아져 동시성을 높이는 데 유리하다.


비관적 동시성 제어

비관적 동시성 제어(Pessimistic Concurrency Control)는 사용자들이 같은 데이터를 동시에 수 정할 것이라고 가정한다. 따라서 한 사용자가 데이터를 읽는 시점에 잠김을 걸고 조회 또는 갱신 처 리가 완료될 때까지 이를 유지한다. 구체적으로 말해 다음과 같이 for update절을 사용해 select 시 점에 해당 레코드에 잠김을 걸어두는 식이다.



  • select 적립포인트, 방문횟수, 최근방문일시, 구매실적 from 고객
  • where 고객번호 = :cust_num for update;
  • -- 새로운 적립포인트 계산
  • update 고객 set 적립포인트 = :적립포인트 where 고객번호 = :cust_num;

잠김은 첫 번째 사용자가 트랜잭션을 완료하기 전까지 다른 사용자들이 그 데이터를 수정할 수 없 게 만들기 때문에 비관적 동시성 제어는 자칫 시스템 동시성을 심각하게 떨어뜨릴 우려가 있다. 이를 방지하려면 다음과 같이 wait 또는 nowait 옵션을 함께 사용해야 한다.



  • for update nowait → Lock이 걸렸다면 대기 없이 Exception을 던짐
  • for update wait 3 → Lock이 걸렸다면 3초간 대기하고 Exception을 던짐

동시성 제어 기능

다중 사용자 환경에서는 트랜잭션들의 동시성을 제어하기 위해 Locking, 2PC, Timestamp 등의 기법을 주로 사용한다. 잠김(Locking)은 트랜잭션의 동시성을 제어하기 위해 가장 많이 사용되는 기 법으로 데이터 처리 과정에 있는 데이터를 읽지 못하게 하는 기법이다. 잠김은 암시적인 잠김 (Implicit Locking)과 명시적인 잠김(Explicit Locking)으로 구분된다. 암시적인 잠김은 DDL(Data Definition Language)를 실행할 때와 같이 DBMS에 의해 자동으로 실시된다. 명시적 인 잠김은 사용자에 의한 트랜잭션 제어(Transaction Control)에 의해 실시된다.


잠김 단위(Lock Granularity 또는 Isolation Level)

잠김 단위는 잠김 대상의 크기를 뜻하며, 단위가 커지면 관리해야 하는 대상의 수가 적어지므로 DBMS가 관리하기 쉬워지지만 동일한 잠김 대상에 동시 액세스할 확률이 높아져 충돌이 자주 발 생하게 된다. 반대로 잠김의 단위가 작아지면 관리해야 하는 대상의 수가 많아져 관리하기는 어려 워지지만 동일한 잠김 대상에 동시 액세스할 확률이 낮아져 충돌 횟수는 적어지게 된다. DBMS에 따라 다소 차이는 있지만 일반적으로 데이터베이스 레벨, 테이블 레벨, 페이지(블록) 레벨, 행 레벨 이 대부분의 DBMS에 의하여 지원되고 있다.


잠김 확산(Locking Escalation)

잠김 확산이란 관리해야 하는 잠금 단위의 개수가 미리 설정한 임계치에 도달하게 되면 잠김의 단 위를 현재 관리하고 있는 단위보다 하나 높은 수준으로 올리는 기능을 말한다. 이러한 개념은 하위 수준에서 관리해야 하는 대상을 상위 수준에서 관리함으로써 그 아래 수준에서는 개별적으로 잠금 들을 관리할 필요가 없어지므로 잠금 대상의 개수가 줄어들며, 그에 따른 자원들도 해제되어 잠김 을 관리하기 위한 DBMS의 부담도 최소화된다.


잠김(Locking)의 유형

일반적으로 읽기 작업에서는 공용 잠김(Shared lock)을 필요로 하고 쓰기 작업에서는 배타적 잠 김(Exclusive lock)을 필요로 한다. 오라클의 경우, 읽기 작업에 공용 잠김을 사용하지 않고 Undo 데이터를 이용하는 방식으로 읽기 일관성을 제공한다. 대부분 DBMS가 하나의 행을 잠글 때 해당 테이블에 대한 잠김도 동시에 일어나는데, 이를 오라 클의 경우‘테이블(TM) 잠김’, SQL 서버의 경우‘Intent 잠김’이라 부른다. 그럼으로써 현재 트 랜잭션이 갱신 중인 테이블에 대한 호환되지 않는 DDL 또는 DML 오퍼레이션을 방지한다. 테이블 잠김에는 다음과 같은 여러 가지 잠김 모드가 있으며, 이 중 RX 모드 테이블 잠김은 DML 작업에 사용되고, RS 모드 테이블 잠김은 select for update 문을 위해 사용된다.

- RS : row share(또는 SS : sub share)

- RX : row exclusive(또는 SX : sub exclusive)

- S : share

- SRX : share row exclusive(또는 SSX : share/sub exclusive)

- X : exclusive

잠김 모드 간 호환성(Compatibility)을 정리하면 있 음을 의미함).


RS RX S SRX X
RS O O O O
RX O O
S O O
SRX O
X
2PC(2 Phased Commit)

2개 이상의 트랜잭션들이 병행적으로 처리되었을 때의 데이터베이스 결과는 그 트랜잭션들을 임 의의 직렬적인 순서로 처리했을 때의 결과와 논리적으로 일치해야 한다. 이처럼 병렬로 수행되는 트랜잭션의 직렬 가능성을 보장하기 위해 주로 사용하는 방법이 2PC(Two-Phased Locking 또 는 2 Phased Commit) 기법이다. 2PC에서는 트랜잭션 필요시 잠김을 필요한 만큼 걸 수 있지만 일단 첫 번째 Locking을 해지하면(Unlock이 되면) 더 이상의 Locking을 걸 수 없다. 따라서 트 랜잭션은 Locking을 거는 성장 단계(Growing Phase)와 Locking을 푸는 축소 단계(Shrinking Phase)의 2단계로 구성된다. 이것은 분산 트랜잭션에서도 데이터 일관성을 유지하기 위해 동일하 게 적용되고 있다.


교착 상태(Dead Lock)

다른 사용자가 잠근 자원이 해제되기를 기다리면서 자신이 잠근 자원을 해제하지 않는 상태로 영 원히 처리를 할 수 없는 무한 대기 상태를 교착 상태라 한다.

[그림 5-2-5] 교착 상태(Dead Lock) - 대기 그래프

교착 상태의 필수 조건은 4가지가 있다.


상호 배제(Mutual Exclusive)

어느 자원에 대해 한 프로세스가 이미 사용 중이면 다른 프로세스는 기다려야 하는 것


점유와 대기(Wait for)

하나 이상의 자원을 할당 받은 채로 나머지 자원을 할당 받기 위해 다른 프로세스의 자원이 해제되 기를 기다리는 프로세스가 존재하는 경우


비중단(no preemption)

자원을 할당 받은 프로세스로부터 자원을 강제로 빼앗지 못하는 것


환형 대기(circular wait)

자원 할당 그래프상에서 프로세스의 환형 사슬이 존재하는 것

위 4가지 교착 상태 필수 조건을 부정함으로써 교착 상태를 예방할 수 있다. 예를 들어, 점유와 대기의 부정으로 사용자가 필요한 자원을 한번에 요청하는 것이다.

4가지 교착 상태를 부정할 수 없는 경우가 발생하므로 트랜잭션을 처리할 때 교착 상태를 회피하 는 방법이 적용된다. 예를 들면, 개발자들이 마스터 테이블과 디테일 테이블을 변경한다면 마스터 테 이블 처리 후 디테일 테이블 처리 혹은 디테일 테이블 처리 후 마스터 테이블 처리로 동일한 순서를 사용하는 것이다.


동시성 구현 사례

잠김을 이용해 선분 이력을 추가하고 갱신할 때 발생할 수 있는 동시성 이슈를 해결하는 사례를 살펴보자.

선분 이력 모델은 여러 측면에서 장점이 있지만 잘못하면 데이터 정합성이 쉽게 깨질 수 있다는 단 점이 있다. 아래 모델을 예로 들어 선분 이력이 동시성과 관련해 어떤 문제를 일으킬 수 있고 어떻게 해결할 수 있는지 살펴보기로 하자.

선분 이력이 동시성과 관련한 문제:고객(# 고객ID ,* 고객명,* 속성1,? 속성2) <-> 부가서비스이력(# 시작일시,# 종료일시,? 기타이력속성) <-> 부가서비스(# 부가서비스ID,* 부가서비스명,* 속성1,? 속성2 간의 동시성



  • declare
  • cur_dt varchar2(14);
  • begin
  • ① 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문에서 실패(0건 갱신)하고, ③번에서 한 건이 insert될 것이다. 첫 번째 트랜잭션이 ①을 수행하고 ②로 진입하기 직전에 어떤 이유에서건 두 번째 트랜잭션이 동 일 이력에 대해 ①~④를 먼저 진행해 버린다면 선분 이력이 깨지게 된다. 따라서 트랜잭션이 순차적 으로 진행할 수 있도록 직렬화 장치를 마련해야 하는데, ①번 문장을 수행하기 직전에 select for update문을 이용해 해당 레코드에 잠김을 설정하면 된다.

그런데 아래처럼 부가서비스 이력에 잠김을 걸어 동시성을 관리하려 한다면 기존에 부가서비스 이 력이 전혀 없던 고객일 경우 잠김이 걸리지 않는다. 그러면 동시에 두 개 트랜잭션이 ③번 insert문으 로 진입할 수 있고, 결과적으로 시작일시는 다르면서 종료일시가 같은 두 개의 이력 레코드가 생긴다.



  • select 고객ID from 부가서비스이력
  • where 고객ID = 1
  • and 부가서비스ID = 'A'
  • and 종료일시 = to_date( '99991231235959', 'yyyymmddhh24miss' )
  • FOR UPDATE NOWAIT ;

따라서 부가서비스 이력의 상위 엔터티인 고객 테이블에 잠김을 걸면 완벽하게 동시성 제어를 할 수 있다.



  • select 고객ID from 고객 where 고객ID = 1
  • FOR UPDATE NOWAIT ;

또 다른 상위 엔터티인 부가서비스는 여러 사용자가 동시에 접근할 가능성이 있어 여기에 잠김을 설정하면 동시성에 나빠질 수 있지만, 고객 테이블은 그럴 가능성이 희박하기 때문에 동시성에 미치 는 영향은 거의 0에 가깝다.


고장 회복(Recovery)

트랜잭션 처리 중 장애가 발생했을 경우 데이터를 트랜잭션이 시작되기 이전 상태로 회복해야 한다. 이를 위해 데이터베이스는 로그를 사용해 Before Image로 UNDO(취소)를 실시하여 롤백 처리한다.


로킹 지속 시간(Locking duration)

Locking duration을 최소화하는 것이 Locking에 의한 지연 문제를 최소화하는 것이다.

[그림 5-2-6] Locking duration

Locking에 의한 경합은 식별자 번호를 얻기 위한 채번 로직에서 많이 발생한다. 따라서 채번은 트랜잭션 종료 시점에 실시하여 locking duration을 최소화하거나 시퀀스나 데이터 타입으로 자동 번호 발생 객체를 사용한다.