Oracle Database 23ai Automatic Transaction Rollback

Scenarios like this are commonly faced ….

Joe Blog issues SQL statement:

UPDATE hr.employees SET salary=10001 WHERE emp_no=1001;

Goes for a coffee break!

Mary issues SQL statement:

UPDATE hr.employees SET salary=9999 WHERE emp_no=1001;

Now Mary has to wait till Joe issues a COMMIT or ROLLBACK ……but how long is the wait for??

A new feature added in Oracle Database 23ai is the ability to assign priority to transactions and limit how long a transaction should wait for a ‘blocking’ transaction to complete or be rolled back.

There are a number of parameters available in the 23ai database related to Automatic Transaction Rollback.

Transaction priority

TXN_PRIORITY – HIGH, MEDIUM, LOW

If a HIGH priority transaction is blocked, Oracle database can roll back any the transaction that is holding the row lock which belong to LOW or MEDIUM priority group.

If a MEDIUM priority transaction is blocked, Oracle database can roll back any the transaction that is holding the row lock which belong to LOW priority group.

Note:Oracle database never rolls back HIGH priority transaction.

How long will a ‘blocked’ Transaction wait

Maximum time duration specified in seconds, which a transaction with priority HIGH or MEDIUM will wait before the database rolls back a lower priority transaction holding row locks.

TXN_AUTO_ROLLBACK_HIGH_PRIORITY_WAIT_TARGET, TXN_AUTO_ROLLBACK_MEDIUM_PRIORITY_WAIT_TARGET

Setting Priority Transaction Mode

TXN_AUTO_ROLLBACK_MODE – TRACK or ROLLBACK

In TRACK mode the Priority Transaction feature can be tried out by the DBA. The database will increment statistics in V$SYSSTAT data dictionary view reflecting how many transactions this 23ai feature would have rolled back – instead of actually rolling back any transactions.

Let us view an example of this new 23ai feature.

View the current values of the parameters related to Automatic Transaction Rollback

[oracle@localhost ~]$  sqlplus system/oracle@freepdb1

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Mon May 29 05:39:21 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Last Successful login time: Mon May 29 2023 03:29:29 +00:00

Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0


SQL> show parameter txn_priority

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
txn_priority                         string      HIGH

SQL> show parameter txn_auto_rollback_high_priority_wait_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
txn_auto_rollback_high_priority_wait integer     2147483647
_target

SQL> show parameter txn_auto_rollback_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
txn_auto_rollback_mode               string      ROLLBACK

Change the HIGH priority wait time to 15 seconds.

SQL> alter system set  TXN_AUTO_ROLLBACK_HIGH_PRIORITY_WAIT_TARGET=15 scope=both;

System altered.

Establish two connections to the PDB.

For SESSION #1 set the TXN_PRIORITY value to LOW.

Issue an UPDATE transaction.

Note: Transaction is NOT committed at this stage.

SESSION 1

SQL>  alter session set TXN_PRIORITY=LOW;

Session altered.

SQL> update mytab set owner='JOE BLOG' where rownum=1;

1 row updated.

In SESSION #2, review the current values of the parameters related to Automatic Transaction Rollback.

Issue an UPDATE statement.

This UPDATE statement is blocked by SESSION #1 uncommitted UPDATE statement.

However, the UPDATE statement will wait for 15 seconds to elapse and then the transaction is completed.

SESSION 2 

SQL> show parameter txn_priority

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
txn_priority                         string      HIGH

SQL> show parameter txn_auto_rollback_high_priority_wait_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
txn_auto_rollback_high_priority_wait integer     15
_target


SQL>  update mytab set owner='JOE BLOGG' where rownum =1;

<<WAITS 15 SECONDS>>


SQL>  update mytab set owner='JOE BLOGG' where rownum =1;

1 row updated.

We can see that SESSION #1 transaction has got rolled back while SESSION #2 transaction can now be committed in the database.

SESSION 1


SQL> commit;
commit
     *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 135855
Session ID: 292 Serial number: 65272


SESSION 2

SQL> commit;

Commit complete.

Thanks!

Thanks to Kishy Kumar, an expert in databases and the lead developer of the Priority Transactions feature discussed in this article, for building this very helpful feature.