Lock과 트랜잭션 동시성 제어

운구름·2022년 7월 10일
DMLlocksql동시성스터디튜닝트랜잭션
1

친절한 SQL 튜닝 스터디

목록 보기
25/27
post-thumbnail

Lock은 데이터베이스의 특징을 결정짓는 가장 핵심적인 매커니즘.

자신이 쓰는 DB의 고유 Lock 매커니즘을 이해하지 못하면, 고품질, 고성능 애플리케이션을 구축하기 어렵다.

트랜잭션 동시성 제어도 DB 개발자라면 알아야 함.

오라클 Lock

오라클은 공유 리소스와 사용자 데이터를 보호할 목적으로 다음과 같은 종류의 Lock을 사용함.

  • DML Lock
    • 애플리케이션 측면에서 가장 중요하게 다룰 Lock
    • 다중 트랜잭션이 동시에 액세스하는 사용자 데이터의 무결성을 보호해준다.
    • Table Lock과 Row Lock이 있음
  • DDL Lock
  • 래치
    • SGA에 공유된 각종 자료구조를 보호하기 위해 사용됨.
  • 버퍼 Lock
    • 버퍼 블록에 대한 액세스 직렬화하기 위해 사용
  • 라이브러리 캐시 Lock/Pin
    • 라이브러리 캐시에 공유된 SQL 커서와 PL/SQL 프로그램을 보호하기 위하 사용
  • 기타 등등 Lock

DML Row Lock

  • 두 개의 동시 트랜잭션이 같은 로우를 변경하는 것을 방지.
  • 하나의 로우를 변경하려면 로우 Lock을 먼저 설정해야 함.
  • UPDATE, DELETE 할 때 다른 트랜잭션이 UPDATE, DELETE 할 수 없음.
  • INSERT에 대한 로우 Lock 경합은 Unique 인덱스가 있을때 발생.
    • Unique 인덱스가 있는 상황에서 두 트랜잭션이 같은 값을 입력하려고 할때 경합 발생
    • 이 경우, 후행 트랜잭션은 기다렸다 선행 트랜잭션이 커밋하면 INSERT 실패하고 롤백한다.
    • 두 트랜잭션이 다른 값을 입력하거나 Unique 인덱스가 아예 없으면 경합은 발생하지 않는다.

MVCC 모델

  • MVCC 모델을 사용하는 오라클은 select 문에 로우 lock을 사용하지 않음. (postgresql도 그럴듯)
  • MVCC 모델을 사용하면 DML과 SELECT는 서로 진행을 방해하지 않음.
  • 오라클은 DML과 SELECT는 서로 진행을 방해하지 않는다. SELECT 끼리도 마찬가지다.
  • DML 끼리는 서로를 방해할 수 있다.

MVCC 모델이 아닌 DBMS

  • MVCC 모델을 사용하지 않으면 SELECT문에 공유 lock을 사용함.
  • 공유 lock끼리는 호환이 되나, 공유 lock과 배타적 lock은 호환되지 않아, DML과 SELECT는 서로 방해될 수 있다.
  • 다른 트랜잭션이 읽고 있는 로우를 변경하려면 다음 레코드로 이동할 때까지 기다려야 함. 그리고 다른 트랜잭션이 변경 중인 로우를 읽으려면 커밋할 때까지 기다려야 한다.

DML 로우 Lock에 대한 성능 저하를 방지하려면, 온라인 트랜잭션을 처리하는 주간에 Lock을 필요이상으로 오래 유지하지 않도록 커밋 시점을 조절해야 한다.

DML 테이블 Lock (TM Lock)

오라클은 DML 로우 Lock을 설정하기 전에 테이블 Lock을 먼저 설정함.

현재 트랜잭션이 갱신중인 테이블 구조를 다른 트랜잭션이 변경하지 못하게 막기위함.

Lock 모드간 호환성 정리

  • RS : row share
  • RX : row exclusive
  • S : share
  • SRX : share row exclusive
  • X : exclusive

테이블 lock이라고 하면 테이블 전체 lock 걸린다고 생각하기 쉬운데, 그게 아니라 자신이 해당 테이블에서 현재 작업을 수행중인지 알리는 일종의 플래그다.

그래서 여러 테이블 lock 모드가 있다. 어떤 모드 사용했는지에 따라 후행 트랜잭션이 할수 있는 작업 범위가 결정된다.

대상 리소스가 사용중일 때 진로 선택

lock 얻으려는 리소스가 사용중일 때, 프로세스는 3가지 방법 중 하나를 택함.

  1. lock이 해제될 때까지 기다린다.
  2. 일정 시간만 기다리다 포기
  3. 기다리지 않고 포기

그러나 NOWAIT 키워드를 사용하면 바로 작업 포기시킬 수 있다.

lock table emp in exclusive mode NOWAIT

Lock을 푸는 열쇠, 커밋

블로킹은 선행 트랜잭션이 결정한 Lock 때문에 후행 트랜잭션이 작업못하고 멈춘 상태이다.

해소 방법은 커밋 또는 롤백 뿐이다.

데드락은 두 트랜잭션이 각각 특정 리소스에 Lock을 설정한 상태에서 맞은편 트랜잭션이 lock을 설정한 리소스에 또 lock을 설정하려고 진행하는 상황을 말함.

오라클에서 교착상태가 발생하면, 이를 먼저 인지한 트랜잭션이 문장 수준 롤백을 진행한 후에 아래 에러 메시지를 던진다.

ORA-00060: deadlock detected while waiting for resource

교착상태를 발생시킨 문장 하나만 롤백

교착상태가 해소되어도 블로킹 상태라 트랜잭션은 커밋 또는 롤백을 결정해야 함.

프로그램 내에서 예외처리(커밋 또는 롤백)를 하지 않는다면, 대기 상태를 지속하게 되므로 주의

4가지 커밋 명령

  • WAIT : LGWR가 로그버퍼를 파일에 기록했다는 완료메시지를 받을때까지 기다림
  • NOWAIT : LGWR가 완료메시지 기다리지 않고 다음 트랜잭션 진행
  • IMMEDIATE : 커밋 명령을 받을 때마다 LGWR가 로그버퍼를 파일에 기록
  • BATCH : 세션 내부에 트랜잭션 데이터를 일정량 버퍼링했다 일괄 처리

트랜잭션 동시성 제어

비관적 동시성 제어

  • 사용자들이 같은 데이터를 동시에 수정할 것을 가정함.
  • 한 사용자가 데이터를 읽는 시점에 lock을 걸고 조회 or 갱신처리가 완료될 때까지 유지.
  • lock은 다른 사용자들이 같은 데이터 수정 못하게 만들어서 비관적 동시성 제어는 잘못사용시 동시성이 나빠진다.
  • 비관적 동시성 제어는 자칫 시스템 동시성을 심각하게 떨어뜨릴 우려가 있지만 FOR UPDATE 에 WAIIT 또는 NOWAIT 옵션을 사용하면 LOCK을 얻기 위해 무한정 기다리지 않아도 됨.
  • 그리고 다른 트랜 잭션에 의해 LOCK이 걸렸을 때 Exception을 만나게 되어 트랜잭션을 종료할 수 있다 ⇒ 오히려 동시성 증가

낙관적 동시성 제어

  • 사용자들이 같은 데이터를 동시에 수정하지 않을 것을 가정함.
  • 데이터를 읽을 때, lock을 설정하지 않는다.
  • 읽는 시점에 lock을 사용하진 않았지만, 데이터를 수정하고자 하는 시점에 앞서 읽은 데이터가 다른 사용자에 의해 변경되었는지 체크해야 한다.

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

저자의 불만?

보통 개발자들 낙관적으로 코딩하는데, 그러지말고 동시성 제어좀 해라.

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

  • FOR UPDATE 사용을 두려워 하지 말자
  • 다중 트랜잭션이 존재하는 데이터베이스 환경에서 공유자원에 대한 액세스 직렬화는 필수
  • 데이터 변경할 목적으로 읽으면 당연히 LOCK을 걸어야 함
  • 금융권은 필수! FOR UPDATE를 알고 쓰고 필요한 상황이면 정확히 사용하고, 동시성이 나빠지지 않게 WAIT 또는 NOWAIT 옵션을 활용한 예외처리를 잘하자
  • 불필요하게 LOCK을 오래 유지하지 말고, 트랜잭션의 원자성을 보장하는 범위 내에서 가급적 빨리 커밋하자.
  • 꼭 주간에 수행할 필요가 없는 배치 프로그램은 야간 시간대에 수행하자.
  • 낙관적, 비관적 동시성 제어를 같이 사용하는 방법도 있다. ⇒ 낙관적 쓰다 어? 잘못됐는데? 비관적이여야지 흐히힣
  • 동시성 향상하고자 할 때 SQL 튜닝은 기본! 효율적인 인덱스 구성, 데이터량에 맞는 조인 메소드 선택!
  • LOCK에 대한 고민은 트랜잭션 내 모든 SQL을 완벽히 튜닝하고 나서 해도 늦지 않다.

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

INSERT, UPDATE, DELETE, MERGE 중 INSERT가 가장 중요하고 튜닝요소가 많음.

  • 채번 방식에 따른 성능 차이가 매우 크기 때문
  • 채번 방식
    • 채번 테이블
    • 시퀀스 오브젝트
    • MAX + 1 조회

채번 테이블

각 테이블 식별자의 단일 컬럼 일련번호 또는 구분 속성별 순번을 채번하기 위해 별도 테이블을 관리하는 방식

채번 레코드를 읽어 1 더한 값으로 변경 → 그 값을 새로운 레코드 입력하는데 사용.

  • 장점
    • 범용성이 좋다
    • INSERT 과정에서 중복 레코드 발생에 대비한 예외 처리에 크게 신경쓰지 않아도 되어 채번함수만 잘 정리하면 편리하게 사용 가능.
    • INSERT 과정에 결번을 방지할 수 있음.
    • PK가 복잡 컬럼일 때도 사용할 수 있음.
  • 단점
    • 다른 채번 방식에 비해 성능이 안좋음.
      • 채번 레코드 변경을 위한 로우 LOCK이 심함
      • 동시 INSERT가 아주 많으면 채번 레코드, 채번 테이블 블록도 경합이 발생
      • 서로 다른 레코드를 변경하는 프로세스도 경합이 일어날 수 있음.

동시 INSERT가 많은 테이블은 사용하기 어렵다.

시퀀스 오브젝트

  • 시퀀스의 가장 큰 장점
    • 빠름
    • INSERT 과정에서 중복 레코드 발생에 대비한 예외처리에 크게 신경 안써도 됨.
    • 개발팀이 사용하기 편리함
  • 단점
    • 시퀀스 채번 과정에서 발생하는 LOCK이 있음.
    • 기본적으로 PK가 단일컬럼일 때만 사용 가능하다.
      (PK가 복합 컬럼일 때도 사용할 수 있지만, 각 레코드를 유일하게 식별하는 최소 컬럼으로 PK를 구성해야 하는 최소성 요건을 위배함.)
    • 신규 데이터를 입력하는 과정에서 결번이 생길 수 있음.
      • 원인1 : 시퀀스 채번 이후 트랜잭션 롤백하는 경우가
      • 원인2 : CACHE 옵션을 설정한 시퀀스가 캐시에서 밀려나는 경우

시퀀스 오브젝트도 결국은 테이블이다.
⇒ 값을 읽고 변경하는 과정에서 LOCK이 발생함.
시퀀스 LOCK에 의한 성능 이슈가 있지만, 캐시 사이즈를 적절히 설정하면 가장 빠른 성능을 제공!

시퀀스 LOCK

오라클 시퀀스 오브젝트에 사용하는 LOCK 3가지

  • 로우 캐시 LOCK
    • 로우 캐시는 SGA의 구성요소이므로 정보를 읽고 쓸때 액세스를 직렬화하기 위한 LOCK
  • 시퀀스 캐시 LOCK
    • 시퀀스 캐시도 공유 캐시에 위치하며, 시퀀스 캐시 값을 얻을때도 액세스 직렬화가 필요하다.
    • = SQ LOCK
  • SV LOCK
    • 데이터베이스 하나에 인스턴스가 여러 개인 RAC 환경에선, 인스턴스마다 시퀀스 캐시를 따로 갖는다. 인스턴스 간에는 번호 순서를 기본적으로 보장하지 않는다.
    • RAC에서 노드 간에 순서가 보장된 상태로 Sequence.nextval을 호출하는 동안 획득한다. CACHE + ORDER 속성을 부여한 Sequence 에서 사용된다.
    • ORDER 속성이 부여된 Sequence 에 대해서 nextval을 호출하면 SSX 모드로 SV 락을 획득해야 한다.
    • RAC란? 오라클 RAC은 Oracle Real Application Clusters의 약자로서, 2001년 미국 오라클사가 개발한 클러스터링 및 고가용성 을 위한 옵션이다. 출처 : t.ly/fKZN

MAX + 1 조회

대상 테이블의 최종 일련번호를 조회하고, 거기에 1을 더해 INSERT 하는 방법

insert into 상품거래(거래일련번호, 계좌번호, 거래일시, 상품코드, 거래가격, 거래수량)
value ((select max(거래일련번호) + 1 from 상품거래)
				, :acnt_no, sysdate, :prod_cd, :trd_price, :trd_qty);
  • 장점
    • 시퀀스 또는 별도의 채번 테이블을 관리하는 부담이 없다.
    • 동시 트랜잭션 충돌이 많지 않으면 성능이 빠름
    • PK가 복합 컬럼인 경우도 사용 가능.
  • 단점
    • 레코드 중복에 대비한 세밀한 예외처리 필요.
    • 다중 트랜잭션에 의한 동시 채번이 심하면 시퀀스보다 성능이 많이 나빠질 수 있다.
      ⇒ 레코드 중복에 의한 로우 경합 때문

lock 경합 요소를 고려한 채번 방식 선택 기준

  1. 다중 트랜잭션에 의한 동시 채번이 많지 않으면 아무거나 사용해도 상관 없음.
  2. 채번 테이블이나 시퀀스 오브젝트 관리가 부담스러우면 MAX + 1 추천
  3. 다중 트랜잭션에 의한 동시 채번이 많고 PK가 단일컬럼 일련번호라면 시퀀스 방식이 좋음.
  4. 다중 트랜잭션에 의한 동시 채번이 많고 PK 구분 속성에 값 종류 갯수가 많으면, 중복에 의한 로우 LOCK 경합 및 재실행 가능성이 낮음. ⇒ MAX + 1 추천
  5. 다중 트랜잭션에 의한 동시 채번이 많고, PK 구준 속성에 값 종류가 적으면 순환 옵션을 가진 시퀀스 오브젝트가 좋음.

시퀀스보다 좋은 솔루션

  • 한 개 이상 구분 속성과 함께, 뒤쪽 순번 대신 데이터 입력일시를 두는 방식으로 PK 구조 설계하면, 채번 또는 INSERT 과정에서 생기는 LOCK 이슈를 거의 해소할 수 있다.
  • 채번 과정을 생략하고 SYSDATE 또는 SYSTIMESTAMP 추천

정보 생명 주기를 효과적으로 관리하는데 있어 데이터 삭제는 매우 중요하다.

DB가 빠르게 입력하는 만큼 빨리 삭제할 수 있어야 유지보수에 좋다. 그래서 빠르게 삭제할 수 있는 구조로 설계해야 한다.

그래서 입력일시를 PK에 포함하는 것은 의미 있는 일이다. “파티션을 이용한 대량 DELETE 튜닝”처럼 서비스 중단 없이 파티션 단위로 커팅하려면 PK에 입력일시라는 삭제 기준이 있어야 함.

인덱스 블록 경합

  • INSERT 성능이 빨라도 문제! 이유는 인덱스 경합 때문.
  • 채번 과정을 생략하는 순간부터 인덱스 블록 경합이 일어남. MAX + 1 방식에서 자주 일어난다.
  • 인덱스 블록 경합 해소 방법
    • 인덱스를 “해시 파티셔닝” 하는 것이다.
      ⇒ 값이 순차적으로 증가해도 해시 함수 리턴값이 각각 달라 다른 파티션에 입력되기 때문.
    • 인덱스를 리버스키 인덱스로 전환하는 방법
profile
운구름
뭉실뭉실 코더 운구름
이전 포스트

파티션을 활용한 DML 튜닝

다음 포스트

옵티마이저 통계정보와 비용 계산 원리

0개의 댓글