Oracle 23c에 Automatic Transaction Rollback 기능이 추가되었습니다. 이 기능은 블로킹이 발생한 후 일정 시간이 경과하면 우선순위 낮은 트랜잭션을 중단시켜 동시성을 향상시킵니다.
If a transaction does not commit or rollback for a long time while holding row locks, it can potentially block other high-priority transactions. This feature allows applications to assign priorities to transactions and for administrators to set timeouts for each priority. The database will automatically rollback a lower priority transaction and release the row locks held if it blocks a higher priority transaction beyond the set timeout, allowing the higher priority transaction to proceed. Automatic Transaction Rollback reduces the administrative burden while also helping to maintain transaction latencies/SLAs on higher priority transactions.
이 기능은 아래 파라미터와 관련이 있습니다. txn_priority 파라미터는 트랜잭션 우선순위, txn_auto_rollback_high_priority_wait_target 파라미터는 HIGH 우선순위 트랜잭션의 대기 시간, txn_auto_rollback_medium_priority_wait_target 파라미터는 MEDIUM 우선순위 트랜잭션의 대기 시간을 설정합니다. txn_auto_rollback_mode 파라미터는 기능의 동작을 설정하며, 값을 TRACK으로 설정하면 롤백을 수행하지 않고 관련 통계 값만 증가시킵니다.
-- 1-1
SELECT name, value, isses_modifiable, description
FROM v$parameter
WHERE name LIKE 'txn_%';
NAME VALUE ISSES_MODIFIABLE DESCRIPTION
--------------------------------------------- ---------- ---------------- -------------------------------------------
txn_priority HIGH TRUE Priority of a transaction in a session
txn_auto_rollback_high_priority_wait_target 2147483647 FALSE Auto abort wait for high pri txns
txn_auto_rollback_medium_priority_wait_target 2147483647 FALSE Auto abort wait for medium pri txns
txn_auto_rollback_mode ROLLBACK FALSE Modes for Auto Transaction Rollback feature
4 행이 선택되었습니다.
-- 1-2
SELECT name, ordinal, value, isdefault
FROM v$parameter_valid_values
WHERE name LIKE 'txn_%';
NAME ORDINAL VALUE ISDEFAULT
---------------------- ------- -------- ---------
txn_priority 1 HIGH TRUE
txn_priority 2 MEDIUM FALSE
txn_priority 3 LOW FALSE
txn_auto_rollback_mode 1 ROLLBACK TRUE
txn_auto_rollback_mode 2 TRACK FALSE
5 행이 선택되었습니다.
테스트를 위해 아래와 같이 테이블을 생성하고, txn_auto_rollback_high_priority_wait_target 파라미터를 10, txn_auto_rollback_medium_priority_wait_target 파라미터를 20으로 설정하겠습니다.
-- 2-1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1) AS SELECT 1 FROM DUAL;
-- 2-2
ALTER SYSTEM SET txn_auto_rollback_high_priority_wait_target = 10;
ALTER SYSTEM SET txn_auto_rollback_medium_priority_wait_target = 20;
아래와 같은 순서로 S1, S2, S3 세션에서 트랜잭션 우선순위를 설정하고 UPDATE 문을 수행하겠습니다. 우선순위에 의해 10초 후에 S1 세션이 종료되고 S2 세션이 UPDATE가 완료됩니다. 다시 10초 후에 S2 세션이 종료되고 S3 세션이 UPDATE가 완료됩니다. S1, S2 세션 모두 트랜잭션 우선순위가 HIGH인 S3 세션에 의해 종료됩니다.
-- 3-1: S1
ALTER SESSION SET txn_priority = 'LOW';
UPDATE t1 SET c1 = 1;
1 행이 업데이트되었습니다.
경 과: 00:00:00.07
ORA-03113: 통신 채널에 EOF 가 있습니다 --> 10초 후
-- 3-2: S2
ALTER SESSION SET txn_priority = 'MEDIUM';
UPDATE t1 SET c1 = 2;
1 행이 업데이트되었습니다.
경 과: 00:00:10.47
ORA-03113: 통신 채널에 EOF 가 있습니다 --> 20초 후
-- 3-3: S3
ALTER SESSION SET txn_priority = 'HIGH';
UPDATE t1 SET c1 = 3;
1 행이 업데이트되었습니다.
경 과: 00:00:20.08
ROLLBACK;
이 기능과 관련하여 V$TRANSACTION 뷰에 txn_priority, txn_priority_wait_target 칼럼이 추가되었습니다.
-- 4
SELECT b.sid, b.serial#, a.txn_priority, a.txn_priority_wait_target
FROM v$transaction a
, v$session b
WHERE a.txn_priority_wait_target > 0
AND b.taddr = a.addr;
트랜잭션 우선순위에 의해 세션이 종료되면 alert log에 아래와 같은 로그 메시지가 기록됩니다.
-- 5
SELECT message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%terminated by transaction%'
AND ROWNUM = 1;
MESSAGE_TEXT
------------------------------------------------------------------------------------------------------------------------
Session (sid:, serial:, xid:, txn_priority: "LOW") terminated by transaction (sid:, serial:, xid:, txn_priority: "HIGH")
because of the parameter "txn_auto_rollback_high_priority_wait_target = 10"
1개의 행이 선택되었습니다.
이 기능과 관련하여 3개의 대기 이벤트와 4개의 통계 지표가 추가되었습니다.
-- 6-1
SELECT event#, name, parameter1, parameter2, parameter3
FROM v$event_name
WHERE name LIKE 'enq: TX - row lock%';
EVENT# NAME PARAMETER1 PARAMETER2 PARAMETER3
------ ------------------------------------ ---------- -------------- ----------
340 enq: TX - row lock contention name|mode usn<<16 | slot sequence
341 enq: TX - row lock (HIGH priority) name|mode usn<<16 | slot sequence
342 enq: TX - row lock (MEDIUM priority) name|mode usn<<16 | slot sequence
343 enq: TX - row lock (LOW priority) name|mode usn<<16 | slot sequence
4 행이 선택되었습니다.
-- 6-2
SELECT statistic#
, name
, RTRIM (DECODE (BITAND (class, 1), 0, NULL, 'User + ')
|| DECODE (BITAND (class, 2), 0, NULL, 'Redo + ')
|| DECODE (BITAND (class, 4), 0, NULL, 'Enqueue + ')
|| DECODE (BITAND (class, 8), 0, NULL, 'Cache + ')
|| DECODE (BITAND (class, 16), 0, NULL, 'OS + ')
|| DECODE (BITAND (class, 32), 0, NULL, 'RAC + ')
|| DECODE (BITAND (class, 64), 0, NULL, 'SQL + ')
|| DECODE (BITAND (class, 128), 0, NULL, 'Debug + ')
, ' + ') AS class
FROM v$statname
WHERE name LIKE 'txns%';
STATISTIC# NAME CLASS
---------- ------------------------------------------------------------- -----
1824 txns rollback txn_auto_rollback_high_priority_wait_target Debug
1825 txns rollback txn_auto_rollback_medium_priority_wait_target Debug
1826 txns track mode txn_auto_rollback_high_priority_wait_target Debug
1827 txns track mode txn_auto_rollback_medium_priority_wait_target Debug
4 행이 선택되었습니다.
마지막으로 txn_auto_rollback_high_priority_wait_target 파라미터를 10, txn_auto_rollback_medium_priority_wait_target 파라미터를 리셋하겠습니다.
-- 7
ALTER SYSTEM RESET txn_auto_rollback_high_priority_wait_target;
ALTER SYSTEM RESET txn_auto_rollback_medium_priority_wait_target;