Oracle AI Database 26ai— Automatic Transaction Rollback (Priority Transactions with high, medium and low priority)

Source: Dev.to

Blocking When Two Users Update the Same Record

If two users attempt to modify the same row, the session that issues the UPDATE later is blocked until the first session ends its transaction.

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…

The blocking can be observed with 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

What if the waiting transaction is more important?

Starting with Oracle Database AI 26ai, Oracle provides parameters that can automatically roll back lower‑priority transactions after a configurable wait time, allowing higher‑priority transactions to proceed.

Automatic Transaction Rollback Parameters

• txn_priority – Sets the transaction priority for the current session (HIGH, MEDIUM, LOW). By default all transactions are HIGH, meaning no automatic rollback occurs.

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

• txn_auto_rollback_high_priority_wait_target – Maximum seconds a HIGH‑priority transaction will wait for a LOW or MEDIUM transaction before the lower‑priority transaction is rolled back and its session killed.

Demonstration Scenario

1. Set the wait target to 40 seconds

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

2. Session 1 – LOW priority transaction

-- 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 – also LOW priority (will be blocked)

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

You can see the block:

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

4. Session 3 – HIGH priority (will pre‑empt the LOW ones)

-- 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 is also blocked by session 1391, but because it has higher priority it will be released after at most 40 seconds and will acquire the row.

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

Monitoring the Feature

Two new columns (TXN_PRIORITY, TXN_PRIORITY_WAIT_TARGET) are added to v$transaction:

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

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

Outcome

After the 40‑second wait, session 3 acquires the row, and the lower‑priority sessions are terminated:

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

Alert Log Entries

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"