동시성제어 SELECT FOR UPDATE #1

 

 

출처 : http://www.dator.co.kr/hotshin/textyle/236147

 

오라클 데이터베이스에서 선택된 행들에 대하여 배타적인 Lock을 설정 할 수 있는 기능이 SELECT FOR UPDATE 입니다. 요 기능에 대해서 잠시 살펴 봅니다. 어플리케이션에서 해당 기능을 프로그래밍 할 수도 있으나 오라클에서 제공되는 기능을 사용할 상황이 있겠죠.

활용하려는 상황

1. 특정한 테이블의 데이터(row)에 대하여 순차적 DML 처리를 가능하도록 하고자 하는 경우

2. 특정 데이터에 대해 동시 트랜잭션이 발생하여 데이터 정합성이 깨지는 것을 방지하고자 할 때

3. Update 처리 진행 중인 데이터를 다른 세션에서 읽는 것을 방지하고자 하는 경우

좀 장황해 보이네요. 좀 쉬운 표현으로는 A라는 사용자가 테이블의 특정 ROW를 UPDATE 하기 위해서 SELECT 하였으니까 다른 사용자들은 UPDATE 하지 말라는 것이죠.

활용 처

1. 영화 및 공연장의 특정 좌석을 예약하고자 한다.

2. 인터넷에서 판매되는 상품의 재고수량을 실시간으로 관리하고자 한다.

3. 은행의 예금 및 대출 통장의 잔액 변경의 일관성을 유지하고자 한다.

4. 인터넷 사이트의 포인트 잔액을 관리한다.

5. 기타 . . 등등

즉 하나의 리소스를 놓고 여러 트랜잭션에서 SELECT 한 후 INSERT/UPDATE/DELETE 로직을 처리하고자 할 때 먼저 SELECT 한 곳에서 리소스에 대해 LOCK을 걸고 COMMIT/ROLLBACK 하기 전에 다른 세션에서는 SELECT 되지 못하게 하는 기능이다. 프로그램적으로는 동시 처리하지 않고 순차적(Serial) 으로 처리 하라는 의미이다.

동시성 제어 시나리오
[그림1] 3명이 동시 상품구매 요청

그림 한번 그려 보았네요. 잘 안보이죠. 더블 클릭. 따닥 ~~

[그림1]은 동일 상품에 대해 3명이 동시에 주문하려고 하였을 때 상품 수량의 변화를 나타내는 가상 시나리오이다. 특정 프로모션 기간일 때 다수의 사용자가 접속하여 상품을 주문하려고 하는 상황이다. 회사마다 이런 상황이 닥쳐을 때 대처하는 방법이 다를 수 있다. 어떤 회사는 업무 정책에 따라 동시성을 제어하지 않고 마이너스( - ) 수량 발생시 수작업 후속처리를 할 수 있고, 다른 회사는 수량 부족시 아예 에러를 발생시켜서 상품 부족시 주문발생을 억제 시킬 수도 있다.

SQL 구문

사용되는 구분과 예제를 살펴 봅니다. 오라클에서 LOCK은 테이블 단위가 아니라 처리할 데이터 ROW 레벨입니다.

1) FOR UPDATE with no option

LOCK를 획득하기 까지 무한정 기다린다.

해당 세션에서 transaction을 해제(commit, rollback등) 하기 전까지는 결과셋에 대해서 lock이 걸림

SELECT A.*

FROM T_PRD_M A

WHERE PRD_CD = ' 10004'

FOR UPDATE;

2) FOR UPDATE nowait

lock를 회득하지 못하면 바로 exception 처리된다. (ORA-00054 오류 )

다른 세션에서 PRD_CD = ' 10004'에 대해서 lock을 잡고 있다면 에러가 발생

SELECT A.*

FROM T_PRD_M A

WHERE PRD_CD = ' 10004'

FOR UPDATE NOWAIT;

3) FOR UPDATE WAIT second

WAIT 시간(초) 만큼 동안 LOCK을 획득하기 위해 재시도한다.

주어진 시간 동안 LOCK을 획득하지 못하면 ORA-30006와 함께 해당 SQL문은 실패한다.

lock이 걸린 행들을 무한정 기다리게 되는 현상을 방지할 수 있으며, lock에 대한 대기 시간을 지정할 수 있기 때문에 로직 처리시간을 확보해야 하는 애플리케이션 개발에 사용 가능함.

SELECT A.*

FROM T_PRD_M A

WHERE PRD_CD = ' 10004'

FOR UPDATE WAIT 5; à 5초가 기다렸다가 lock을 획득하지 못하면 에러 발생

4) FOR UPDATE of

for update 구문은 FROM 절에 기술된 복수개의 테이블의 해당 행에 모두 LOCK을 설정한다. 이 때 of를 기술함으로 하여 특정 TABLE의 행에만 LOCK을 설정할 수 있다

SELECT A.*, C.*

FROM T_PRD_M A, T_CLS_M C

WHERE A.PRD_CD = '10004'

AND A.CLS_CD = C.CLS_CD

FOR UPDATE OF A.PRD_CD WAIT 5; à A테이블만 LOCK 겁니다.

FOR UPDATE OF C.CLS_CD WAIT 5; à C테이블에 LOCK을 겁니다.

FOR UPDATE OF WAIT 5; à A, B 테이블 모두에 LOCK 겁니다.

여기서 LOCK이 걸린다는 것은 위 SQL이 실행될 때 아래의 SQL들은 대기중으로 빠져서 기다린다는 이야기 겠죠

SELECT A.*, C.*

FROM T_PRD_M A, T_CLS_M C

WHERE A.PRD_CD = '10004'

AND A.CLS_CD = C.CLS_CD

FOR UPDATE OF A.PRD_CD WAIT 5;

동일한 SQL은 당연히 실행되지 않습니다

.

SELECT A.*

FROM T_PRD_M A

WHERE A.PRD_CD = '10004'

FOR UPDATE ;

T_PRD_M의 10004 상품코드를 검색시 실행되지 않습니다. 당근 PRD_CD = ‘10005’는 실행되겠죠. LOCK 걸린 ROW가 아니기 때문에..

UPDATE T_PRD_M A

SET REG_ID = 'UPDATE'

WHERE A.PRD_CD = '10004';

어디선가 T_PRD_M의 10004 상품코드를 UPDATE 하려고 한다면 LOCK 발생하여 대기상태로 빠집니다.

SELECT A.*

FROM T_PRD_M A

WHERE A.PRD_CD = '10004'

이 SQL은 정상적으로 실행 될까요 ?

빙고. LOCK과 상관이 없죠. 그냥 SELECT 됩니다.

SELECT FOR UPDATE 구문은 오라클 9부터 나온것이라고 하네요.

그리고 LOCK 걸린것은 commit; rollback; 될때 해제되겠죠

LOCK 세션 확인 쿼리는 아래와 같음.

SELECT A.SID, A.SERIAL#, B.TYPE, C.OBJECT_NAME

FROM V$SESSION A, V$LOCK B, USER_OBJECTS C

WHERE A.SID=B.SID

AND B.ID1=C.OBJECT_ID

AND a.schemaname= 'SH'-- 사용자명 넣기

동시성 제어 시나리오 2

[그림2] 동시 은행 입출금 처리

그림2는 은행 예금 통장에서 여러 매체를 사용하여 입금 혹은 출금이 동시에 발생하는 예시입니다. 1개 통장의 입출금이 동시에 발생될 때 각각 처리 한다면 최종 잔액의 정합성을 보장할 수 없겠죠. 이때 입출금 처리를 Serial 하게 처리 해야 합니다. 위 상황에서도 SELECT FOR UPDATE 가 사용 될 수 있습니다.

조심해야 하는 것

뭐 모든 기능이 좋은 것이 있다면 단점도 있겠죠.

1) Select For Update 문장이 꼭 필요한 프로그램에서만 적용한다

- 일반 조회 및 검색 시에는 Select For Update를 사용하면 안 된다.

- 중요한 업무에 사용되는 테이블의 데이터 변경 시 동시성에 대한 제어를 하고자 하는 요건이 있는 경우에만 사용한다.

2) 동일한 SQL 처리 로직 구문에서는 동일한 Select For Update 구문을 사용하도록 함

3) Update 변경처리 직전에 “Select For Update”를 사용하여 check 및 검증용으로 사용한다.

4) 무분별한 lock의 사용은 시스템 리소스를 감소시키고, lock에 의한 시스템 중단이 발생 될 수 도 있다.

- Select For Update 구문이 들어가는 모듈을 공통화하고, 사용되는 SQL 본 수를 최소화 한다.

5) Program 및 프레임웍에서 Serial한 로직 처리가 가능한 경우 어플리케이션에서 해결 할 수 있도록 한다.

6) Select For Update 방식 중 가급적이면 “3. FOR UPDATE WAIT 시간” 를 사용하여 lock이 유지되는 것을 방지한다.

7) 대량 배치 처리시 해당된 모든 row에 대한 lock이 걸릴 수 있기 때문에 특별한 주의를 요한다.

8) Select For Update 구문을 사용하고자 할 경우 DA 및 DBA에게 적용 사유를 공지한 후 사용하도록 한다. ( 이 구문을 사용하는 SQL과 프로그램 로직은 특별 관리 대상으로 선정한다 )

다음에는 Select For Update를 사용하지 않고 테이블과 데이터 발생규칙으로만 제어하는 사례를 살펴 보겠습니다.