Lock과 트랜잭션 동시성 제어

DML Lock

DML Lock은 다중 트랜잭션이 동시에 액세스하는 사용자 데이터의 무결성을 보호해 준다. DML Lock에는 테이블 Lock과 로우 Lock가 있다.

 

DML 로우 Lock

두 개의 동시 트랜잭션이 같은 로우를 변경하는 것을 방지한다. 하나의 로우를 변경하려면 로우 Lock를 먼저 설정해야한다. 어떤 DBMS이든지 DML 로우 Lock에는 배타적 모드를 사용하므로 UPDATE 또는 DELETE를 진행중인 로우를 다른 트랜잭션이 UPDATE하거나 DELETE 할 수 없다

 

DML 테이블 Lock

오라클은 DML로우 Lock을 설정하기에 앞서 테이블 Lock를 먼저 설정한다. 현재 트랜잭션이 갱신 중인 테이블 구조를 다른 트래잭션이 변경하지 못하게 막기 위해서이다. 테이블 Lock을 'TM Lock'이라고 부르기도 한

 

Lock 모드간 호환성 정리

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

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

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

 

 

lock을 푸는 열쇠, 커밋

블로킹과 교착상태는 다르다. 블로킹은 선행 트랜잭션이 설정한 Lock 때문에 후행 트랜잭션이 작업을 진행하지 못하고 멈춰있는 상태다. 이것을 해결하기 위한 방법이 커밋이다.

교착상태는 두 트랜잭션이 각각 특정 리소스에 Lock을 설정한 상태에서 맞은편 트랜잭션이 Lock을 설정한 리소스에 또 Lock을 설정하려고 진행하는 상황을 말한다. 교착상태가 발생하면 둘 중 하나가 뒤로 물러나지 않으면 영영 해결불가다.

# 오라클에서 교착상태 발생시 에러메시지
0RA-00060: deadlock detected while watiting for resource

 

 트랜잭션 동시성 제어

동시성 제어는 비관적 동시성 제어와 낙관적 동시성 제어로 나뉜다.

비관적 동시성 제어는 사용자들이 같은 데이터를 동시에 수정할 것으로 가정한다.

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

낙관적 동시성 제어는 사용자들이 동시에 수정하지 않을 것으로 가정한다.

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

 

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

신규 데이터를 입력하려면 PK 중복을 방지하기 위한 채번이 선행되어야 하는데 가장 많이 사용하는 채번 방식이1) 채번테이블 2) 시퀀스 오브젝트 3) MAX +1 조회 이다.

1) 채번 테이블

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

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

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

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

2) 시퀀스 오브젝트

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

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

3) 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 구준 속성에 값 종류가 적으면 순환 옵션을 가진 시퀀스 오브젝트가 좋음.

 

 

 

출처: 개발자를 위한 SQL 튜닝 입문서 친절한 SQL 튜닝