Oracle 23c에 Lock-Free Reservation 기능이 추가되었습니다. 해당 기능은 로우 락 대신 Reservation Journal 테이블(이후 저널 테이블)을 통해 잔고, 재고, 예약 가능 좌석 수 등을 갱신하는 트랜잭션의 동시성을 향상시킵니다.
Lock-Free Reservations enables concurrent transactions to proceed without being blocked on updates of heavily updated rows. Lock-Free reservations are held on the rows instead of locking them. Lock-Free Reservations verifies if the updates can succeed and defers the updates until the transaction commit time. Lock-Free Reservations improves the end user experience and concurrency in transactions.
테스트를 위해 아래와 같이 테이블을 생성하겠습니다. c1 칼럼을 PK, 갱신할 c2 칼럼을 RESERVABLE 칼럼으로 정의하고, RESERVABLE 칼럼를 검증하는 CHECK 제약조건을 생성합니다. 앞선 세 가지 규칙을 준수하지 않으면 테이블 생성 시 에러가 발생합니다.
-- 1
ALTER TABLE t1 MODIFY (c2 NOT RESERVABLE);
DROP TABLE t1 PURGE;
CREATE TABLE t1 (
c1 NUMBER
, c2 NUMBER RESERVABLE
, CONSTRAINT t1_pk PRIMARY KEY (c1)
, CONSTRAINT t1_c1 CHECK (c2 >= 0)
);
아울러 Lock-Free Reservation을 지정한 테이블은 RESERVABLE 칼럼을 해제한 후 삭제해야 합니다. 참고로 23.2 버전의 에러 메시지는 Lock-Free Reservation 기능을 RESERVABLE이 아닌 ESCROW로 표시합니다.
-- 2
ORA-55728: 에스크로 열은 기본 키가 있는 테이블에만 지정할 수 있습니다.
ORA-55764: 에스크로 테이블을 삭제/이동할 수 없습니다.
먼저 alter table <table_name> modify (<escrow_column_name> NOT ESCROW)를 실행한 다음, 에스크로 테이블을 삭제/이동하십시오.
*_TABLES 뷰의 has_reservable_column 칼럼은 RESERVABLE 칼럼을 가진 테이블 여부, *_TAB_COLUMNS 뷰의 reservable_column 칼럼은 RESERVABLE 칼럼 여부를 표시합니다.
-- 3-1
SELECT has_reservable_column FROM user_tables WHERE table_name = 'T1';
HAS_RESERVABLE_COLUMN
---------------------
YES
1개의 행이 선택되었습니다.
-- 3-2
SELECT column_name, reservable_column FROM user_tab_columns WHERE table_name = 'T1';
COLUMN_NAME RESERVABLE_COLUMN
----------- -----------------
C1 NO
C2 YES
2 행이 선택되었습니다.
RESERVABLE 칼럼을 가진 테이블을 생성하면 SYS_RESERVJRNL로 시작하는 저널 테이블이 자동으로 생성됩니다.
-- 4-1
SELECT table_name FROM user_tables WHERE table_name LIKE 'SYS_RESERVJRNL_%';
TABLE_NAME
---------------------
SYS_RESERVJRNL_123006
1개의 행이 선택되었습니다.
-- 4-2
SELECT column_name, data_type, nullable, column_id
FROM user_tab_columns
WHERE table_name LIKE 'SYS_RESERVJRNL_118657';
COLUMN_NAME DATA_TYPE NULLABLE COLUMN_ID
-------------- --------- -------- ---------
ORA_SAGA_ID$ RAW Y 1
ORA_TXN_ID$ RAW Y 2
ORA_STATUS$ CHAR Y 3
ORA_STMT_TYPE$ CHAR Y 4
C1 NUMBER N 5
C2_OP CHAR Y 6
C2_RESERVED NUMBER Y 7
7 행이 선택되었습니다.
테스트를 위해 S1 세션에서 아래 INSERT 문을 수행하겠습니다.
-- 5: S1
INSERT INTO t1 VALUES (1, 2);
COMMIT;
S2 세션에서 아래 UPDATE 문을 수행하고 t1 테이블 조회하면 결과가 변경되지 않은 것을 확인할 수 있습니다. 저널 테이블 조회하면 갱신 내용이 조회됩니다.
-- 6-1: S2
UPDATE t1 SET c2 = c2 - 1 WHERE c1 = 1;
1 행이 업데이트되었습니다.
-- 6-2: S2
SELECT * FROM t1;
C1 C2
-- --
1 2
1개의 행이 선택되었습니다.
-- 6-3: S2
SELECT * FROM SYS_RESERVJRNL_123006;
ORA_SAGA_ID$ ORA_TXN_ID$ ORA_STATUS$ ORA_STMT_TYPE C1 C2_OP C2_RESERVED
------------ ---------------- ----------- ------------- -- ----- -----------
1A00200004010000 ACTIVE UPDATE 1 - 1
1개의 행이 선택되었습니다.
참고로 UPDATE 문은 앞서 수행한 형식을 사용해야 하며, 형식을 준수하지 않으면 아래와 같은 에러가 발생합니다.
-- 7
ORA-55732: 에스크로 업데이트 시 모든 기본 키 열을 지정해야 합니다.
ORA-55734: 에스크로 열 SET 절은 주어진 에스크로 열에서 단일 표현식 값을 더하거나 빼야 합니다.
괄호를 적절히 사용하십시오.
SET 절은 e = e + (<expression>) 또는 e = e - (<expression>) 형식이어야 합니다.
여기서 e는 에스크로 열 이름입니다.
ORA-55746: 에스크로 열 업데이트 명령문은 + 또는 - 연산만 지원하고, 비연속/블라인드 쓰기는 지원하지 않습니다.
S1 세션에서 아래 UPDATE 문을 수행하면 S2 세션과 동일한 로우를 갱신했음에도 블로킹이 발생하지 않는 것을 확인할 수 있습니다.
-- 8-1: S1
UPDATE t1 SET c2 = c2 + 1 WHERE c1 = 1;
1 행이 업데이트되었습니다.
-- 8-2: S1
SELECT * FROM t1;
C1 C2
-- --
1 2
1개의 행이 선택되었습니다.
-- 8-3: S1
SELECT * FROM SYS_RESERVJRNL_123006;
ORA_SAGA_ID$ ORA_TXN_ID$ ORA_STATUS$ ORA_STMT_TYPE C1 C2_OP C2_RESERVED
------------ ---------------- ----------- ------------- -- ----- -----------
0A001200DD020000 ACTIVE UPDATE 1 + 1
1개의 행이 선택되었습니다.
앞서 수행한 두 트랜잭션은 저널 테이블에 대한 INSERT 문의 SQL ID를 저널 테이블을 공유하기 위한 키 값으로 사용하는 것으로 보입니다. Oracle 23c에 추가된 V$TRANSACTION_BLOCK 뷰에서 연관된 트랜잭션을 조회할 수 있습니다.
-- 9-1
SELECT * FROM v$transaction_block;
ADDR XIDUSN XIDSLOT XIDSQN LADDR TSN RDBA OBJ# SQL_ID CON_ID
---------------- ------ ------- ------ ---------------- --- -------- ------ ------------- ------
0000000078DACEA0 26 32 260 0000000078C546C8 6 62914707 123007 9h1fxfn1j4jyc 3
0000000078DADAA8 10 18 733 0000000078C54A38 6 62914710 123007 9h1fxfn1j4jyc 3
2 행이 선택되었습니다.
-- 9-2
SELECT sql_text FROM v$sql WHERE sql_id = '9h1fxfn1j4jyc';
SQL_TEXT
---------------------------------------
INSERT INTO "SYS_RESERVJRNL_123006" ...
INSERT INTO "SYS_RESERVJRNL_123006" ...
2 행이 선택되었습니다.
S2 세션에서 아래 UPDATE 문을 수행하면 에러가 발생합니다. 체크 제약조건을 검증할 때 자체 트랜잭션은 +, - 연산을 모두 적용하고, 다른 트랜잭션은 - 연산만 적용하는 것으로 보입니다.
-- 10: S2
UPDATE t1 SET c2 = c2 - 2 WHERE c1 = 1;
ORA-02290: 체크 제약조건(TUNA.T1_C1)이 위배되었습니다
S1 세션에서 동일한 UPDATE 문을 수행하면 에러가 발생하지 않습니다.
-- 11-1: S1
UPDATE t1 SET c2 = c2 - 2 WHERE c1 = 1;
-- 11-2: S1
SELECT * FROM SYS_RESERVJRNL_123006;
ORA_SAGA_ID$ ORA_TXN_ID$ ORA_STATUS$ ORA_STMT_TYPE C1 C2_OP C2_RESERVED
------------ ---------------- ----------- ------------- -- ----- -----------
0A001200DD020000 ACTIVE UPDATE 1 + 1
0A001200DD020000 ACTIVE UPDATE 1 - 2
2 행이 선택되었습니다.
S2 세션에서 아래 UPDATE 문을 수행하면 S1 세션에서 직전에 수행한 UPDATE 문에 의해 에러가 발생합니다.
-- 12: S2
UPDATE t1 SET c2 = c2 - 1 WHERE c1 = 1;
ORA-02290: 체크 제약조건(TUNA.T1_C1)이 위배되었습니다
S1 세션에서 롤백을 수행한 후 저널 테이블 조회하면 결과가 반환되지 않습니다.
-- 13-1: S1
ROLLBACK;
-- 13-2: S1
SELECT * FROM SYS_RESERVJRNL_123006;
선택된 레코드가 없습니다.
S1 세션에서 UPDATE 문을 다시 수행하면 에러가 발생하지 않습니다. 커밋을 수행한 후 t1 테이블을 조회하면 변경된 결과가 반환됩니다.
-- 14-1: S2
UPDATE t1 SET c2 = c2 - 1 WHERE c1 = 1;
1 행이 업데이트되었습니다.
-- 14-2: S2
SELECT * FROM SYS_RESERVJRNL_123006;
ORA_SAGA_ID$ ORA_TXN_ID$ ORA_STATUS$ ORA_STMT_TYPE C1 C2_OP C2_RESERVED
------------ ---------------- ----------- ------------- -- ----- -----------
1A00200004010000 ACTIVE UPDATE 1 - 1
1A00200004010000 ACTIVE UPDATE 1 - 1
1개의 행이 선택되었습니다.
-- 14-3: S2
COMMIT;
커밋이 완료되었습니다.
-- 14-4: S2
SELECT * FROM t1;
C1 C2
-- --
1 0
1개의 행이 선택되었습니다.
-- 14-5: S2
SELECT * FROM SYS_RESERVJRNL_123006;
선택된 레코드가 없습니다.
아래 표는 앞선 수행한 테스트 결과를 보여줍니다.
| 순서 | S1 | S2 | 결과 |
| 1 | C2 - 1 | 성공 | |
| 2 | C2 + 1 | 성공 | |
| 3 | C2 - 2 | 실패 | |
| 4 | C2 - 2 | 성공 | |
| 5 | C2 - 1 | 실패 | |
| 6 | ROLLBACK | ||
| 7 | C2 - 1 | 성공 | |
| 8 | COMMIT |