enq: TX – Row Lock Contention Handled in Oracle 23ai

What is "enq: TX - row lock contention" ?

A row is locked when one session attempts to modify the data in that row while another session simultaneously tries to modify the same row. The 1st session will be blocker for the 2nd session(waiter).

Let’s look at an example. Session (sid:202) first executes an UPDATE on table DB without issuing a COMMIT or ROLLBACK. At the same time, session (sid:203) attempts to modify the same row, but it is blocked by session (sid:202). The lock will be released only when the blocking session (sid:202) either commits or rolls back the transaction.

Blocking sessions can remain in the database for a long time and may impact critical application transactions. In such cases, a DBA may need to terminate the blocking sessions to release the locks and allow the waiting transactions to proceed.

Oracle 23ai introduced a new feature "Priority Transactions" which allows LOW priority transactions to be automatically rolled back. This behaviour is controlled by new priority transaction parameters.

This behavior is controlled by new priority transaction parameters that determine which transactions holding row locks can be rolled back automatically. When this occurs, the database rolls back the transaction, but the session itself remains active. The application must then acknowledge the automatic rollback by explicitly issuing a ROLLBACK statement.

Let’s take a closer look at the new Priority Transaction parameters introduced in Oracle 23ai.

TXN_PRIORITY
PRIORITY_TXNS_MODE
PRIORITY_TXNS_HIGH_WAIT_TARGET
PRIORITY_TXNS_MEDIUM_WAIT_TARGET

The Priority Transactions feature automatically rolls back low-priority transactions that blocks high priority transactions.

1) TXN_PRIORITY: [LOW|MEDIUM|HIGH]

HIGH: By default, no transaction will be rolled back in the database which means that all the transactions get HIGH priority by default.

MEDIUM: Priority Transactions roll back a MEDIUM priority transaction if it blocks a HIGH priority transaction that is waiting for the row lock longer than the time specified by the PRIORITY_TXNS_HIGH_WAIT_TARGET parameter.

LOW: Priority Transactions roll back a LOW priority transaction if it blocks a HIGH or MEDIUM priority transaction that is waiting for the row lock longer than the time specified by PRIORITY_TXNS_HIGH_WAIT_TARGET or PRIORITY_TXNS_MEDIUM_WAIT_TARGET parameter.

Note: If a LOW priority transaction is blocked waiting for a row lock, Priority Transactions does not roll back the transaction, irrespective of its priority. Oracle DB never rolls back a HIGH priority transaction. Similarly, Oracle never rolls back MEDIUM or HIGH priority transactions if the waiting session's priority is LOW.

2) PRIORITY_TXNS_MODE: [ROLLBACK|TRACK]

ROLLBACK: This is the default one and enables Priority Transactions to automatically rolls back the transactions that are blocking MEDIUM or HIGH priority transactions.

TRACK: This enables to track the behavior of Priority Transactions. With this, we can tune Priority Transactions initialization parameters for optimum value post which we can set ROLLBACK mode. In TRACK mode, based on existing Priority Transactions parameters values, it determines when HIGH and MEDIUM priority transactions would roll back. In this mode, database does not rolls back the transactions, instead it increments the below stats:

- txns track mode priority_txns_high_wait_target

- txns track mode priority_txns_medium_wait_target

3) PRIORITY_TXNS_HIGH_WAIT_TARGET:

This parameter specifies maximum number of seconds that a HIGH priority transaction will wait for a row lock.

4) PRIORITY_TXNS_MEDIUM_WAIT_TARGET:

This parameter specifies maximum number of seconds that a MEDIUM priority transaction will wait for a row lock.

Let’s demonstrate this with an example.

We will use three sessions in this demonstration:

Session 1 (Monitoring): To observe blocking and waiting sessions.

Session 2 (Update Query): To execute 1st UPDATE query and hold the row lock.

Session 3 (Update Query): To attempt updating the same row, which will be blocked until the lock is released.

Session1: (SID:33)

#Create a test table and insert some values.

SQL> create table DB(db_id number, db_name varchar2(12));

Table created.

SQL> insert into DB values(1,'ORACLE');

1 row created.

SQL> insert into DB values(2,'MS-SQL');

1 row created.

SQL> insert into DB values(3,'Mysql');

1 row created.

SQL> insert into DB values(4,'PostgreSQL');

1 row created.

SQL> insert into DB values(5,'MongoDB');

1 row created.

SQL> insert into DB values(6,'Cassandra');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from DB;

DB_ID DB_NAME

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

1 ORACLE

2 MS-SQL

3 Mysql

4 PostgreSQL

5 MongoDB

6 Cassandra

6 rows selected.

Let’s set the Transaction Priority parameter values to the minimum so that we can observe the output quickly.

Session1: (SID:33)

#Let’s review the current parameter values.

SQL> show parameter priority

NAME VALUE

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

txn_priority HIGH

priority_txns_mode ROLLBACK

priority_txns_high_wait_target 2147483647

priority_txns_medium_wait_target 2147483647

#Now, update the default parameter values to 15 seconds.

SQL> alter system set priority_txns_high_wait_target=15;

System altered.

SQL> alter system set priority_txns_medium_wait_target=15;

System altered.

#Let’s check the parameter values again to confirm the changes.

SQL> show parameter priority

NAME VALUE

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

txn_priority HIGH

priority_txns_mode ROLLBACK

priority_txns_high_wait_target 15

priority_txns_medium_wait_target 15

#Check the sessions in the database.

set lines 300 pages 3000

col USERNAME for a12

col event for a34

set numwidth 5

select sid,serial#,username,status,event,sql_id,SECONDS_IN_WAIT wait_secs,blocking_session BLK from v$session where username is not null and status='ACTIVE';

Now in Session2(206), set the txn_priority to LOW and execute the update query.

Session2: (SID:206)

#Set the txn_priority to LOW in this session.

SQL> set time on timing on

23:20:52 SQL> select distinct sid from v$mystat;

SID

----------

206

23:30:57 SQL> alter session set txn_priority = LOW;

Session altered.

23:30:59 SQL> show parameter txn_priority

NAME TYPE VALUE

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

txn_priority string LOW

23:31:06 SQL> update DB set DB_NAME='DB2' where db_id=5;

1 row updated.

23:31:14 SQL>

Now, in Session 3 (sid:47), there’s no need to set txn_priority to HIGH since it is already the default. Execute the same UPDATE query.

Session3: (SID:47)

#Execute the same update query in this session and observe the results.

SQL> set time on timing on

23:20:53 SQL> select distinct sid from v$mystat;

SID

----------

Elapsed: 00:00:00.01

23:34:56 SQL> show parameter txn_priority

NAME TYPE VALUE

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

txn_priority string HIGH

23:34:58 SQL> update DB set DB_NAME='DB2' where db_id=5;

You can monitor the sessions in Session1(sid:33).

Session1: (SID:33)

#Let’s monitor the session logs to observe the transaction flow based on priority.

23:21:21 SQL> set lines 300 pages 3000

23:21:22 SQL> col USERNAME for a12

23:21:22 SQL> col event for a34

23:21:22 SQL> set numwidth 5

23:21:22 SQL> select sid,serial#,username,status,event,sql_id,SECONDS_IN_WAIT wait_secs,blocking_session BLK from v$session where username is not null and status='ACTIVE';