오라클 성능 고도화 원리와 해법 I (2012년)
비관적 vs. 낙관적 동시성 제어 0 0 60,930

by 구루비스터디 Concurrency 트랜잭션 [2018.03.20]


  1. 비관적 vs 낙관적 동시성 제어
    1. 비관적 동시성제어?
    2. 낙관적 동시성 제어?


비관적 vs 낙관적 동시성 제어

비관적 동시성제어?

  • 사용자들이 같은 데이터를 동시에 수정할 것이라고 가정한다.
  • 따라서 한 사용자가 데이터를 읽는 시점에 Lock을 걸고 조회 또는 갱신처리가 완료될 때까지 이를 유지한다.
  • Locking은 첫 번째 사용자가 트랜잭션을완료하기 전까지 다른 사용자들이 그 데이터를 수정할 수 없게 만들기 때문에 비관적 동시성 제어를 잘못 사용하면 동시성을 저해 받게 된다.( 잘못 사용하면 )

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

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

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


  • SELECT문에 FOR UPDATE를 사용해서 해당 고객 레코드에 Lock을 걸어둔다면 데이터가 잘못 갱신되는 문제를 방지할 수 있다.
  • SELECT 시점에 Lock을 거는 비관적 동시성 제어는 자칫 시스템 동시성을 심각하네 떨어뜨릴 우려가 있다.( 무한정 기달림 )


FOR UPDATE NOWAIT --> 대기없이 Exception (ORA-00054)을 던짐
FOR UPDATE WAIT 3 --> 3초 대기 후 Exception (ORA-30006)을 던짐


  • 무한정 기다리지 않음
  • 다른 트랜잭션에 의해 Lock이 걸렸을 때 Exception을 만나게 되므로 "다른 사용자에 의해 변경 중이므로 다시 시도하십시오"라는 메시지를 출력하면서
    트랜잭션을 종료할 수 있다. 오히려 동시성을 증가시키게 된다.


낙관적 동시성 제어?

  • 사용자들이 같은 데이터를 동시에 수정하지 않을 것이라고 가정한다.
  • 따라서 데이터를 읽을 땐느 Lock을 설정하지 않는다.
  • 그런데 낙관적 입장에 섰다고 해서 동시 트랜잭션에 의한 데이터의 잘못된 갱신을 신경쓰지 않아도 된다는 것은 아니다.
  • 읽은 시점에 Lock을 사용하지 않았지만, 데이터를 수정하고자 하는 시점에 앞서 읽은 데이터가 다른 사용자에 의해 변경되었는지를 반드시 검사해야한다.

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

  ALTER( '다른 사용자에 의해 변경되었습니다.');

END IF;

  • 앞선 SELECT문에서 읽은 컬럼들이 매우 많다면 UPDATE문의 조건절을 일일이 기술하는 것이 여간 귀찮은 일이 아닐 것이다.

SELECT 적립포인트, 방문횟수, 최근방문일시, 구매실적, 변경일시
  INTO :A, :B, :C, :D, :MOD_DT
  FROM 고객
 WHERE 고객번호 = :CUST_NUM;

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

--다른 트랜잭션에 의해 설정된 Lock에 때문에 동시성이 저하되는 것을 예방할 수 있다.
SELECT *
  FROM 고객
 WHERE 고객번호 = :CUST_NUM
   AND 변경일시 = :MOD_DT
   FOR UPDATE NOWAIT; -- NOWAIT

UPDATE 고객 SET 적립포인트 = :적립포인트
 WHERE 고객번호 = :CUST_NUM
   AND 변경일시 = :MOD_DT;

IF SQL%ROWCOUNT = 0 THEN

  ALTER( '다른 사용자에 의해 변경되었습니다.');

END IF;

  • 최종 변경일시( TIMESTAMP )을 관리하는 컬럼이 있다면 이를 조건절에 넣어 간단히 해당 레코드의 갱신여부를 판단 할 수 있다.
  • INSERT/UPDATE/DELETE 할 때마다 변경일시 컬럼을 변경하도록 구현해야한다.


ORA_ROWSCN ( 10g )

  • Timestamp를 오라클이 직접 관리 해주므로 쉽고 완벽하게 동시성을 제어할 수 있다.

CREATE TABLE WORK_TRAN_MASTR ROWDEPENDENCIES  AS
SELECT 1000 + LEVEL AS A , 0 AS STATUS, 0 AS STATUS_A, 0 AS STATUS_B FROM DUAL CONNECT BY LEVEL <= 100

ALTER TABLE WORK_TRAN_MASTR modify (  A  number NOT NULL ) 

CREATE UNIQUE INDEX JLIVE.WORK_TRAN_MASTR_PK ON JLIVE.WORK_TRAN_MASTR
(A)
LOGGING
TABLESPACE JLIVE01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


ALTER TABLE JLIVE.WORK_TRAN_MASTR ADD (
  CONSTRAINT WORK_TRAN_MASTR_PK
  PRIMARY KEY
  (A)
  USING INDEX JLIVE.WORK_TRAN_MASTR_PK);


-- SESSION 1
DECLARE
AA NUMBER;
ORA NUMBER;
BEGIN

FOR IDX in 1 .. 100 LOOP
--DBMS_OUTPUT.ENABLE;

SELECT a, ora_rowscn
   INTO  AA, ORA
  FROM WORK_TRAN_MASTR 
WHERE ROWNUM = 1 and STATUS = 0;

--DBMS_OUTPUT.PUT_LINE('A : ' ||  TO_CHAR( AA )); 
--DBMS_OUTPUT.PUT_LINE('ora_rowscn : ' ||  TO_CHAR( ORA )); 

 dbms_lock.sleep( DBMS_RANDOM.VALUE * 0.5 );

UPDATE WORK_TRAN_MASTR
      SET STATUS_A = 1
          , STATUS = 1
 WHERE A = AA
     AND ora_rowscn =  ORA;
     
COMMIT;
     
END LOOP;
       
END;
/

-- SESSION 2
DECLARE
AA NUMBER;
ORA NUMBER;
BEGIN

FOR IDX in 1 .. 100 LOOP
--DBMS_OUTPUT.ENABLE;

SELECT a, ora_rowscn
   INTO  AA, ORA
  FROM WORK_TRAN_MASTR 
WHERE ROWNUM = 1 and STATUS = 0;

--DBMS_OUTPUT.PUT_LINE('A : ' ||  TO_CHAR( AA )); 
--DBMS_OUTPUT.PUT_LINE('ora_rowscn : ' ||  TO_CHAR( ORA )); 

 dbms_lock.sleep( DBMS_RANDOM.VALUE * 0.5 );

UPDATE WORK_TRAN_MASTR
      SET STATUS_B = 1
          , STATUS = 1
 WHERE A = AA
     AND ora_rowscn =  ORA;
     
COMMIT;
     
END LOOP;
       
END;
/

  • ora_rowscn은 영구히 저장되는 값이지만 이를 시간정보로 변환하는 데에는 정해진 기한이 있다는 점이다. ( 5일 )
  • SMON 프로세스는 내부적으로 SCN과 Timestamp 간 매핑 정보를 관리하며, 오라클은 이 매핑정보을 이용해 scn_to_timestamp함수를 구현했다.
  • 따라서 5일 이전에 갱신된 레코드의 rowscn을 가지고 Timestamp값을 찾으려고 하면 매핑 테이블에서 값을 찾을 수 없어 에러가 발생하게 된다.
  • 데이타 덤프를 뜨면 각 로우마다 SCN이 찍혀 있는 것을 확인할 수 있다. pg.121
코어 오라클 데이터베이스 스터디 모임 에서 2012년에 오라클 성능 고도화 원리와 해법 I 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3079

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입