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 행이 선택되었습니다.

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

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

Lock-Free Reservation #2

개요

Lock-Free Reservation

Pessimistic Locking

FOR UPDATE SKIP LOCKED

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역

	-- 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;

	-- 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;
	/

	-- 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;

	-- 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 행이 선택되었습니다.

	-- 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;

	-- 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;
	/

	-- 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;

	-- 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 행이 선택되었습니다.

	-- 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;

	-- 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;
	/

	-- 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;

	-- 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 행이 선택되었습니다.