2009. 9. 15. 17:15

[Oracle] SELECT FOR UPDATE NOWAIT | WAIT 기능 소개


SELECT FOR UPDATE NOWAIT | WAIT 기능 소개
=========================================

PURPOSE
-------
오라클 데이터베이스에서는 선택된 행들에 대하여 배타적인 LOCK을 설정할 수
있는 기능인 FOR UPDATE 구문을 제공하고 있다.

여기서는 FOR UPDATE 구문의 전반적인 개관과 9i 신기능으로 소개된 WAIT로
TIMEOUT을 설정하는 방법을 아래와 같이 살펴본다.

1. FOR UPDATE with no option
2. FOR UPDATE NOWAIT(= WAIT 0)
3. FOR UPDATE WAIT integer (0 ~ 4294967295, second)
4. FOR UPDATE OF

Explanation
-----------
1. FOR UPDATE with no option
- 이 경우 오라클은 LOCK을 획득하기까지 무한정 기다린다.


2. FOR UPDATE NOWAIT[= WAIT 0]
- LOCK을 획득하지 못하면 ORA-00054와 함께 바로 실패한다(Example I).
  (FOR UPDATE WAIT 0 도 같이 동작한다)

Example I

-- 모든 예제는 SESSION1이 이미 LOCK을 점유하고 있는 상황을 가정 --

V901:SESSION2> select ename  from scott.emp where empno=7900 for update nowait;
select ename  from scott.emp where empno=7900 for update nowait
                         *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

V901:SESSION2> select ename  from scott.emp where empno=7900 for update wait 0;
select ename  from scott.emp where empno=7900 for update wait 0
                         *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


3. UPDATE WAIT integer(0 ~ 4294967295, second)

- WAIT 다음 주어지는 정수 만큼 동안 LOCK을 획득하기 위해 재시도한다.
  그러나 주어진 시간동안 LOCK을 획득하지 못하면 ORA-30006와 함께
  해당 SQL문은 실패한다(Example I).
  WAIT 다음에 integer 설정하지 않거나 integer의 최대값(4294967295)을
  초과하는 값을 설정하면 ORA-30005 에러를 만나게 된다(Example II,III).

Example I

V901:SESSION2> select ename  from scott.emp where empno=7900 for update wait 5;
select ename  from scott.emp where empno=7900 for update wait 5
                         *
ERROR at line 1:
ORA-30006: resource busy; acquire with WAIT timeout expired


Example II

V901:SESSION2> select ename  from scott.emp where empno=7900 for update wait;
select ename  from scott.emp where empno=7900 for update wait
                                                            *
ERROR at line 1:
ORA-30005: missing or invalid WAIT interval

Example III

V901:SESSION2>  select ename  from scott.emp for update wait 4294967296;

ERROR at line 1:
ORA-30005: missing or invalid WAIT interval


4. FOR UPDATE OF

- FOR UPDATE 구문은 FROM 절에 기술된 복수개의 테이블의 해당행에 모두
  LOCK을 설정한다(Example I)
  이 때 OF 를 기술함으로 하여 특정 TABLE의 행에만 LOCK을 설정할 수 있다(Example II)

* 주의
- FROM 절에 DUMMY로 열거된 TABLE인 경우(즉 JOIN 조건이 없어서 Cartesian product로 연산되는 경우),
  OF 절이 없다면 모든 TABLE, 행에 LOCK이 설정된다(Example III)

Example I

SELECT empno, sal, comm
    FROM emp, dept
    WHERE job = 'CLERK'
        AND emp.deptno = dept.deptno
        AND loc = 'NEW YORK'
    FOR UPDATE;

COL OBJECT_NAME FORMAT a20
SELECT A.SID, A.SERIAL#, B.TYPE, C.OBJECT_NAME
    FROM V$SESSION A, V$LOCK B, DBA_OBJECTS C
    WHERE A.SID=B.SID
        AND B.ID1=C.OBJECT_ID
        AND B.TYPE='TM'
        AND C.OBJECT_NAME IN ('EMP','DEPT');


       SID    SERIAL# TYPE OBJECT_NAME
---------- ---------- ---- --------------------
        16       1184 TM   DEPT
        16       1184 TM   EMP

Example II

SELECT empno, sal, comm
    FROM emp, dept
    WHERE job = 'CLERK'
        AND emp.deptno = dept.deptno
        AND loc = 'NEW YORK'
    FOR UPDATE OF emp.sal;

COL OBJECT_NAME FORMAT a20
SELECT A.SID, A.SERIAL#, B.TYPE, C.OBJECT_NAME
    FROM V$SESSION A, V$LOCK B, DBA_OBJECTS C
    WHERE A.SID=B.SID
        AND B.ID1=C.OBJECT_ID
        AND B.TYPE='TM'
        AND C.OBJECT_NAME IN ('EMP','DEPT');


       SID    SERIAL# TYPE OBJECT_NAME
---------- ---------- ---- --------------------
        16       1184 TM   EMP

Example III

SELECT empno, sal, comm
    FROM emp, dept
    FOR UPDATE;


COL OBJECT_NAME FORMAT a20
SELECT A.SID, A.SERIAL#, B.TYPE, C.OBJECT_NAME
    FROM V$SESSION A, V$LOCK B, DBA_OBJECTS C
    WHERE A.SID=B.SID
        AND B.ID1=C.OBJECT_ID
        AND B.TYPE='TM'
        AND C.OBJECT_NAME IN ('EMP','DEPT');

       SID    SERIAL# TYPE OBJECT_NAME
---------- ---------- ---- --------------------
        16       1184 TM   DEPT
        16       1184 TM   EMP


Reference Documents
-------------------
Oracle9i SQL Reference
Release 2 (9.2)
Part Number A96540-01
    
from www.oracle.co.kr