Automatic Transaction Rollback feature in 23C

 Automatic Transaction Rollback feature in 23C

The Row lock contention has been a major pain area for developers specially when it comes to releasing locks in case there is an exception thrown by a transaction from an application. The developers in most cases take help from the DBAs to kill their blocking parent session or in essense kill the final blocking session if there is a chain of row locks under the lock tree.

The Automatic Transaction Rollback feature automatically rolls back low-priority transactions that are blocking higher priority transactions from obtaining row locks.

This parameter is one of four initialization parameters that enable you to configure Automatic Transaction Rollback. The other parameters are:

TXN_PRIORITY - Specifies a priority (HIGH, MEDIUM, or LOW) for all transactions in a user session

TXN_AUTO_ROLLBACK_MEDIUM_PRIORITY_WAIT_TARGET - Specifies the maximum number of seconds that a MEDIUM priority transaction will wait for a row lock

TXN_AUTO_ROLLBACK_MODE - Specifies whether to enable Automatic Transaction Rollback or test Automatic Transaction Rollback by running in tracking mode

Note: This parameter is available starting with Oracle Database 23c.

Lets see what wait events have been introduced with the 23c version :

SQL> select event#, name, WAIT_CLASS from v$event_name where name like '%TX - row%';

    EVENT# NAME                                                             WAIT_CLASS

---------- ---------------------------------------------------------------- ----------------------------------------------------------------

       340 enq: TX - row lock contention                                    Application

       341 enq: TX - row lock (HIGH priority)                               Application

       342 enq: TX - row lock (MEDIUM priority)                             Application

       343 enq: TX - row lock (LOW priority)                                Application

The parameter txn_auto_rollback_medium_priority_wait_target default value is 2147483647, The TXN_AUTO_ROLLBACK_HIGH_PRIORITY_WAIT_TARGET specifies the maximum number of seconds that a HIGH priority transaction will wait for a row lock before the Automatic Transaction Rollback feature rolls back a lower priority transaction holding the lock.

SQL> show parameter TXN_AUTO_ROLLBACK_MEDIUM_PRIORITY_WAIT_TARGET

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

txn_auto_rollback_medium_priority_wa integer     2147483647

it_target

SQL> col NAME For a100

SQL> show parameter TXN_AUTO_ROLLBACK_MEDIUM_PRIORITY_WAIT_TARGET

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

txn_auto_rollback_medium_priority_wa integer     2147483647

it_target

SQL>

# Now since this is a static parameter, it needs a database restart.

SQL> alter system set txn_auto_rollback_medium_priority_wait_target=50 scope=spfile;

System altered.

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1608409424 bytes

Fixed Size                 10043728 bytes

Variable Size             402653184 bytes

Database Buffers         1191182336 bytes

Redo Buffers                4530176 bytes

Database mounted.

Database opened.

Lets confirm the value :

SQL> show parameter TXN_AUTO_ROLLBACK_MEDIUM_PRIORITY_WAIT_TARGET

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

txn_auto_rollback_medium_priority_wa integer     50

Session 1 with Medium priority: 

SQL> select * from t1;

         I         I2

---------- ----------

         1          1

         2          2

         3          3

         4          4

         5          5

SQL>  alter session set TXN_PRIORITY=MEDIUM;

Session altered.

SQL> update t1 set i=11 where i=1;

1 row updated.

Session 2 with High Priority: 

SQL> alter session set TXN_PRIORITY=HIGH;

Session altered.

SQL> show parameter TXN_AUTO_ROLLBACK_MEDIUM_PRIORITY_WAIT_TARGET

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

txn_auto_rollback_medium_priority_wa integer     50

SQL> select * from t1;

         I         I2

---------- ----------

         1          1

         2          2

         3          3

         4          4

         5          5

SQL> update t1 set i=1001 where i=1;

>>> Hangs here >>>>>

Session 3 DBA session Monitors the wait :

SQL> select sid,EVENT,WAIT_CLASS,WAIT_TIME,SECONDS_IN_WAIT,STATE from v$session_wait where event like '%lock%';

       SID EVENT                                    WAIT_CLASS            WAIT_TIME SECONDS_IN_WAIT STATE

---------- ---------------------------------------- -------------------- ---------- --------------- -------------------

       112 enq: TX - row lock (HIGH priority)       Application                   0              22 WAITING

SQL> select sid,EVENT,WAIT_CLASS,WAIT_TIME,SECONDS_IN_WAIT,STATE from v$session_wait where event like '%lock%';

       SID EVENT                                    WAIT_CLASS            WAIT_TIME SECONDS_IN_WAIT STATE

---------- ---------------------------------------- -------------------- ---------- --------------- -------------------

       112 enq: TX - row lock (HIGH priority)       Application                   0             49 WAITING

Now go to session 1, the one with MEDIUM priority :

SQL> select * from t1;

select * from t1

       *

ERROR at line 1:

ORA-03135: connection lost contact

Process ID: 1043

Session ID: 31 Serial number: 10449

Now go to session 2 >> High Priority session :

SQL> update t1 set i=1001 where i=1;

1 row updated.

SQL> 

SQL> select * from t1;

         I         I2

---------- ----------

      1001          1

         2          2

         3          3

         4          4

         5          5

# This clearly explains how high priority session takes priority over low priority sessions. Happy 23C #Oracle.