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

비관적 동시성제어?

사용자들이 같은 데이터를 동시에 수정할 것이라고 가정한다.
따라서 한 사용자가 데이터를 읽는 시점에 Lock을 걸고 조회 또는 갱신처리가 완료될 때까지 이를 유지한다.
Locking은 첫 번째 사용자가 트랜잭션을완료하기 전까지 다른 사용자들이 그 데이터를 수정할 수 없게 만들기 때문에
비관적 동시성 제어를 잘못 사용하면 동시성을 저해 받게 된다.( 잘못 사용하면 )
{CODE:SQL}
SELECT 적립포인트, 방문횟수, 최근방문일시, 구매실적 FROM 고객
WHERE 고객번호 = :CUST_NUM FOR UPDATE;

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

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

{CODE}

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

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

{CODE}

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

낙관적 동시성 제어?

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

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

{CODE:SQL}
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;
{CODE}

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

ORA_ROWSCN ( 10g )

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

{CODE:SQL}
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