[2015-05-22] Oracle Ways of Concurrency Control

WareValleyhttp://www.WareValley.com

Database Audit and Protection [ DB 접근통제 ]

Database Encryption [ DB 암호화 ]

Database Vulnerability Assessment [ DB 취약점 분석 ]

Database SQL Query Approval [ DB 작업결재 ]

Database Performance Monitoring and Management [ DB 성능관리 및 개발 ]

WareValley

Oracle TransactionWays of Concurrency Control

오렌지팀 윤석준 선임연구원

Database security and management, WareValley.http://www.WareValley.com

Concurrency Control

비관적 동시성 제어(Pessimistic Concurrency Control)

• 같은 Data를 동시에 수정할 것이라고 가정

• Data를 읽는 시점에 Lock을 걸고 처리가완료될 때까지 유지

• 동시성이 많이 떨어짐(많이 떨어짐 ? 거의 없다고 봐야…)

낙관적 동시성 제어(Optimistic Concurrency Control)

• 같은 Data를 동시에 수정하지 않을 것이라고 가정

• Data를 읽을 때는 Lock을 걸지 않고,대신 Update 시점에 앞서 읽은 Data가변경되었는지 확인해야 함

• Lock이 유지되는 시점이 매우 짧아동시성 확보에 유리-> 하지만…. 귀찮 귀찮

-> 그래서 실상은 동시성 제어 없는낙관적 프로그래밍이 대부분


e.g. Concurrency Control

Senario SQL

SELECT itemCode, itemPrice

INTO :Code , :Price

WHERE items;

INSERT INTO orders

SELECT :Code, :custId, :orderDate, :shopId,

FROM items

WHERE itemCode = :Code

AND itemPrice = :Price;

IF sql%rowcount = 0 then

ALERT('상품 가격이 변경되었습니다.');

END if;

• 온라인 쇼핑몰에서 1,000원짜리 바나나를주문

• 주문이 진행되는 동안 바나나 가격이1,200원으로 수정됨

• 최종 결제 버튼을 클릭하는 순간변경 여부를 체크하여,변경되었으면 사용자에게 통보


Pessimistic Concurrency Control

Senario SQL

적립 포인트 추가( 끝 ! 다른 설명이 필요 ? )

SELECT point, visitCnt,lastVisitedDate, amtBuy

FROM custWHERE id = :cust_num FOR UPDATE;

-- 적립 Point 계산

UPDATE custSET point = :newPoint

WHERE id = :cust_num;

잘못된 변경은 막을 수 있지만,다른 Transaction 에서 Lock 을 걸어두고 하루 종일 있으면…사용자 입장에서 하루 종일 기다릴 수도…시스템 다운됬다고 생각할 수도…


FOR UPDATE options

FOR UPDATE NOWAIT

- 대기없이 Exception ( ORA-00054 )

FOR UPDATE WAIT 3

- 3초 대기 후 Exception ( ORA-30006 )


Optimistic Concurrency Control #1

Senario SQL

적립 포인트 추가( OK ? )

SELECT point, visitCnt, last_dt, amtBuyINTO :a, :b, :c, :dFROM custWHERE id = :cust_num;-- 적립 Point 계산UPDATE cust

SET point = :newPointWHERE id = :cust_numAND point = :aAND visitCnt = :bAND last_dt = :cAND amtBuy = :d;

IF sql%rowcount = 0 thenALERT('다른 사용자에 의해 변경되었습니다.');

END if;

변수이름 붙이는 것도 귀찮…(만약 column이 4개가 아니라 20개라면…)

=> 그냥 최종변경일 하나 Check 하고 끝내자.OK ?



SQL

SELECT point, visitCnt, last_dt, amtBuy, mod_dtINTO :a, :b, :c, :d, :mod_dtFROM custWHERE id = :cust_num;



WHERE id = :cust_numAND mod_dt = :mod_dt;


END if;

이제 Column 하나만 비교 하면 끝

하지만, 프로그램 상에서가 아니라직접 SQL로 UPDATE 하면서mod_dt 를 변경하지 않으면…



SQL

SELECT point, visitCnt, last_dt, amtBuy, mod_dtINTO :a, :b, :c, :d, :mod_dtFROM custWHERE id = :cust_num;


SELECT idFROM custWHERE id = :cust_num;AND mod_dt = :mod_dt FOR UPDATE;


WHERE id = :cust_num

UPDATE 바로 전에FOR UPDATE 로 SELECT 한방



SQL

SELECT point, visitCnt, last_dt, amtBuy, ora_rowscnINTO :a, :b, :c, :d, :scnFROM cust_RWHERE id = :cust_num;-- 적립 Point 계산UPDATE cust

SET point = :newPointWHERE id = :cust_numAND rowscn = :scn;


END if;

Row 단위로 SCN 등록하도록 변경

(기본값은 NoRowDepencencies 로

Block의 SCN 값이 ora_rowscn이 됨)

프로그램이 아닌 직접 SQL문으로

Data 수정했을 경우에도 적용됨

하지만 11gR2 (11.2.0.1) 에도

Bug 7338384 가 있어서

사용할 수 없다.

=> 2 개의 Transaction에서 동시에

UPDATE했을 경우 Lost Update 발생

CREATE TABLE cust_RRowDependenciesASSELECT * FROM cust;


ORA_ROWSCN

RowDependenciesNoRowDependencies

SELECT EMPNO, SAL, ORA_ROWSCN FROM SCOTT.EMP_R;SELECT EMPNO, SAL, ORA_ROWSCN FROM SCOTT.EMP;

UPDATE SCOTT.EMPSET SAL = SAL + 20

WHERE EMPNO = 7369;

UPDATE SCOTT.EMP_RSET SAL = SAL + 20

WHERE EMPNO = 7369;

SELECT EMPNO, SAL, ORA_ROWSCN FROM SCOTT.EMP_R;SELECT EMPNO, SAL, ORA_ROWSCN FROM SCOTT.EMP;

[2015-05-22] Oracle Ways of Concurrency Control

Software

Transcript of [2015-05-22] Oracle Ways of Concurrency Control