Lock-Free Reservation #2

개요

지난 글에 이어 2,000개의 세션에서 1,000개의 티켓을 예약하는 시나리오를 가정하고 Lock-Free Reservation, Pessimistic Locking, FOR UPDATE SKIP LOCKED 방식의 성능을 측정해보겠습니다. 참고로 Oracle 23c Free는 2 코어만 지원하므로 향후 추가 테스트가 필요할 것으로 보입니다.

Lock-Free Reservation

Lock-Free Reservation 방식을 테스트를 위해 아래와 같이 테이블을 생성하겠습니다.

-- 1
ALTER TABLE t1 MODIFY (c2 NOT RESERVABLE);

DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;

CREATE TABLE t1 (
    c1 NUMBER
  , c2 NUMBER RESERVABLE
  , CONSTRAINT t1_pk PRIMARY KEY (c1)
  , CONSTRAINT t1_c1 CHECK (c2 >= 0)
);

CREATE TABLE t2 (c1 TIMESTAMP, c2 NUMBER);

INSERT INTO t1 VALUES (1, 1000);
COMMIT;

Lock-Free Reservation 방식은 아래와 같은 방식으로 예약을 처리합니다. 예약에 필요한 작업에 0.1초가 소요된다고 가정합니다.

-- 2
CREATE OR REPLACE PROCEDURE p1 (i_v1 IN NUMBER)
IS
    e_check_constraint_violated EXCEPTION;
    PRAGMA EXCEPTION_INIT (check_constraint_violated, -2290);
    v1 NUMBER;
BEGIN
    UPDATE t1 SET c2 = c2 - 1 WHERE c1 = 1;
    DBMS_SESSION.SLEEP (0.1); -- do something
    INSERT INTO t2 VALUES (SYSTIMESTAMP, i_v1);
    COMMIT;
EXCEPTION
    WHEN e_check_constraint_violated THEN
        ROLLBACK;
END;
/

DBMS_JOB 패키지로 동시에 2000개의 예약 작업을 수행하겠습니다.

-- 3
ALTER SYSTEM SET job_queue_processes = 2000;

DECLARE
    v_job NUMBER;
    v_next_date DATE := SYSDATE + INTERVAL '20' SECOND;
BEGIN
    FOR i IN 1 .. 2000 LOOP
        DBMS_JOB.SUBMIT (v_job, 'P1 (' || i || ');', v_next_date);
        COMMIT;
    END LOOP;
END;
/

ALTER SYSTEM RESET job_queue_processes;

아래 쿼리로 결과를 확인할 수 있습니다. 수행 시간은 10초, 초당 평균 예약 건수는 100건입니다.

-- 4-1
SELECT COUNT (*) AS cnt
     , MIN (c2) AS min_c2
     , MAX (c2) AS max_c2
     , MAX (c1) - MIN (c1) AS elapsed
  FROM t2;

 CNT MIN_C2 MAX_C2 ELAPSED
---- ------ ------ --------------------------
1000      1   1006 +000000000 00:00:10.341758

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

-- 4-2
SELECT   TO_CHAR (c1, 'MI:SS') AS c1
       , COUNT (*) AS cnt
    FROM t2
GROUP BY TO_CHAR (c1, 'MI:SS')
ORDER BY 1;

C1    CNT
----- ---
00:01  69
00:02 103
00:03  92
00:04 105
00:05 110
00:06  97
00:07  90
00:08  87
00:09  80
00:10 103
00:11  64

11 행이 선택되었습니다.

Pessimistic Locking

Pessimistic Locking 방식을 테스트하기 위해 아래와 같이 테이블을 생성하겠습니다.

-- 5
DROP TABLE t3 PURGE;
DROP TABLE t4 PURGE;

CREATE TABLE t3 (
    c1 NUMBER
  , c2 NUMBER
  , CONSTRAINT t3_pk PRIMARY KEY (c1)
  , CONSTRAINT t3_c1 CHECK (c2 >= 0)
);

CREATE TABLE t4 (c1 TIMESTAMP, c2 NUMBER);

INSERT INTO t3 VALUES (1, 1000);
COMMIT;

Pessimistic Locking 방식은 아래와 같은 방식으로 예약을 처리합니다.

-- 6
CREATE OR REPLACE PROCEDURE p2 (i_v1 IN NUMBER)
IS
    v1 NUMBER;
BEGIN
    SELECT c1 INTO v1 FROM t3 FOR UPDATE;

    IF v1 >= 0 THEN
        UPDATE t3 SET c2 = c2 - 1 WHERE c1 = 1;
        DBMS_SESSION.SLEEP (0.1); -- do something
        INSERT INTO t4 VALUES (SYSTIMESTAMP, i_v1);
        COMMIT;
    ELSE
        ROLLBACK;
    END IF;
END;
/

DBMS_JOB 패키지로 동시에 2000개의 예약 작업을 수행하겠습니다.

-- 7
ALTER SYSTEM SET job_queue_processes = 2000;

DECLARE
    v_job NUMBER;
    v_next_date DATE := SYSDATE + INTERVAL '20' SECOND;
BEGIN
    FOR i IN 1 .. 2000 LOOP
        DBMS_JOB.SUBMIT (v_job, 'P2 (' || i || ');', v_next_date);
        COMMIT;
    END LOOP;
END;
/

ALTER SYSTEM RESET job_queue_processes;

아래 쿼리로 결과를 확인할 수 있습니다. 수행 시간은 102초, 초당 평균 예약 건수는 10건입니다.

-- 8-1
SELECT COUNT (*) AS cnt
     , MIN (c2) AS min_c2
     , MAX (c2) AS max_c2
     , MAX (c1) - MIN (c1) AS elapsed
  FROM t4;

 CNT MIN_C2 MAX_C2 ELAPSED
---- ------ ------ --------------------------
1000      1   1015 +000000000 00:01:42.083347

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

-- 8-2
SELECT   TO_CHAR (c1, 'MI:SS') AS c1
       , COUNT (*) AS cnt
    FROM t4
GROUP BY TO_CHAR (c1, 'MI:SS')
ORDER BY 1;

C1    CNT
----- ---
00:01   7
00:02  10
00:03  10
00:04   9
00:05  10
00:06  10
00:07  10
00:08  10
00:09   9
00:10  10
...
103 행이 선택되었습니다.

FOR UPDATE SKIP LOCKED

FOR UPDATE SKIP LOCKED 방식을 테스트하기 위해 아래와 같이 테이블을 생성하겠습니다.

-- 9
DROP TABLE t5 PURGE;

CREATE TABLE t5 (
    c1 NUMBER
  , c2 NUMBER
  , c3 TIMESTAMP
  , c4 NUMBER
  , CONSTRAINT t5_pk PRIMARY KEY (c1, c2)
)
ORGANIZATION INDEX;

INSERT INTO t5 (c1, c2) SELECT 1, ROWNUM FROM XMLTABLE ('1 to 1000');
COMMIT;

FOR UPDATE SKIP LOCKED 방식은 아래와 같은 방식으로 예약을 처리합니다.

-- 10
CREATE OR REPLACE PROCEDURE p3 (i_v1 IN NUMBER)
IS
BEGIN
    FOR f1 IN (SELECT *
                 FROM t5
                WHERE c1 = 1
                  AND c3 IS NULL
                  FOR UPDATE SKIP LOCKED)
    LOOP
        BEGIN
            UPDATE t5 SET c3 = SYSTIMESTAMP, c4 = i_v1 WHERE c1 = f1.c1 AND c2 = f1.c2;
            DBMS_SESSION.SLEEP (0.1); -- do something
            COMMIT;
        EXCEPTION
            WHEN OTHERS THEN
                ROLLBACK;
        END;
        
        EXIT;
    END LOOP;
END;
/

DBMS_JOB 패키지로 동시에 2000개의 예약 작업을 수행하겠습니다.

-- 11
ALTER SYSTEM SET job_queue_processes = 2000;

DECLARE
    v_job NUMBER;
    v_next_date DATE := SYSDATE + INTERVAL '20' SECOND;
BEGIN
    FOR i IN 1 .. 2000 LOOP
        DBMS_JOB.SUBMIT (v_job, 'P3 (' || i || ');', v_next_date);
        COMMIT;
    END LOOP;
END;
/

ALTER SYSTEM RESET job_queue_processes;

아래 쿼리로 결과를 확인할 수 있습니다. 수행 시간은 10초, 초당 평균 예약 건수는 100건입니다.

-- 12-1
SELECT COUNT (*) AS cnt
     , MIN (c4) AS min_c4
     , MAX (c4) AS max_c4
     , MAX (c3) - MIN (c3) AS elapsed
  FROM t5;

 CNT MIN_C4 MAX_C4 ELAPSED
---- ------ ------ --------------------------
1000      1   1001 +000000000 00:00:09.361067

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

-- 12-2
SELECT   TO_CHAR (c3, 'MI:SS') AS c3
       , COUNT (*) AS cnt
    FROM t5
GROUP BY TO_CHAR (c3, 'MI:SS')
ORDER BY 1;

C3     CNT
----- ----
00:01   80
00:02  100
00:03   94
00:04   97
00:05  137
00:06  123
00:07  111
00:08   88
00:09  120
00:10   50

10 행이 선택되었습니다.

저작자표시 비영리 변경금지

개요