Auto Transaction Rollback in Oracle 23c – Is this the end of Row Lock Contention in Oracle Database ?
Posted by FatDBA on April 14, 2023
Hi Guys,
Oracle 23c is full of great features, one of the outstanding feature added to the version is the Automatic Transaction Rollback … Means no more long transaction level locking or the infamous event ‘enq: TX row lock contention‘ or the pessimistic locking 🙂
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.
Lets do a quick demo to explain this behavior in details.
I have created a small table with two rows and two columns and will use it for this demo to test automatic txn rollback features. To show a quick demo, I will set txn_auto_rollback_high_priority_wait_target to a lower value of 15 seconds. Will issue an UPDATE statement from the first session after setting the TXN_PRIORITY to ‘LOW‘ at the session level and will open a parallel session (session 2) and issue the same statement where the it will try to modify the same row already in exclusive lock mode by session 1.
--------------------------------------
-- SESSION 1
--------------------------------------
[oracle@mississauga ~]$ sqlplus / as sysdba
SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Fri Apr 14 22:46:34 2023
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL>
SQL> select * from dixit;
ID NAME
---------- --------------------
999 Fatdba
101 Prashant
SQL> select to_number(substr(dbms_session.unique_session_id,1,4),'XXXX') mysid from dual;
MYSID
----------
59
SQL> show parameter TXN_AUTO_ROLLBACK_HIGH_PRIORITY_WAIT_TARGET
NAME TYPE VALUE
-------------------------------------------- ----------- ------------------------------
txn_auto_rollback_high_priority_wait_target integer 15
SQL> alter session set TXN_PRIORITY=LOW;
Session altered.
SQL> show parameter TXN_PRIORITY
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
txn_priority string LOW
-- I will now issue update and don't issue ROLLBACK or COMMIT
SQL> update dixit set id=101010101 where name='Fatdba';
1 row updated.
SQL >
Okay so the stage is set! We’ve already ran an UPDATE statement on the table from SESSION 1 (SID : 59) and I will open a new session (session 2) and issue the same statement, but here the txn_priority is set to its default ‘HIGH‘ and we’ve already set txn_auto_rollback_high_priority_wait_target to 15 seconds earlier.
--------------------------------------
-- SESSION 2
--------------------------------------
[oracle@mississauga ~]$ sqlplus / as sysdba
SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Fri Apr 14 22:46:34 2023
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL>
SQL> select to_number(substr(dbms_session.unique_session_id,1,4),'XXXX') mysid from dual;
MYSID
----------
305
SQL> show parameter TXN_PRIORITY;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
txn_priority string HIGH
-- Now this session will go into blocking state.
SQL> update dixit set id=0 where name='Fatdba';
...
.....
Alright, so session 2 (SID : 305) with txn_priority=HIGH is now blocked, as the row was first locked in exclusive mode by session 1 (SID : 59), but we’ve set TXN_PRIORITY=LOW (at session level) and system level change of TXN_AUTO_ROLLBACK_HIGH_PRIORITY_WAIT_TARGET to 15 seconds,
Lets query the database and see what is waiting on what ?? You will see SID 305 (session 2) is waiting for the txn level lock and waiting on event ‘enq: TX – row lock (HIGH priority)‘. BTW, this also a new event added into Oracle 23c for sessions waiting with HIGH priorities, other two are for LOW and MEDIUM priorities.
SQL>
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
SQL>
-----------------------------------------------------------------
-- Contention details (What has blocked what ?)
-----------------------------------------------------------------
SQL>
INST_ID SID SERIAL# USERNAME SQL_ID PLAN_HASH_VALUE DISK_READS BUFFER_GETS ROWS_PROCESSED EVENT
---------- ---------- ---------- -------------------------------------------------------------------------------------------------------------------------------- ------------- --------------- ---------- ----------- -------------- ----------------------------------------------------------------
OSUSER STATUS BLOCKING_SE BLOCKING_INSTANCE BLOCKING_SESSION PROCESS MACHINE PROGRAM
-------------------------------------------------------------------------------------------------------------------------------- -------- ----------- ----------------- ---------------- ------------------------ ---------------------------------------------------------------- ------------------------------------------------------------------------------------
MODULE ACTION LOGONTIME LAST_CALL_ET SECONDS_IN_WAIT STATE
---------------------------------------------------------------- ---------------------------------------------------------------- ------------------- ------------ --------------- -------------------
SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
RUNNING_SIN
-----------
1 305 15926 SYS 9jwbjqg195zdw 2635034114 0 6 0 enq: TX - row lock (HIGH priority)
oracle ACTIVE VALID 1 59 8808 mississauga.candomain sqlplus@mississauga.candomain (TNS V1-V3)
sqlplus@mississauga.candomain (TNS V1-V3) 04-14-2023 22:46:35 12 13 WAITING
update dixit set id=00000000 where name='Fatdba'
00:00:12
The session 2 (SID 305) will wait for 15 seconds and database will automatically snipes session 1 (SID 59) due to LOW priority and UPDATE issued by Session 2 will persist, whereas session 1 (SID 59) and will throw “ORA-03135: connection lost contact”.
-- SESSION 1 with SID 59
SQL>
SQL> select * from dixit;
select * from dixit
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 8843
Session ID: 59 Serial number: 31129
-- SESSION 2 with SID 305
SQL> update dixit set id=0 where name='Fatdba';
1 row updated.
SQL> select * from dixit;
ID NAME
---------- --------------------
0 Fatdba
101 Prashant
SQL>
Hope It Helped!
Prashant Dixit
Tales From A Lazy Fat DBA 
reachsyed7 said
Hello Prashant,
you pen down the articles very eloquent…
please keep posting..
need to enquire if you are taking any classes for same.
please let me know..
regards
Rahman
reachsyed7@gmail.com
FatDBA said
Hi Rahman,
Thanks for your kind words my friend but at the moment I have put sessions on hold as I have migrated to a new place. I will update you once I am back