PL/SQL (14) - 커서 (Cursor) FOR UPDATE

​

FOR UPDATE 절

- 커서 질의에 FOR UPDATE 절을 추가해서 영향을 받는 행을 잠글 수 있다.

- FOR UPDATE 절은 SELECT 문 맨 마지막에 온다. (ORDER BY 보다 뒤)

- SELECT ... FOR UPDATE문은 갱신 또는 삭제될 행을 식별한 다음 결과 집합에 있는 각 행을 잠근다.

- 행의 기존 값을 기반으로 갱신을 수행하려는 경우, 갱신 전에 다른 사용자가 해당 행을 변경하는 것을 방지하는 효과가 있다.

- SELECT 문 맨 마지막에 온다. ORDER BY 보다 뒤에...

- FOR UPDATE OF column_name 을 사용하여 해당 column_name이 있는 테이블에서만 행을 잠근다.

- NOWAIT 절은 다른 세션에서 해당 행을 잠근 경우, 무한정 대기하지 않고 즉시 오라클 오류를 반환하기 때문에 제어가 곧바로 프로그램으로 되돌아가 다른 작업을 수행할 수 있다.

- NOWAIT 를 생략하면 행을 사용할 수 있을 때까지 무한정 대기한다.

 

cursor_test4.sql

DECLARE
        v_product mobile_phone.product_name%TYPE;
        v_brand mobile_phone.brand%TYPE;
 
     CURSOR cursor_mp(p_brand VARCHAR2) IS
         SELECT brand, product_name FROM mobile_phone
                WHERE brand = p_brand
         FOR UPDATE OF brand;    (NOWAIT를 사용하지 않았으므로 접근시 무한정 대기)
​
 BEGIN
        OPEN cursor_mp('LG');
        LOOP
                FETCH cursor_mp INTO v_brand, v_product;
                EXIT WHEN cursor_mp %NOTFOUND;
                DBMS_OUTPUT.PUT_LINE(v_brand || ' => ' ||  v_product);
        END LOOP;

	DBMS_OUTPUT.PUT_LINE(v_brand || ' 총 ' || cursor_mp%ROWCOUNT || ' 건');
        CLOSE cursor_mp;
 END;
 /

 

- NOWAIT를 사용하지 않은 경우이다.

- 2개 세션을 열어놓고 테스트 해보자.

- session 1 에서 Update를 하고 아직 commit 되지 않은 상태에서, session 2 가 커서에 FOR UPDATE 문으로 이미 잠겨있는 행을 잠그려고 하기 때문에 이미 해당 row가 사용중이라는 메시지와 함게 NOWAIT 절이 요구된다는 메시지를 뿌려주고 있다.

 

​

- 반대로 session 2 가 먼저 FOR UPDATE 절이 있는 커서를 실행하고, session 1 이 Update를 시도하면 해당 row가 잠겨있기 때문에 계속 대기하게 된다.

 

- 이 때 session 2 에서 commit 이나 rollback을 해주면, 잠금이 풀리면서 session 1 의 Update 작업이 이루어진다.

 

 

cursor_test5.sql

 DECLARE
        v_product mobile_phone.product_name%TYPE;
        v_brand mobile_phone.brand%TYPE;
 
     CURSOR cursor_mp(p_brand VARCHAR2) IS
         SELECT brand, product_name FROM mobile_phone
                WHERE brand = p_brand
         FOR UPDATE OF brand NOWAIT;
​
 BEGIN
        OPEN cursor_mp('LG');
        LOOP
                FETCH cursor_mp INTO v_brand, v_product;
                EXIT WHEN cursor_mp %NOTFOUND;
                DBMS_OUTPUT.PUT_LINE(v_brand || ' => ' ||  v_product);
        END LOOP;

        DBMS_OUTPUT.PUT_LINE(v_brand || ' 총 ' || cursor_mp%ROWCOUNT || ' 건');
        CLOSE cursor_mp;
 END;
 /

 

- NOWAIT를 사용한 경우이다.

- NOWAIT 는 'WAIT 0' 과 같은 의미이다. 대기 하지 않는다.

- NOWAIT 절을 사용하면 루프에서 잠금을 수행하지 못해 반환되는 오류 코드를 테스트할 수 있다.

 

 

 

cursor_test6.sql

 DECLARE
        v_product mobile_phone.product_name%TYPE;
        v_brand mobile_phone.brand%TYPE;
 
     CURSOR cursor_mp(p_brand VARCHAR2) IS
         SELECT brand, product_name FROM mobile_phone
                WHERE brand = p_brand
         FOR UPDATE OF brand WAIT 10;
​
 BEGIN
        OPEN cursor_mp('LG');
        LOOP
                FETCH cursor_mp INTO v_brand, v_product;
                EXIT WHEN cursor_mp %NOTFOUND;
                DBMS_OUTPUT.PUT_LINE(v_brand || ' => ' ||  v_product);
        END LOOP;

        DBMS_OUTPUT.PUT_LINE(v_brand || ' 총 ' || cursor_mp%ROWCOUNT || ' 건');
        CLOSE cursor_mp;
 END;
 /

 

- WAIT 에 시간(초)을 지정하면 지정한 시간동안 재시도를 하고 Lock을 획득하지 못하면 에러를 출력한다.

 

- 10 초 후 아래와 같은 에러를 출력한다.