꿈꾸는 개발자, DBA 커뮤니티 구루비

비관적 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