PL/SQL - FOR UPDATE

 

 

 

오라클에서는  DML문 (INSERT, UPDATE, DELETE, MERGE )의 경우는 commit 를 하기전에는 완료되지 않는다.

만일 commit 를 해주지 않으면 해당 데이터를 다른 세션에서 접근할수 없도록 한다. 이것은 꽤 중요한 역할을 하게

되는데 예를들어 은행계좌를 생각해보자.

 

 

만일 A 가 자신의 계좌에서 50,000 원을 출금한다고 해보자.

현재 A의 계좌에는 60000원이 들어있다.

 

A계좌에서 50.000원이 차감된다.                                                UPDATE TBL_ACCOUNT SET STMONEY=STMONEY-50000
                                                                                            WHERE ACTNUM='1234'

 

근데 계좌 출금을 하려고 ATM기기에서 버튼을                            UPDATE TBL_ACCOUNT SET STMONEY=STMONEY-23000
누르는 도중  핸드폰요금 자동 이체가되려고 한다고 보자               WHERE ACTNUM='1234' 

 (핸드폰 요금이 23000원이라고 하자)                                                                                                                 

                                                                                          

 

그러면 50000 - 23000 - 50000 = -23.000

 

공교롭게도 은행통장 잔고가 마이너스가 되버린다. 이것은 상식적이지 않다. 만일 핸드폰 자동이체가

먼저 이루어진다면 27.000원잔고가 남으므로 출금이 이루어질수 없다.

 

이처럼 하나의 과정이 완료되기전에 다른 일이 일어나서는 안되도록 해야한다. 즉 A가 출금시도를 하려는 Session이

연결되여있다면 다른 작업(예에서는 핸드폰 자동결제등)이 이루어 지지 않도록 해야한다.

 

데이터베이스에서는 하나의 작업행위를 트랜잭션(TRANSACTION)이라고 하는데, DML 문이 실행중일 경우 ROLLBACK이나

COMMIT 가 하기전에는 그 트랜잭션을 종료하지 않고 계속 유지하게 되서 다른 DML문이 접근 시도를 막는다. 이런 막는 시도를

ROCK이 발생한다고이야기 한다.

 

 

1.  FOR UPDATE - 행들에 대한 LOCK을 설정할수 있는 기능을 한다.

, 

다음과같은 테이블을 생각해보자

 

 계좌번호                잔고

______________________________

 ACTNUM              STMONEY  

 0001                     50000

 

 

 

위테이블을 TBL_ACCOUNT  이라고 하자. 위처럼 0001 계좌에는 50000원의 잔고를 가진 상태이다.

 

우리는 위 계좌에서 30000원을 출금해보려한다. 근데 아까 이야기 한예제처럼 갑자기 핸드폰 요금 23000원이

빠져나가려는 세션이 거의 동시에 접근할려고 하는 가정을 만들어 보자. 이번에는 0001 계좌에 대한 LOCK을

만들어 낼것이므로 마이너스 잔고와 같은 사태는 일어나지 않는다.

 

1) 1001 계좌에 해단 LOCK설정

SQLGATE 에서 다음과 같은 쿼리문을 입력해둔다.

 

아래처럼 1001번 계좌에 대해 FOR UPDATE를 이용해서 LOCK을 설정해 두면

한 세션이 접근 중에는 다른 세션에서 이 계좌에 대해 접근을 할수가 없다.

(물론 다른세션에서 SELECT는 가능하다고 한다. 그러나 INSERT,UPDATE 는 불가능하다)

즉 여기서는 LOCK을 설정한 SQLGATE에서는 update가 가능하지만 다른 세션 -여기서는 다른 세션 접근을 명령프롬프트를

이용할것이다.- 에서는 update가 불가능하다.

 

 

SELECT STMONEY
FROM TBL_ACCOUNT
WHERE ACTNUM='0001'
FOR UPDATE

 

 

 

 

2) SQLGATE에서 30000원 출금행위 쿼리문

 

UPDATE TBL_ACCOUNT SET STMONEY=STMONEY - 30000
WHERE ACTNUM='0001'

 

 

중요> 아직 위 UPDATE문은 COMMIT 을 하지 않음을 기억하라.

 

 

3) 다른 세션에서 접근

 

다른 세션에서 접근을 위해 명령프롬프트를 열어 핸드폰 요금에 해당하는 23000원 출금행위를 위해 update 해보면

완료가 되지 않고 무한정 기다리게 된다.  

 

 

 

 

 

 

4) 이제 다시 sqlgate로 가서 COMMIT를 실행한 후 다시 명령프롬프트에서 실행해 보자.

 

 

 

 

갱신이 완료 된다.

 

 

 

2. PL/SQL 에서 LOCK 설정

 

 

 

다음과 같은 NEW_MEMBER 테이블에서 생각해보자

 

사원번호   이름      연봉     부서번호

++++++++++++++++++++++++++++++++

EMPNO   NAME    SAL   DEPTNO

++++++++++++++++++++++++++++++++

  1001     한승연      2000        10

  1002     박규리      2500        15

  1003        니콜      3000        10

  1004     박규리      2600        15

 

 

위 테이블에서  부서번호 10번인 직원들에게 연봉을 300 올려주록 하자.

그러나 다른 DBA관리자가 위 테이블을 조작하게 되여서 앞에서 처럼 동시접근으로 인한 문제가 생길수 있으므로

LOCK 을 고려해보면 다음과 같이 코딩하면된다.

 

 

 CREATE OR REPLACE PROCEDURE PCD_CUMMUPDATE

 IS
    CURSOR CUR_SAWON10
    IS
    SELECT SANO, COMM
    FROM TBL_MEMBER
    WHERE DEPTNO=10
    FOR UPDATE

 

BEGIN
          FOR i IN CURSAWON10 LOOP

          UPDATE TBL_MEMBER SET SAL=SAL+300

          WHERE SANO=i.SANO

 

END;

 

역시 일반쿼리문과 별다른것은 없다. 다수의 행을 처리해야하므로 커서를쓰고 커서 정의구문에 FOR UPDATE 구문을 사용했다.