Lock과 트랜잭션 동시성 제어

• Lock은 데이터베이스의 특징을 결정짓는 가장 핵심적인 메커니즘
• 트랜잭션 동시성제어도 반드시 학습해야할 주제
• 여러채번 방식의 성능을 비교하여 테이블 식별자 설계 및 채번 방식선택기준을 제시

1. 오라클 Lock

• 오라클은 공유리소스와 사용자 데이터 보호목적 다음과같은 Lock을 사용
  • DML Lock
  • DDL Lock
  • 래치
  • 버퍼Lock
  • 라이브러리 캐시 Lock/Pin
• 래치는 SGA에 공유된 각종 자료구조를 보호하기위해 사용
• 버퍼Lock은 버퍼블록에 대한 액세스를 직렬화 하기 위해 사용
• 라이브러리캐시 Lock과 Pin은 라이브러리캐시에 공유된 SQL커서와 PL/SQL프로그램을 보호하기 위해 사용
• 애플리케이션 개발측면에서 가장 중요하게 다루어야할 Lock은 DML Lock, DML Lock은 다중 트랜잭션이 동시에 액세스하는 데이터의 무결성을 보호해 준다.
  DML Lock에는 테이블 Lock과 로우 Lock이 있다.

1.1 DML 로우 Lock

• 두개의 동시 트랜잭션이 같은 로우를 변경하는것을 방지
• 하나의 로우를 변경하려면 로우 Lock을 먼저 설정해야한다.
• 어떤 DBMS든지 DML로우 Lock에는 배타적 모드를 사용하므로 수정을 진행중인(아직 커밋전인) 로우를 다른트랜잭션이 UPDATE하거나 DELETE할 수 없다
• INSERT에 대한 로우Lock 경합은 Unique인덱스가 있을때만 발생 > Unique인덱스가 있는상황에서 두 트랜잭션이 같은 값을 입력하려고 할때, 블로킹이 발생
  > 블로킹발생시 후행 트랜잭션은 기다렸다가 선행트랜잭션이 커밋하면 INSERT에 실패하고, 롤백하면 성공한다.
  > 두 트랜잭션이 서로 다른 값을 입력하거나 Unique인덱스가 아예 없으면 INSERT에 대한 로우 Lock경합은 발생하지 않는다.
• MVCC모델을 사용하는 오라클은(for update절이 없는) SELECT문에 로우 Lock을 사용하지 않는다.
  • 오라클은 다른트랜잭션이 변경한 로우를 읽을때 복사본 블록을 만들어서 쿼리가 '시작된 시점'으로 되돌려서 읽는다.
  • 변경이 진행중인(아직 커밋하지않은)로우를 읽을때도 Lock이 풀리때까지 기다리지 않고 복사본을 만들어서 읽는다.
  • 따라서 SELECT문에 Lock을 사용할 필요가 없다.
  • 결국, 오라클에서는 DML과 SELECT는 서로 진행을 방해하지않는다, 물론 SELECT끼리도
  • DML끼리는 방해할수 있는데 이는 어떤 DBMS도마찬가지
• DML로우 Lock에의한 성능 저하를 방지하려면, 온라인 트랜잭션을 처리하는 주간에 Lock을 필요이상으로 오래유지하지 않도록 커밋 시점을 조절해야한다. 그에앞서 모든 트랜잭션이 빨리일을마치도록, 즉Lock이 오래지속되지않도록 관련 SQL을 모두 튜닝해야 한다.

1.2 DML테이블 Lock

• 오라클은 DML로우 Lock설정하기전 테이블 Lock을 먼저 설정
• 현재 트랜잭션이 갱신중인 테이블 구조를 다른 트랜잭션이 변경하지못하게 막기 위함
• TM Lock이라고도 부른다.
• 로우 Lock에는 항상 배타적모드를 사용하지만, 테이블 Lock에는 여러가지 Lock모드 사용
• 테이블Lock이 테이블전체에 Lock을 뜻하지는 않음
• 오라클의 테이블 Lock은 자신(테이블Lock을 설정한 트랜잭션)이 해당 테이블에서 현재 어떤 작업을 수행 중인지를 알리는 일종의 푯말(Flag)
• 테이블 Lock에는 여러 모드가있고, 모드선택에따라 후행 트랜잭션이 수행가능한 작업범위가 결정됨.
• 진행할 수 없다면 기다릴지, 작업포기할지 진로를 결정해야한다. 기다려야한다면 대기자 목록에 Lock요청을 등록하고 기다린다
• Lock을얻고자하는 리소스가 사용중일때 사용자가 선택할 수있는 3가지경우(SELECT FOR UPDATE)
  1. Lock이 해제될때까지 기다린다. (select * from t for update)
  2. 일정시간만 기다리다 포기한다. (select * from t for update wait 3)
  3. 기다리지않고 작업을 포기한다. (select * from t for update nowait)

1.3 Lock을 푸는 열쇠, 커밋

• 블로킹(Blocking) : 선행 트랜잭션이 설정한Lock때문에 후행트랜잭션이 작업을 진행하지 못하고 멈춰있는 상태, 해소방법은 커밋(롤백) 뿐
• 교착상태(Deadlock) : 두 트랜잭션이 각각 특정 리소스에 Lock을 설정한 상태에서 맞은편 트랜잭션이 Lock을 설정한 리소스에 또 Lock을 설정하려고 진행하는 상황
  • 교착상태에선 둘중하나가 뒤로물러나지않으면 영영 풀리징낳는다, 좁은 골목길에 두대차량이 마주선것과 유사
  • 오라클에서 교착상태 발생시, 이를먼저 인지한 트랜잭션이 문장수준 롤백을 진행한 후 아래 에러미시지를 던진다. 교착상태를 발생시킨 문장 하나만 롤백하는것

    ORA-00060: deadlock detected while waiting for resource

  • 이제 교착상태는 해소됐지만 블로킹상태에 놓인다. 따라서 이 메시지를 받은 트랜잭션은 커밋 또는 롤백을 결정해야만한다. 프로그램내에서 이에러에 대한 예외처리 (커밋or롤백)하지않으면 대기상태를 지속하므로 주의가 필요
• 오라클은 데이터 읽을때 Lock을 사용하지 않으므로 다른 DBMS에 비해 상대적으로 Lock경합이 적게 발생
• 읽는 트랜잭션진행을 막는부담감은없으나, '불필요하게'트랜잭션을 길게 정의하지않아야함
  트랜잭션이 너무 길면, 롤백할때 너무많은 시간이 걸림. Undo 세그먼트가 고갈되거나 Undo세그먼트 경합을 유발
• 같은 데이터를 갱신하는 트랜잭션이 동시에 수행되지않도록 어플리케이션을 설계해야하고
  DML Lock때문에 동시성이 저하되지 않도록 적절한 시점에 커밋해야 한다.
• 반대로 너무자주 커밋하면 서버 프로세스가 LGWR에 로그 버퍼를 비우도록 요청하고 동기(sync)방식으로 기다리는 횟수가 늘기때문에 기본적으로 성능이 느려진다.

2. 트랜잭션 동시성 제어

• 동시성제어는 비관적 동시성 제어와 낙관적 동시성 제어로 나뉜다.
• 비관적 동시성 제어
  • 사용자들이 같은데이터를 동시에 수정할것으로 가정
  • 데이터를 읽는 시점에 Lock을걸고 조회또는 갱신 처리 완료때까지 이를 유지
  • Lock은 첫번째사용자가 트랜잭션 완료하기전까지 다른사용자가 같은데이터를 수정할 수 없게만들기때문에
  • 비관적 동시성 제어를 잘못 사용하면 동시성이 나빠진다.
• 낙관적 동시성 제어
  • 사용자들이 같은 데이터를 동시에 수정하지 않을것으로 가정
  • 데이터 읽을때 Lock설정안함
  • 읽을때는 Lock을사용하지않았지만, 데이터 수정하는시점에는 앞서읽은 데이터가 다른사용자에 의해 변경되었는지 반드시 검사필요

2.1 비관적 동시성 제어

• 비관적 동시성 제어는 자칫 시스템 동시성을 심각하게 떨어트릴 우려가 있지만, FOR UPDATE에 WAIT또는 NOWAIT옵션을 함께사용한다면 LOCK을 얻기위해 무한정 기다리지 않아도된다.

  SELECT 적립포인트, 방문횟수, 최근일시, 구매실적 FROM 고객
  WHERE 고객번호 = :CUST_NUM for update;
  for update nowait > 대기없이 Exception(ORA-00054)을 던짐
  for update wait 3 > 3초 대기후 Exception(ORA-30006)을 던짐

• WAIT나 NOWAIT옵션사용시 다른트랜잭션에 의해 LOCK이걸렸을때 EXCEPTION을만나므로
  "다른사용자에 의해 변경중이니 다시시도하십시오"등의 메시지 출력하면서 트랜잭션 종료가능
  오히려 동시성을 증가시킴
• 큐에쌓은 데이터를 처리할때 Lock이걸린 레코드는 생략하고 다음레코드를 계속 읽도록 구현하는법
  (SQL에 ROWNUM조건이없으니 클라이언트단에서 100개읽으면 멈추도록 구현해야함)

  select ust_id, rcpt_amt from cust_rcpt_Q
  where yn_und ='Y' for update skip locked;

2.2 낙관적 동시성 제어

• select 문에 읽은 컬럼이 매우많다면 UPDATE문에 조건을 일일기 기술하는것은 귀찮은 일
• 만약 UPDATE대상 테이블에 최종변경일시를 관리하는 컬럼이 있다면, 이를 조건절에 넣어 간단히 해당레코드의 갱신여부 판단가능.

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
 alert('다른 사용자에 의해 변경되었습니다.');
end if

• 낙관적 동시성 제어에서도 UPDATE전에 nowait옵션을 활용한 SELECT문을 한번더 수행함으로써 Lock에대한 예외처리를 한다면, 다른 트랜잭션이 설정한 Lock을 기다리지않게 구현할 수 있다.

2.3 동시성 제어 없는 낙관적 프로그래밍

• 낙관적 동시성 제어를 사용하면 Lock이 유지되는 시간이 매우 짧아져 동시성을 높이는 데 매우 유리
• 하지만 다른사용자가 같은 데이터를 변경했는지 검사하고 그에따라 처리방향성을 결정하는 귀찮은절차가 뒤따른다.
• 항상 트랜잭션 시작시점을 잘생각해서 동시성제어로직을 추가해야한다.

2.4 데이터 품질과 동시성 향상을 위한 제언

• 성능보다 데이터품질이 더 중요
• 동의한다면 FOR UPDATE사용을 두려워말라
• 다중 트랜잭션이 존재하는 데이터베이스환경에서 공유자원에 대한 액세스 직렬화는 필수
• JAVA프로그램에서 멀티쓰레드프로그래밍시 synchronized키워드의 역할
• 데이터 변경목적이라면 당연히 Lock을걸어야한다.
• For update를 정확히사용하고, 동시성이 나빠지지않게 wait, nowait옵션을 활용한 예외처리에 세심한 주의가필요
• 불필요하게 Lock을 오래유지하지 않고, 트랜잭션의 원자성을 보장하는 범위 내에서 가급적 빨리 커밋
• 트랜잭션을 재생할 수 있는 경우라면 중간에 적당한 주기로 커밋하는 방안도 고려
• 꼭 주간에 수행할 필요가 없는 배치프로그램은 야간 시간대에 수행
• 낙관적, 비관적 동시성제어를 같이사용할수도 있음
  • 일단 낙관적 동시성제어 시도했다가 다른트랜잭션에 의해 데이터가 변경된 사실이 발견되면
  • 롤백하고 다시시도할때 비관적 동시성제어를 사용하는 방식
• 동시성을 향상하고자할때 SQL튜닝은 기본, 가장효율적인 인덱스를 구성해주고 데이터량에 맞는 조인메소드를 선택해야한다
• 루프를 돌면서 절차적으로 처리하면 성능이 매우느리고, 느린만큼 Lock도 오래 지속
  • Array Processing을활용하든, One SQL로하든, 처리성능이 빨라지면 Lock도 빨리해제된다
  • Lock에 대한 고민은 트랜잭션 내 모든 SQL을 완벽히 튜닝하고나서 해도 늦지 않다.

3. 채번 방식에 따른 INSERT성능 비교

• INSERT, UPDATE DELETE, MERGE중 가장 중요하고 튜닝요소가 많은것은 INSERT
• 수행빈도가높아서 이기도 하지만, 채번방식에 따른 성능차이가 매우 크기 때문
• 신규데이터 입력을위해 PK중복을 방지하기위한 채번이 선행되야하는데, 가장많이 사용하는 아래 세가지 채번 방식의 성능과 장단점을 비교해보자.
  • 채번 테이블
  • 시퀀스 오브젝트
  • MAX + 1조회
• PK가 [상담원ID+상담일자+상담순번]처럼 복합컬럼으로 구성시, 순번이외 컬럼을 '구분 속성'이라고 부른다

3.1 채번 테이블

• 각 테이블 식별자의 단일컬럼 일련번호 또는 구분 속성별 순번채번을 위해 별도 테이블을 관리하는 방식
• 채번레코드를 읽어 1을 더한값으로 변경하고, 그 값을 새로운 레코드를 입력하는데 사용
• 이방식은 채번 레코드 변경 과정에서 자연스럽게 액세스 직렬화(트랜잭션 줄세우기)가 이루어지므로 두 트랜잭션이 중복값을 채번할 가능성을 원천적으로 방지
• 장점
  • 범용성이 좋다
  • INSERT과정에 중복 레코드 발생에 대비한예외 처리에 크게신경쓰지않아도 되므로, 채번 함수만 잘젖ㅇ의하면 편리하게 사용가능
  • INSERT과정에 결번방지
  • PK가 복합컬럼일때도 사용가능
• 단점
  • 다른 채번방식에 비해 성능이 안좋다
  • 채번레코드 변경하기위한 로우Lock경헙때문
  • 로우Lock은기본적으로 대상테이블에 Insert를마치고 커밋 또는 롤백할때까지 지속
  • 동시INSERT가 아주 많으면 채번 레코드뿐아니라 채번 테이블 블록 자체에도 경합이 발생
  • 서로 다른 레코드를 변경하는 프로세스끼리도 경합할수있다는 뜻
  • 동시 INSERT가 아주많은 테이블은 사실상 사용이 어렵다.

3.2 시퀀스 오브젝트

• 장점
  • 성능이 빠르다(성능이슈가 없는것은아니다 - 시퀀스채번과정발생하는 LOCK)
  • 채번테이블과 마찬가지로 INSERT과정 중복레코드발생에 대비한 예외처리 신경안써도됨
• 시퀀스 오브젝트는 오라클 내부에서 관리하는 채번 테이블.(SYS.SEQ$테이블)
  DBA_SEQUENCES뷰를 통해 조회가능
• 시퀀스 오브젝트도 결국 테이블이므로 값을 읽고 변경하는 과정에 Lock매커니즘이 작동
• 캐시 사이즈를 적절히 설정하면 가장빠른 성능을 제공
• 자율 트랜잭션 기능도 기본적으로 구현
• 자율트랜잭션
  • pragma autonomous_transaction 으로 선언
  • 자율트랜잭션 선언시 내부에서 커밋수행해도 메인 트랜잭션은 커밋하지않은 상태로남는다.
  • 메인 트랜잭션 insert문에서 채번함수를 호출하고 최종적으로 커밋전까지 다른 작업을 많이 수행하더라도
    채번 테이블 로우Lock은 이미 해제한 상태여서 다른 트랜잭션을 블록킹하지 않는다.
• 단점
  • '기본적으로' pk가 단일컬럼일때만 사용가능 (최소성 요건만족위해, 복합컬럼일때도할수는있음)
  • 신규데이터 입력과정에서 결번발생 가능 (롤백, CACHE옵션을설정한 시퀀스가 캐시에서 밀려나는 경우)
  • 자주사용하지않아 캐시에서 밀려나거나 인스턴스를 재기동하는순간, 캐시돼있던 번호는 모두 사라지며 디스크에서 다시 읽을때 그다음 번호부터 읽는다.

  3.3 MAX + 1조회

• 아래와 같이 테이블 최종일련번호를 조회하고, 거기에 1을 더해서 INSERT하는 방식
• 장점
  • 시퀀스, 또는 별도 채번 테이블 관리부담없음
  • 동시트랜잭션에 의한 충돌이 많지않으면 성능이 매우빠름
  • PK가 복합컬럼인 경우, 구분 속성별 순번을 채번할 때도 사용가능
  • 채번테이블은 구분속성값의 수가 적을때만 사용할 수 있지만, 이방식은 값의 수가 아무리많아도 상관없고, 오히려 성능이 좋아진다 > 입력값 중복에 의한 로우Lock경합이 줄고 재실행 횟수도 줄기때문
• 단점
  • 레코드중복에대한 세밀한 예외처리 필요
  • 다중 트랜잭션에 의한 채번이 심하면 시퀀스보다 성능이 많이 나빠질 수 있다.
    레코드 중복에 의한 로우Lock경합때문
    로우Lock은 선행 트랜잭션이 커밋or롤백 할때까지 지속

3.4 정리

• Lock경합 요소를 고려한 채번방식 선택기준
  1. 다중 트랜잭션에 의한 동시 채번이 많지않으면, 어느방식을써도 무관, 채번테이블이나 시퀀스 오브젝트 관리부담을 고려한다면 가급적 MAX+1방식이 좋다
  2. 다중트랜잭션에의한 동시채번이 많고 PK가 단일컬럼 일련번호라면 시퀀스 방식이 가장좋다
  3. 다중 트랜잭션에의한 동시채번이 많고 PK구분 속성에 값 종류 개수가 많으면 중복에 의한 로우Lock경합 및 재실행 가능성이 낮다. 그땐 시퀀스보다 MAX+1방식이 구조적으로 좋다
  4. 다중 트랜잭션에 의한 동시채번이 많고 PK구분속성에 값 종류 개수가 적으면, MAX+1은 성능문제발생가능, 그럴땐 순환(CYCLE)옵션을 가진 시퀀스오브젝트 활용을 고려.