Automatic transaction rollback in 23c

Automatic transaction rollback in 23c with high, medium and low priority transactions

Visit my group in LinkedIn to find more:

https://www.linkedin.com/groups/8151826

Automatic transaction rollback is another very interesting new topic in 23c. 

It provides functionality to control when transactions which are holding row locks can be automatically rolled back by terminating its session.

This feature means no more long transaction level locking or the infamous event ‘enq: TX row lock contention‘ or the pessimistic locking.

If a transaction does not commit or rollback for a long time while holding row locks, it can potentially block other high-priority transactions. 

This feature allows applications to assign priorities to transactions, and administrators to set timeouts for each priority. 

The database will automatically rollback a lower-priority transaction and release the row locks held if it blocks a higher-priority transaction beyond the set timeout, allowing the higher-priority transaction to proceed.

Automatic Transaction Rollback reduces the administrative burden while also helping to maintain transaction latencies/SLAs on higher-priority transactions.

For example let's assume an application modifies some rows for a long time and Oracle acquires a row lock for each row that is modified e.g. by any kind of DML operations. 

It can block another transaction on a row lock as long as it does not release the locked rows.To release the locked rows, a COMMIT or ROLLBACK must be issued in the session holding the lock. 

Sometimes it needs a database administrator to manually terminate the blocking transaction by killing the session with ALTER SYSTEM KILL or cancel the SQL statement with ALTER SYSTEM CANCEL SQL.

To implement the feature a decision needs to be taken in which case a transaction will be rolled back. Therefore different priorities for transactions are introduced. 

That means it is possible now to define the priority of your transaction. You can decide between low, medium and high (default). The Automatic Transaction Rollback feature will then automatically roll back low-priority transactions that are blocking higher priority transactions from obtaining row locks after a pre-defined wait time. 

The default priority for a transaction is always high and there is no automatic rollback enabled.

To implement this feature you need to set both parameters - one for the transaction priority and one for the wait time. Here is a short definition of the two parameters. 

In case of a row level locking or pessimistic level locking where a single row of a table was locked by one of the following statements INSERT, UPDATE, DELETE, MERGE, and SELECT … FOR UPDATE. 

The row level lock from first session will exist it performs the rollback or a commit. This situation becomes severe in some case i.e. The application modifies some rows but doesn’t commit or terminate the transaction because of an exception in the application. 

Traditionally, in such cases the database administrator have to manually terminate the blocking transaction by killing the parent session.

Oracle 23c has come up with a brilliant feature which it implements through a session settings to control the transaction priority. 

Transaction priority (TXN_PRIORITY) is set at session level using ALTER SESSION command. Once the transaction priority is set, it will remain the same for all the transactions created in that session. This parameter specifies a priority (HIGH, MEDIUM, or LOW) for all transactions in a user session. When running in ROLLBACK mode, you can track the performance of Automatic Transaction Rollback by monitoring the following statistics:

TXN_AUTO_ROLLBACK_HIGH_PRIORITY_WAIT_TARGET This param specifies the max number of seconds that a HIGH priority txn will wait for a row lock. Similarly, there is another parameter for MEDIUM classed statements .

TXN_AUTO_ROLLBACK_MEDIUM_PRIORITY_WAIT_TARGET which specifies the max number of seconds that a MEDIUM priority txn will wait for a row lock.

Both control the maximum time duration, in seconds. 

A transaction with priority HIGH & MEDIUM will wait this defined wait time before the database rolls back a lower priority transaction holding a row lock. 

The default value is the maximum number value.
Note: You need to have the ALTER SYSTEM privilege to configure this parameter. For example use the following  ALTER SYSTEM command to set the wait parameter to 25 seconds. It's modifiable in a PDB.

SQL> alter system set txn_auto_rollback_high_priority_wait_target = 25;

The transaction priority parameter is TXN_PRIORITY and can have the values LOW, MEDIUM, and HIGH. 

It can be defined with an ALTER SESSION command. 

SQL> alter session set txn_priority = low;

Note: All transactions have the default priority HIGH but changeable manually.

******************************

Regards,

Alireza Kamrani.

Senior Database Consultant.