SQL 튜닝이론 - 트랜잭션 동시성 제어(비관적 동시성 제어, 낙관적 동시성 제어, FOR UPDATE, FOR UPDATE OF )

SQL 튜닝이론 - 트랜잭션 동시성 제어(비관적 동시성 제어, 낙관적 동시성 제어, FOR UPDATE, FOR UPDATE OF )

 

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

 

비관적 동시성 제어는 사용자들이 같은 데이터를 동시에 수정할 것을 가정합니다. 한 사용자가 데이터를 읽는 시점에 LOCK을 걸고 조회 또는 갱신처리가 완료될 때까지 이를 유지합니다. 첫 번째 사용자가 트랜잭션을 완료하기 전까지 같은 데이터를 다른 사용자가 수정할 수 없기 때문에 비관적 동시성 제어를 남용하면 동시성이 나빠집니다.

 

낙관적 동시성 제어는 사용자들이 같은 데이터를 동시에 수정하지 않을 것을 가정합니다. 따라서 데이터를 읽을 때 LOCK을 설정하지 않습니다. 데이터를 읽을 때는 LOCK을 설정하지 않지만 갱신 시에는 갱신에 앞서 읽은 데이터가 다른 사용자에 의해 변경되었는지 반드시 검사해야 합니다.

 

비관적 동시성 제어

 

아래와 같은 쿼리의 작동은 데이터 읽기와 갱신 사에이 다른 트랜잭션이 데이터를 변경할 수 있습니다.

 

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

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

 

아래와 같이 select 문에 for update를 사용하면 갱신 전 테이블에 lock을 설정하므로 데이터가 잘못 갱신되는 문제를 방지할 수 있습니다.

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

 

위와 같은 방식의 비관적 동시성 제어는 시스템의 동시성을 떨어뜨릴 우려가 있지만 FOR UPDATE에 WAIT 또는 NOWAIT 옵션을 함께 사용하면 LCCK을 얻기 위해 무한정 기다리지 않아도 됩니다.

 

FOR UPDATE NOWAIT -- LOCK을 얻지 못하면 대기없이 ORA-00054 예외를 발생시킵니다.
FOR UPDATE WAIT 3  -- 3초 대기 후 ORA-30006 EXCEPTION 을 발생시킵니다.

 

WAIT 또는 NOWAIT 옵션을 사용하면, 다른 트랜잭션에 의해 LOCK이 걸린 테이블에 접근 시 EXCEPTION을 만나게 되므로 오히려 동시성을 증가합니다.

 

QUEUE TABLE 동시성 제어

큐 테이블에 쌓인 고객 입금 정보를 일정시간 간격으로 읽어서 입금 테이블에 반영하는 데몬 프로그램이 있다고 가정합니다. 데몬이 여러 개 동작하면 고객 입금 정보 테이블에 LOCK이 걸릴 수 있는 상황입니다.

 

LOCK 이 걸리면 데몬의 SELECT 트랜잭션을 3초간 대기했다가 다음에 다시 시도하게 할 수 있습니다. FOR UPDATE WAIT 3 옵션을 지정합니다. 큐에 쌓인 데이터를 일괄적으로 UPDATE 하면 LOCK 이 걸려있는 시간이 길기 때문에 고객 입금 정보를 100개 씩만 읽도록 합니다.

 

select cust_id, rcpt_Amt from cust_rcpt_Q
where yn_upd = 'Y' and rownum <= 100 for update wait 3;

 

이럴 때 skip locked 옵션을 사용하면  lock이 걸린 레코드는 생략하므로 레코드의 수량을 지정하지 않고도 레코드를 계속 읽도록 구현할 수 있습니다.

select cust_id, rcpt_Amt from cust_rcpt_Q
where yn_upd = 'Y' and for update skip locked;

 

낙관적 동시성 제어

 

SELECT-LIST에서 네 개의 칼럼음 참조했을 때 낙관적 동시성 제어 예시입니다.

 

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

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

 

만약 update 대상 테이블에 최종변경일시를 관리하는 칼럼이 있다면, 이를 조건절에 넣어 해당 레코드의 갱신여부를 판단할 수 있습니다. update문제 칼럼을 일일이 기술할 필요가 없습니다.

 

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

update 고객 set 적립포인트 = :적립포인트
where 고객번호 = :cust_nm
and 변경일시 = :mod_dt

 

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

 

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

update 고객 set 적립포인트 = :적립포인트
where 고객번호 = :cust_nm
and 변경일시 = :mod_dt

 

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

 

낙관적 동시성 제어를 사용하면 LOCK이 유지되는 시간이 매우 짧아져 동시성을 높이는데 유리합니다. 하지만 다른 테이블 사용자가 같은 데이터를 변경했는지 검사하고 처리하는 절차가 필요합니다.

 

INSERT INTO 주문
SELECT :상품코드, :고객ID, :주문일시, :상점번 호
FROM 상품
WHERE 상품코드 = :상품코드
AND 가격 = :가격;

IF SQL%ROWCOUNT = 0 THEN 
    ALERT('상품가격변경');
 END IF;

 

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

 

데이터의 성능만큼 데이터의 품질도 중요합니다. 데이터의 품질을 위기해 FOR UPDATE를 잘 사용해야 합니다. 다중 트랜잭션이 존재하는 데이터베이스 환경에서 공유 자원에 대한 액세스의 직려화는 필수입니다. JAVA 프래그램에서 멀티 스레드 프로그래밍을 위해 SYNCHRONIZED 키워드를 사용하는 것과 같습니다.

 

데이터를 변경할 목적으로 테이블을 읽는다면 당연히 LOCK을 걸어야 합니다. 불필요하게 LOCK을 오래 유지하지 않고, 트랜잭션의 원자성을 보장하는 범위 내에서 가급적 빨리 커밋해야 합니다. 트랜잭션을 재생할 수 있는 경우, 중간에 적당한 주기로 커밋하는 방법도 고려할 수 있습니다.

 

낙관적, 비관적 동시성 제어를 동시에 사용하는 방법도 있습니다. 일단 낙관적 동시성 제어를 시도했다가 다른 트랜잭션에 의해 데이터가 변경된 사실이 발견되면, 롤백하고 다시 시도할 때 비관적 동시성 제어를 사용하는 방식입니다.

 

동시성 향상을 위해 SQL 튜닝은 가장 기본적입니다. 효율적인 인덱스를 구성하고 데이터량에 맞는 조인 메서드를 선택해야 합니다.

 

로우 LOCK 대상 테이블 지정

 

for update of를 사용하면 특정 테이블에 있는 특정 로우에만 lock을 걸 수 있습니다.

 

SELECT B.주문수량
FROM 계좌마스터 A, 주문 B
WHERE  A.고객번호 = :CUST_NO
AND B.계좌번호 = A.계좌번호
AND B.주문일자 = :ORD_DT
FOR UPDATE OF B.주문수량