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

 If two users attempt to modify the same record, the user who issues the UPDATE statement later will be blocked and will remain in a blocked state until the first user (the one who acquired the record earlier) ends their 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…

We can see the blocking of the second session using the following command:

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

It is possible that the second user, whose transaction is waiting, is more important for us. In such a case, what solution is available?

Starting with Oracle Database AI 26ai, Oracle has introduced several parameters to control this situation. These parameters make it possible to automatically roll back lower-priority transactions after a specified period of time if they block higher-priority transactions.

The parameters related to Automatic Transaction Rollback are shown below:

txn_priority                                   string      HIGH
txn_auto_rollback_mode                         string      ROLLBACK
txn_auto_rollback_high_priority_wait_target    integer     2147483647
txn_auto_rollback_medium_priority_wait_target  integer     2147483647

The txn_priority parameter is one of the most important parameters in this feature. Using this parameter, we can set transaction priority at the session level. A transaction priority can be HIGH, MEDIUM, or LOW. By default, the priority of all transactions is set to HIGH, which means no transaction will be automatically rolled back.

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

Another important parameter is txn_auto_rollback_high_priority_wait_target. This parameter specifies the maximum number of seconds that a HIGH priority transaction should wait for a LOW or MEDIUM priority transaction. After the specified time, the lower-priority transaction will be rolled back and its session will be killed.

In the following scenario, we will better understand the role of these two parameters.

First, we set the txn_auto_rollback_high_priority_wait_target parameter to 40 seconds:

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

In session 1, we execute a transaction with low priority:

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

In session 2:

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

By executing this command, session 1408 will be blocked by session 1391:

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

Press enter or click to view image in full size

In session 3, we execute a transaction with HIGH priority:

--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 since session 3 has a higher priority, it will be released from the blocked state within a maximum of 40 seconds and will acquire the required record.

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

Press enter or click to view image in full size

For the Automatic Transaction Rollback feature, two columns — txn_priority and txn_priority_wait_target—have been added to the v$transaction view. Using this view, we can also monitor this feature:

SQL> select ADDR,txn_priority, txn_priority_wait_target from v$transaction;
ADDR                TXN_PRI TXN_PRIORITY_WAIT_TARGET
----------------    ------- ------------------------
0000000085CA11A0   HIGH                          40

After 40 seconds, session 3 acquires the record, and the other two sessions are killed:

--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
Process ID: 76244
Session ID: 1391 Serial number: 61119

--session 2:
SQL> select sid from v$mystat where rownum=1;
ORA-03113: end-of-file on communication channel
Process ID: 76246
Session ID: 1408 Serial number: 35823

After these events, the following messages appear in the alert log:

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" 

Written by Vahid Yousefzadeh

I have been a DBA since 2011 and I work with Oracle technology. Linkdin: linkedin.com/in/vahidusefzadeh telegram channel ID:@oracledb vahidusefzadeh@gmail.com