Oracle AI Database 26ai — 자동 트랜잭션 롤백 (우선 순위가 높은, 중간 및 낮은 트랜잭션)

Source: Dev.to

위에 제공된 소스 링크 외에 번역할 텍스트를 알려주시면 한국어로 번역해 드리겠습니다.

두 사용자가 동일한 레코드를 업데이트할 때 차단

두 사용자가 같은 행을 수정하려고 하면, UPDATE 문을 나중에 실행한 세션은 첫 번째 세션이 트랜잭션을 종료할 때까지 차단됩니다.

Session 1

SQL> select sid from v$mystat where rownum = 1;
SID
----
2190

SQL> update USEF.TBL1 set id = 1;
1 row updated

Session 2

SQL> select sid from v$mystat where rownum = 1;
SID
----
944

SQL> update USEF.TBL1 set id = 1;
Executing…

차단 현상은 v$lock을 통해 확인할 수 있습니다:

SQL> select SID, ID1, ID2, LMODE, BLOCK, REQUEST
       from v$lock
      where type = 'TX';

SID   ID1      ID2   LMODE  BLOCK  REQUEST
----  -------- ----  -----  -----  -------
944   458766   2511      0      0        6
2190  458766   2511      6      1        0

대기 중인 트랜잭션이 더 중요하다면?

Oracle Database AI 26ai부터 Oracle은 구성 가능한 대기 시간 후에 낮은 우선순위 트랜잭션을 자동으로 롤백하고, 높은 우선순위 트랜잭션이 진행될 수 있도록 하는 파라미터를 제공합니다.

자동 트랜잭션 롤백 매개변수

• txn_priority – 현재 세션의 트랜잭션 우선순위를 설정합니다 (HIGH, MEDIUM, LOW). 기본값은 모든 트랜잭션이 HIGH이며, 이는 자동 롤백이 발생하지 않음을 의미합니다.

  SQL> alter session set txn_priority = {HIGH | MEDIUM | LOW};

• txn_auto_rollback_high_priority_wait_target – HIGH 우선순위 트랜잭션이 LOW 또는 MEDIUM 트랜잭션을 기다릴 수 있는 최대 초수이며, 그 이후에는 낮은 우선순위 트랜잭션이 롤백되고 해당 세션이 종료됩니다.

Source: …

Demonstration Scenario

1. 대기 목표를 40초로 설정

SQL> alter system set txn_auto_rollback_high_priority_wait_target = 40;
System altered.

2. Session 1 – LOW 우선순위 트랜잭션

-- Session 1
SQL> select sid from v$mystat where rownum = 1;
SID
----
1391

SQL> alter session set txn_priority = LOW;
Session altered.

SQL> update USEF.TBL1 set id = 1391 where id = 1;
1 row updated.

3. Session 2 – 역시 LOW 우선순위 (블록됨)

-- Session 2
SQL> select sid from v$mystat where rownum = 1;
SID
----
1408

SQL> alter session set txn_priority = LOW;
Session altered.

SQL> update USEF.TBL1 set id = 1408 where id = 1;
Executing…

블록 현상을 확인할 수 있습니다:

SQL> select sid, event, seconds_in_wait, blocking_session
       from v$session
      where event like '%enq%';

4. Session 3 – HIGH 우선순위 (LOW 세션을 선점)

-- Session 3
SQL> select sid from v$mystat where rownum = 1;
SID
----
2910

SQL> alter session set txn_priority = HIGH;
Session altered.

SQL> update USEF.TBL1 set id = 2910 where id = 1;
Executing…

Session 3도 세션 1391에 의해 차단되지만, 우선순위가 더 높으므로 최대 40초 후에 해제되고 해당 행을 획득합니다.

SQL> select sid, event, seconds_in_wait, blocking_session
       from v$session
      where event like '%enq%';

기능 모니터링

두 개의 새로운 열(TXN_PRIORITY, TXN_PRIORITY_WAIT_TARGET)이 v$transaction에 추가되었습니다:

SQL> select ADDR, txn_priority, txn_priority_wait_target
       from v$transaction;

ADDR                TXN_PRIORITY  TXN_PRIORITY_WAIT_TARGET
------------------- ------------ ------------------------
0000000085CA11A0    HIGH         40

결과

40초 대기 후, 세션 3이 행을 획득하고, 낮은 우선순위의 세션들은 종료됩니다:

-- Session 3
SQL> update USEF.TBL1 set id = 2910 where id = 1;
1 row updated.

-- Session 1
SQL> select sid from v$mystat where rownum = 1;
ORA-03113: end-of-file on communication channel

-- Session 2
SQL> select sid from v$mystat where rownum = 1;
ORA-03113: end-of-file on communication channel

알림 로그 항목

TEHRANPDB(3):Session (sid: 1391, serial: 61119, xid: 1.0.17832, txn_priority: "LOW")
 terminated by transaction (sid: 2910, serial: 43419, xid: -1.-1.-1, txn_priority: "HIGH")
 because of the parameter "txn_auto_rollback_high_priority_wait_target = 40"
2023-08-23T12:23:48.287763+04:30
TEHRANPDB(3):Session (sid: 1408, serial: 35823, xid: 9.27.23564, txn_priority: "LOW")
 terminated by transaction (sid: 2910, serial: 43419, xid: -1.-1.-1, txn_priority: "HIGH")
 because of the parameter "txn_auto_rollback_high_priority_wait_target = 40"