Automatic Transaction Rollback in Oracle Database 23ai

In the Oracle Database 23ai Enterprise Edition, it is now possible to automatically rollback some transactions, and a set of new parameters have been added to control this behavior.

Of course implementing this feature requires deep understanding of your application, data model and source code.

The general idea :

* We don’t want critical transactions to be delayed for too long due to row locks caused by less important transactions.
* The new parameters would allow us to define if a transaction T1 is considered more important than a transaction T2.
* The new parameters would also let us set waiting thresholds, specifying how many seconds T1 will wait for the less important T2 to release the lock before forcing a rollback.

The parameters

The parameters that are controlling this feature are :

Let’s see what each parameter means :

1-TXN_PRIORITY

This is where we define each transaction importance, it is used at session level.

We use the alter session command; and HIGH is the default value.

2 – TXN_AUTO_ROLLBACK_HIGH_PRIORITY_WAIT_TARGET and TXN_AUTO_ROLLBACK_MEDIUM_PRIORITY_WAIT_TARGET :

These are the waiting thresholds in seconds, defined at SYSTEM level.
They controls how many seconds would a high or medium transaction wait for a lesser important transaction over the same row locks.

These targets have the following characteristics :

* The minimum possible value is 1. (1 second)
* It can be set at PDB level, so pluggable databases can have different thresholds.
* In a RAC database, instances can also be set at different values.

For instance, to set a target of 30 seconds for high priority transactions :

It is very important to note that this does NOT mean a high priority transaction will always wait for “only” 30 seconds.

For instance :

If you have a high transaction T1 waiting for a medium transaction T2, which is waiting for a low transaction T3, all for the same row locks, then the 30 seconds count will only start when T2 receive the lock, so you have to add also the time when T2 had been itself waiting for T3.

3- TXN_AUTO_ROLLBACK_MODE :

This is the feature’s mode, and it is defined at system level.
It may have as a value : “ROLLBACK” (the default) or “TRACK”.
If you set the mode at ‘track’, then the feature will just track and report the cases, no automatic rollback will happen.

So this mode (TRACK) could be a great way to test the feature’s effect before properly implementing it.

This parameter has the following characteristics :

* it is pdb mofifiable, so each pluggable database can have a different value.
* However RAC instances MUST have the same value.

Example

We will do a simple test : We will make a low priority session (Session 1) update a row without commiting, and then try to update the same row in a high priority session (Session 2).
We will also use this case to illustrate that the thresholds are PDB defined, and to check some ways to see statistics about rollback operations.

Let’s connect to our root container and set the priority waiting parameters :

[oracle@localhost ~]$ sqlplus / as sysdba



SQL> alter system set "txn_auto_rollback_high_priority_wait_target"=300;

System altered.

SQL> alter system set "txn_auto_rollback_medium_priority_wait_target"=300;

System altered.

SQL> select name,value from v$parameter where name like '%txn_priority%';

NAME                                                         VALUE
------------------------------------------------------------ ------------------------------------------------------------
txn_priority                                                 HIGH
txn_auto_rollback_high_priority_wait_target                  300
txn_auto_rollback_medium_priority_wait_target                300

The session priority is at HIGH because it is the default, and we have not changed it.
Now let’s connect to our pluggable database and verify our values :

SQL> alter session set container=DEVPDB;

Session altered.

SQL> select name,value from v$parameter where name like '%txn_priority%';

NAME                                                         VALUE
------------------------------------------------------------ ------------------------------------------------------------
txn_priority                                                 HIGH
txn_auto_rollback_high_priority_wait_target                  2147483647
txn_auto_rollback_medium_priority_wait_target                2147483647

The waiting values are different, because it is a pluggable level defined value, 2147483647 seconds is the default. let’s set it at 4 minutes :

SQL> alter system set "txn_auto_rollback_high_priority_wait_target"=160;

System altered.

SQL> alter system set "txn_auto_rollback_medium_priority_wait_target"=160;

System altered.

SQL> select name,value from v$parameter where name like '%txn_priority%';

NAME                                                         VALUE
------------------------------------------------------------ ------------------------------------------------------------
txn_priority                                                 HIGH
txn_auto_rollback_high_priority_wait_target                  160
txn_auto_rollback_medium_priority_wait_target                160

Now let’s set our current session at LOW priority and then make an update query without doing a commit or rollback :

-- Session 1 : Low priority
SQL> alter session set "txn_priority"="LOW";

SQL> select name,value from v$parameter where name like '%txn_priority%';

NAME                                                         VALUE
------------------------------------------------------------ ------------------------------------------------------------
txn_priority                                                 LOW
txn_auto_rollback_high_priority_wait_target                  160
txn_auto_rollback_medium_priority_wait_target                160

SQL> update hr.EMPLOYEES set SALARY=10000 where EMPLOYEE_ID=102;

1 row updated.

SQL>

Now let’s connect to a new session on the same pluggable database, and try to update the same row :

-- Session 2 : High Priority
SQL> select name,value from v$parameter where name like '%txn_priority%';

NAME                                                         VALUE
------------------------------------------------------------ ------------------------------------------------------------
txn_priority                                                 HIGH
txn_auto_rollback_high_priority_wait_target                  160
txn_auto_rollback_medium_priority_wait_target                160

SQL> update hr.EMPLOYEES set SALARY=9000 where EMPLOYEE_ID=102;

=> The prompt will not return as the session is waiting for session 1
Back in session 1, you can see the blocking information, please note the specific EVENT (row high priority)

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

       SID EVENT
---------- ----------------------------------------------------------------
SECONDS_IN_WAIT BLOCKING_SESSION
--------------- ----------------
        42 enq: TX - row (HIGH priority)
            132              197

After 4 minutes the prompt in session 2 will return, the query in session 1 had been rollbacked.
if you try to do a commit in session 1, you will receive an ORA-03135 error (Connection lost contact)

-- session 1 
SQL> commit;
commit
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 4905
Session ID: 197 Serial number: 46817
Help : https://docs.oracle.com/error-help/db/ora-03135/

SQL>

Back in session 2, you can do your commit and check the value of the row :

-- Session 2 
SQL> commit;

Commit complete.

SQL> select SALARY from hr.EMPLOYEES where EMPLOYEE_ID=102;

    SALARY
----------
      9000

For statistics about how many rollbacks did you have overall, you can query the v$sysstat view

SQL> select * from v$sysstat where name like '%txns rollback%';

STATISTIC# NAME                                                                  CLASS      VALUE    STAT_ID     CON_ID
---------- ---------------------------------------------------------------- ---------- ---------- ---------- ----------
      1894 txns rollback priority_txns_high_wait_target                            384          5  312271427          3
      1895 txns rollback priority_txns_medium_wait_target                          384          2 3671782541          3

SQL>

In this case, in our pluggable database (con_id=3) :

– 2 times did a medium priority transaction caused the rollback of a low priority transaction.
– 5 times did a high priority transaction caused the rollback of either a medium or low priority transaction.

If you have set you feature’s mode as “TRACK” instead of “ROLLBACK”, then you should search statistics that are like ‘%txns track%’ instead :

SQL> select * from v$sysstat where name like '%txns track%';

STATISTIC# NAME                                                                  CLASS      VALUE    STAT_ID     CON_ID
---------- ---------------------------------------------------------------- ---------- ---------- ---------- ----------
      1896 txns track mode priority_txns_high_wait_target                          384          0 2659394049          3
      1897 txns track mode priority_txns_medium_wait_target                        384          0 1855585812          3

In this case, no occurrences because I did not used it. As mentioned before, this mode (TRACK) could be a great way to test “What would happen” if you implement the feature without causing any rollbacks.