FOR UPDATE SKIP LOCKED 절 사용 방법

FOR UPDATE SKIP LOCKED 절은 주로 다중 소비자 큐(multi-consumer queue)을 구현하기 위해 사용됩니다.

 

테스트를 위해 아래와 같이 테이블을 생성하겠습니다. 선착순으로 쿠폰을 발생하는 업무를 가정하여 c1 칼럼은 쿠폰번호, c2 칼럼은 발생여부 값을 저장합니다.

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 AS SELECT ROWNUM AS c1, 'N' AS c2 FROM XMLTABLE ('1 to 10');
CREATE INDEX t1_x1 ON t1 (c2, c1);

 

1번 세션에서 아래 쿼리를 실행하면 c1이 1인 행에 락이 설정됩니다.

-- 2: S1
SELECT *
  FROM t1
 WHERE c1 >= 1
   AND c2 = 'N'
   AND ROWNUM = 1
   FOR UPDATE SKIP LOCKED;

C1
--
 1

1개의 행이 선택되었습니다.

 

2번 세션에서 동일한 쿼리를 실행하면 결과가 반환되지 않습니다. 이것은 FOR UPDATE 절이 WHERE 절 이후에 수행되기 때문입니다. 1번 세션에서 락을 설정한 c1이 1인 행이 조회되고 이 행에 락을 설정할 수 없기 때문에 결과가 반환되지 않은 것입니다. 

-- 3: S2
SELECT *
  FROM t1
 WHERE c1 >= 1
   AND c2 = 'N'
   AND ROWNUM = 1
   FOR UPDATE SKIP LOCKED;

선택된 레코드가 없습니다.

 

아래 예제를 실행하면 c2이 2인 행에 락이 설정됩니다. CURSOR FOR LOOP 문으로 1행이 반환될 때까지 결과를 가져옵니다.

-- 4: S2
SET SERVEROUTPUT ON

DECLARE
    v1 t1.c1%TYPE;
BEGIN
    FOR f1 IN (SELECT *
                 FROM t1
                WHERE c1 >= 1
                  AND c2 = 'N'
                  FOR UPDATE SKIP LOCKED)
    LOOP
        v1 := f1.c1;
        EXIT;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE (v1);
END;
/
2

PL/SQL 처리가 정상적으로 완료되었습니다.

 

아래 예제는 CURSOR FOR LOOP 문 대신 BULK COLLECTION INTO 절을 사용합니다. 3번 세션에서 예제를 수행하면 c1이 3인 행에 락이 설정됩니다.

-- 5: S3
SET SERVEROUTPUT ON

DECLARE
    CURSOR cur1
    IS
    SELECT *
      FROM t1
     WHERE c1 >= 1
       AND c2 = 'N'
       FOR UPDATE SKIP LOCKED;
    
    TYPE tnt IS TABLE OF cur%ROWTYPE;
    v1 tnt;
BEGIN
    OPEN cur1;
    FETCH cur1 BULK COLLECT INTO v1 LIMIT 1;
    CLOSE cur1;

    DBMS_OUTPUT.PUT_LINE (v1(1).c1);
END;
/
3

PL/SQL 처리가 정상적으로 완료되었습니다.