Lock과 트랜잭션 동시성 제어

Last modified: 2026-04-28 02:25:26 +0900 View History

오라클 Lock

DML 로우 Lock

DML 테이블 Lock

Null RS RX S SRX X
Null ○ ○ ○ ○ ○ ○
RS ○ ○ ○ ○ ○
RX ○ ○ ○
S ○ ○ ○
SRX ○ ○
X ○

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

select * from t for update;
select * from t for update wait 3;
select * from t for update nowait;

Lock을 푸는 열쇠, 커밋

트랜잭션 동시성 제어

비관적 동시성 제어

SELECT 적립포인트, 방문횟수, 최근방문일시, 구매실적 
  FROM 고객
 WHERE 고객번호 = :cust_num;

-- 새로운 적립포인트 계산

UPDATE 고객 
   SET 적립포인트 = :적립포인트 
 WHERE 고객번호 = :cust_num;

SELECT 적립포인트, 방문횟수, 최근방문일시, 구매실적 
 FROM 고객
WHERE 고객번호 = :cust_num 
  FOR UPDATE;

for update nowait → 대기없이 Exception(ORA-00054)을 던짐
for update wait 3 → 3초 대기 후 Exception(ORA-30006)을 던짐

큐(Queue) 테이블 동시성 제어

SELECT cust_id, rcpt_amt 
  FROM cust_rcpt_Q
 WHERE yn_upd = 'Y' 
   AND rownum <= 100 
   FOR UPDATE WAIT 3;

SELECT cust_id, rcpt_amt 
  FROM cust_rcpt_Q
 WHERE yn_upd = 'Y' 
   FOR UPDATE SKIP LOCKED;

낙관적 동시성 제어

SELECT 적립포인트, 방문횟수, 최근방문일시, 구매실적 INTO :a, :b, :c, :d
  FROM 고객
 WHERE 고객번호 = :cust_num;

-- 새로운 적립포인트 계산

UPDATE 고객 
   SET 적립포인트 = :적립포인트
 WHERE 고객번호 = :cust_num
   AND 적립포인트 = :a
   AND 방문횟수 = :b
   AND 최근방문일시 = :c
   AND 구매실적 = :d ;

IF SQL%ROWCOUNT = 0 THEN
    ALERT('다른 사용자에 의해 변경되었습니다.');
END IF;

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;

SELECT 고객번호
  FROM 고객
 WHERE 고객번호 = :cust_num
   AND 변경일시 = :mod_dt
   FOR UPDATE NOWAIT;

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

img
insert into 주문
select :상품코드, :고객ID, :주문일시, :상점번호, ...
from   상품
where  상품코드 = :상품코드
and    가격 = :가격 ;  -- 주문을 시작한 시점 가격

if sql%rowcount = 0 then
    alert('상품가격이 변경되었습니다.');
end if;

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

로우 Lock 대상 테이블 지정

img
-- 양쪽 테이블 모두에 로우 Lock
select b.주문수량
from   계좌마스터 a, 주문 b
where  a.고객번호 = :cust_no
and    b.계좌번호 = a.계좌번호
and    b.주문일자 = :ord_dt
for update;

-- 주문수량이 있는 주문 테이블에만 로우 Lock
select b.주문수량
from   계좌마스터 a, 주문 b
where  a.고객번호 = :cust_no
and    b.계좌번호 = a.계좌번호
and    b.주문일자 = :ord_dt
for update of b.주문수량;

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

채번 테이블

자율 트랜잭션

create or replace function seq_nextval(l_gubun number) return number
as
    pragma autonomous_transaction; -- 자율 트랜잭션 사용
    l_new_seq seq_tab.seq%type;
begin
    update seq_tab
    set    seq = seq + 1
    where  gubun = l_gubun;

    select seq into l_new_seq
    from   seq_tab
    where  gubun = l_gubun;

    commit;
    return l_new_seq;
end;

insert into target_tab values ( seq_nextval(123), :x, :y, :z );

시퀀스 오브젝트

시퀀스 Lock

순환옵션을 가진 시퀀스 활용

MAX + 1 조회

insert into 상품거래(거래일련번호, 계좌번호, 거래일시, 상품코드, 거래가격, 거래수량)
values ( (select max(거래일련번호) + 1 from 상품거래)
, :acnt_no, sysdate, :prod_cd, :trd_price, :trd_qty );
채번 방식 식별자 구조 주요 경합 부수적인 경합 비고
채번 테이블 일련번호 (값 변경을 위한) 로우 Lock 경합 (동시성이 높다면) 채번 테이블 블록 경합 • 채번 테이블 관리 부담
구분+순번 단일 일련번호일 때보다 Lock 경합 감소
시퀀스 오브젝트 일련번호 시퀀스 경합 (시퀀스 경합 해소 시) 인덱스 블록 경합 • 시퀀스 관리 부담
• INSERT 과정에 결번 가능성
MAX + 1 일련번호 (입력 값 중복 시) 로우 Lock+재실행 (동시성이 매우 높다면) 인덱스 블록 경합 • 별도 오브젝트 관리 없음
• 중복 값 발생에 대비한 예외처리 필수
• PK 인덱스 구성에 따른 성능 차이 발생
구분+순번 단일 일련번호일 때보다 Lock 경합 감소 (구분 속성 값의 종류 수가 많으면, 현저히 감소)
img

12c 시퀀스 신기능

create sequence my_seq;

create table t (
  c1 number default my_seq.nextval not null
, c2 varchar2(5));

insert into t (c1, c2) values( my_seq.nextval, 'X' );
insert into t (c2) values( 'X' );

create table t (c1 number generated always as identity, c2 varchar2(5));

insert into t (c2) values ( 'X' );
insert into t (c1, c2) values ( default, 'X' );

insert into t (c1, c2) values ( 3, 'X' );

create table t (c1 number generated by default as identity, c2 varchar2(5));

create sequence g_seq GLOBAL; --GLOBAL 키워드 생략 가능

create sequence s_seq SESSION;

시퀀스보다 좋은 솔루션

인덱스 블록 경합

img
img

시퀀시 신기능 활용

create sequence g_seq global;
create sequence s_seq session;

select g_seq.nextval from dual;

insert into t( id, c1, c2 ) values
(to_char(g_seq.currval, 'fm0000') || to_char(s_seq.nextval, 'fm0000'), 'A', 'B' );