Managing transaction priority in Oracle 23c

Introduction

Out of the 300 features encapsulated in the new Oracle 23c database, I’ve chosen to write during the Oracle CloudWord a brief blog post about a feature that particularly caught my attention: Automatic Transaction Rollback.

Starting from version 23c, Oracle enables automatic transaction rollback with parameters to manage this functionality. Through this blog post I’ll demonstrate, with a practical example how this functionality works.

Use case scenario

Let’s consider the following scenario: two developers are working on the table “employees” , and a critical batch process runs every 30 minutes on this same table.

The first developer (Dev1) is performing an update operation that takes some time. Instead of waiting for the update to complete and deciding whether to commit or rollback the transaction, Dev1 decides to go to the cafeteria.

The second developer (Dev2) is also working on the same project and needs to update the same table shortly after Dev1 started his update. However, because of the lock created by Dev1’s update, Dev2 is unable to execute his transaction. Becoming impatient while waiting at the laptop, Dev2 decides to leave for home, leaving the transaction pending.

After a few minutes, the critical batch process (executed by “financial_app”) starts, but it too becomes stuck due to the ongoing update on the “employees” table.

Transaction Rollback with Oracle 23c

A situation like this can now be managed using Automatic Transaction Rollback. To set up transaction rollback, we need to consider two (or three) parameters:

1. Transaction priority (txn_priority), which can be set on session level to low, medium or high.
2. Time a transaction holding row lock can be automatically rolled back. This setting can be set in the system parameters (TXN_AUTO_ROLLBACK_HIGH_PRIORITY_WAIT_TARGET & TXN_AUTO_ROLLBACK_MEDIUM_PRIORITY_WAIT_TARGET)

Transaction priority

The priority can be changed on session level using the below syntax:

ALTER SESSION SET "txn_priority" = "HIGH";

As stated in the Oracle documentation:

• If a HIGH priority transaction is blocked for a row lock, Oracle database can roll back the transaction that is holding the row lock only if the holder is LOW or MEDIUM priority.
• If a MEDIUM priority transaction is blocked for a row lock, Oracle database can roll back the transaction that is holding the row lock only if the holder is LOW priority.
• If a LOW priority transaction is blocked for a row lock, Oracle database will not attempt to roll back the transaction holding the row lock irrespective of its priority.
• Oracle database never rolls back a HIGH priority transaction.

Time a transaction holding row lock can be automatically rolled back

There are two system parameters which define the time (in seconds) after a transaction holding row lock can be automatically rolled back:

1. TXN_AUTO_ROLLBACK_HIGH_PRIORITY_WAIT_TARGET 
2. TXN_AUTO_ROLLBACK_MEDIUM_PRIORITY_WAIT_TARGET 

There is no low priority wait target parameter provided since Oracle database doesn’t roll back a blocker transaction if waiter’s priority is LOW.

These system parameters can be defined using the below command (example for txn_auto_rollback_high_priority_wait_target):

ALTER SYSTEM SET txn_auto_rollback_high_priority_wait_target = 15;

Preparation of the environnement to run our example

Let’s revisit our example with two developers (dev1 and dev2) and one critical application (financial_app) and break down this scenario step by step:

Users creation

This step doesn’t add much to my example, except that it allows me to demonstrate the use of another new and interesting feature: the DB_DEVELOPER_ROLE. This role provides the fundamental roles and privileges that Oracle believes are essential for a database developer.

SQL> create user dev1 identified by dev1;
SQL> create user dev2 identified by dev2;
SQL> create user financial_app identified by financial_app;
SQL> alter user financial_app quota 128M on users;
SQL> grant DB_DEVELOPER_ROLE to dev1, dev2, financial_app;

… well let’s say two others features with the “grant on schema” features (schema level privilege):

SQL> grant select any table on schema financial_app to dev1, dev2;
SQL> grant insert any table on schema financial_app to dev1, dev2;
SQL> grant update any table on schema financial_app to dev1, dev2;

Table creation

Let’s create a very basic employees table:

SQL> CREATE TABLE financial_app.employees ( employee_id number(10) NOT NULL, employee_name varchar2(50) NOT NULL, city varchar2(50) );

We will now insert few rows:

SQL> insert into financial_app.employees (employee_id, employee_name, city) values (1, 'Westermann', 'Basel'), (2, 'Wisson', 'Nidau'), (3, 'Usai', 'Sutz'), (4, 'Berbier', 'Zurich');

Priority wait definition

For the sake of this example, we will assign different wait target to “Medium” and “High” priority transactions.

SQL> ALTER SYSTEM SET txn_auto_rollback_high_priority_wait_target = 15;
SQL> ALTER SYSTEM SET txn_auto_rollback_medium_priority_wait_target = 30;

Let’s run our example

As mentionned in our introduction we have three users with three different sessions (Dev1, Dev2 and financial_app). Dev1 and Dev2 will both update employees table and won’t commit nor rollback their transaction. Each of these three users will have a different priority:

Dev1 – priority low

[oracle@grs-oraclecloudinstance bin]$ ./sqlplus dev1/dev1
SQL> ALTER SESSION SET "txn_priority" = "LOW";

Dev2 – priority medium

[oracle@grs-oraclecloudinstance bin]$ ./sqlplus dev2/dev2
SQL> ALTER SESSION SET "txn_priority" = "MEDIUM";

financial_app – priority high

[oracle@grs-oraclecloudinstance bin]$ ./sqlplus financial_app/financial_app
SQL> ALTER SESSION SET "txn_priority" = "HIGH";

Update execution

Dev 1

First of all we have Dev1 who runs his update on employees table. In this example he only updates one row and it’s obviously very fast but anyway he do not commit his transaction.

SQL> UPDATE financial_app.employees set employee_name = 'Steulet' where employee_ID=1;
1 row updated.

SQL> SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') FROM dual;
TO_CHAR(SYSDATE,'MM
-------------------
09-21-2023 01:08:30

Dev2

5 second later, we have Dev2 who runs his update on employees table but as mentionned before Dev1 forgot to commit his transaction.

SQL> SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') FROM dual;
TO_CHAR(SYSDATE,'MM
-------------------
09-21-2023 01:08:35

SQL> UPDATE financial_app.employees set employee_name = 'Schweitzer' where employee_ID=1;

Here the transaction is stuck due to the row lock but after 30 seconds we get the below confirmation:

1 row updated.

SQL> SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') FROM dual;
TO_CHAR(SYSDATE,'MM
-------------------
09-21-2023 01:09:05

Financial_app

The second developer, dev2, also did not commit his transaction but the financial_app runs his update at 01:10:16:

SQL> SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') FROM dual;
TO_CHAR(SYSDATE,'MM
-------------------
09-21-2023 01:10:16

SQL> Update financial_app.employees set employee_name = '*';

As before the transaction his stuck due to the row lock but after 15 seconds we get the following confirmation:

1 row updated.

SQL> SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') FROM dual;
TO_CHAR(SYSDATE,'MM
-------------------
09-21-2023 01:10:31

What happened?

As often, a picture is worth a thousand words. The image below summarizes what happened with these three sessions.

User Dev 1 runs his update at 01:08:30, he immediately received the confirmation that his row was updated. However, he did not commit his transaction, which resulted in a lock on the employees’ table.

5 seconds later at 01:08:35, Dev2 also performed an update on the same table. He did not receive an immediate confirmation of his update. However, after 30 seconds, at 01:09:05, the confirmation arrived. This eventually caused the session of Dev1 to end with a rollback of his transaction. Dev 2, did not commit his transaction neither, which also resulted in a lock on the employee’s table.

Few seconds later at 01:10:16, financial_app runs his batch on employees table. As for Dev2, the batch was not able to execute immediately the update. However after 15 seconds, the confirmation arrived. This caused the session of Dev2 to end with a rollback of his transaction.

The content of the employees’ table at 01:10:31 in the financial_app’s session is as follows:

SQL> set linesize 200
SQL> select * from financial_app.employees;
EMPLOYEE_ID EMPLOYEE_NAME             CITY
----------- ------------------------- ---------------------
          1 *                         Basel
          2 *                         Nidau
          3 *                         Sutz
          4 *                         Zurich