Prioritized Transactions in Oracle 26ai: Comparing Traditional Locking and Priority-Based Processing

Introduction

In my previous blog post, we explored how Oracle 26ai completely eliminates lock contention for numeric attributes using Lock-Free Reservations. But what happens when you have critical business logic that must rely on standard row locks, and a background process blocks a time-sensitive VIP operation?

Traditionally, Oracle operates on a strict first-come, first-served model. If a low-priority batch script updates a row first, a high-priority executive or customer-facing transaction is forced to sit in a queue waiting on enq: TX - row lock contention.

With Priority Transactions in Oracle 26ai, you can explicitly define transaction importance and set threshold policies. If a high-priority session hits a row locked by a lower-priority transaction, Oracle will automatically abort and roll back the blocking session after a configured wait period.

In this article, I will compare traditional row-level locking with Prioritized Transactions using practical examples and show how transaction priorities can change concurrency behavior under contention.

This article is part of a small series on modern concurrency features in Oracle 26ai, including Lock-Free Reservations, Prioritized Transactions, and Value-Based Concurrency Control.

1. The Traditional Way (Pessimistic Locking)

Before looking at Prioritized Transactions, let's first examine how traditional Oracle transactions behave during lock contention. In the traditional model, Oracle follows a first-come, first-served approach. Once a session acquires a row lock, every other session attempting to modify the same row must wait, regardless of the business importance of the transaction.

For this demonstration, we'll use the HR.EMPLOYEES table and treat the SALARY column as a simple account balance. The goal is to show how a low-priority transaction can block a more important transaction simply because it obtained the lock first.

To make the behavior easier to observe, I opened three SQL*Plus sessions connected to FREEPDB1 using the HR user:

• Session 1 (white terminal) performs the first update and holds the lock.
• Session 2 (black terminal) attempts to update the same row and becomes blocked.
• Monitoring Session (yellow terminal)  used to monitor locks, waits, and session activity.

Let's see what happens when both sessions attempt to update the same employee record. 

The employee with EMPLOYEE_ID = 100 initially has a salary of 26,900.

Session 1 (Low Priority)

In Session 1, at 02.49.46, I executed the following update:

UPDATE hr.employees 
SET salary = salary + 500 
WHERE employee_id = 100; 

The statement completed immediately, and the transaction was intentionally left uncommitted, causing Oracle to hold an exclusive lock on the row.

Session 2 (High Priority)

Six minutes later, at 02.55.58, I opened Session 2 and attempted to update the same row:

UPDATE hr.employees
SET salary = salary + 200
WHERE employee_id = 100;

Although Session 2 represents a more important business operation, the statement did not return. The session became blocked and waited because Session 1 had already acquired the row lock.

This highlights a limitation of the traditional locking model: Oracle does not consider transaction importance when resolving lock conflicts. A critical transaction can be delayed by a routine background task simply because it arrived later.

To confirm the blocking situation, I queried the monitoring session and checked the BLOCKING_SESSION information. The query showed that Session 2 was waiting on Session 1 and experiencing enq: TX - row lock contention.

Session 2 remains blocked and waits on enq: TX - row lock contention until Session 1 commits or rolls back the transaction. Despite being the more important business operation, Session 2 receives no special treatment under the traditional locking model.

Part 2: The Modern Way (Priority Transactions)

First, roll back the transactions in Sessions 1 and 2, then check the lock status in the monitoring session.

Now let's see how Priority Transactions can change this behavior. Instead of allowing a low-priority transaction to block a high-priority transaction for a long time, we can define transaction priorities and tell Oracle how long a high-priority transaction should wait.

Step 1: Configure Priority Transactions

First, connect as SYSDBA and configure the required parameters.

--Set the wait time for HIGH priority transactions
ALTER SYSTEM SET priority_txns_high_wait_target = 5; 
--Enable automatic rollback
ALTER SYSTEM SET priority_txns_mode = 'ROLLBACK';

The PRIORITY_TXNS_HIGH_WAIT_TARGET parameter defines how long a HIGH priority transaction can wait for a lock held by a lower-priority transaction. In our test, we set it to 5 seconds.

The PRIORITY_TXNS_MODE parameter controls what Oracle does after this wait time is reached.

• ROLLBACK – Oracle rolls back the blocking lower-priority transaction so the higher-priority transaction can continue.
• TRACK – Oracle only tracks and reports the events without actually rolling back the blocking transaction. This can be useful when testing the feature before enabling automatic rollback.

For this test, we will use ROLLBACK mode so we can see the behavior directly.

Next, we need to assign a priority to our transactions.

 Step 2: Assign Priorities to the Sessions

Now let's assign different priorities to the two sessions.

Session 1 – LOW Priority

In Session 1, set the transaction priority to LOW and execute the update. Do not commit the transaction.

ALTER SESSION SET txn_priority = LOW;

UPDATE hr.employees
SET salary = salary + 500
WHERE employee_id = 100;

The update completes and Session 1 holds the row lock.

Session 2 – HIGH Priority

Now, in Session 2, set the transaction priority to HIGH and execute the update on the same row.

ALTER SESSION SET txn_priority = HIGH;

UPDATE hr.employees
SET salary = salary + 200
WHERE employee_id = 100;

Session 2 starts waiting because Session 1 is holding the row lock.

The Result: Automatic Preemption

Now watch both sessions. After the configured 5-second wait target is reached, Oracle can preempt the lower-priority transaction so the HIGH-priority transaction can continue.

On Session 1 (LOW Priority): If you attempt to issue a COMMIT or run another statement in Session 1, Oracle halts the session and throws an eviction error:

This happens because the LOW priority transaction was blocking the HIGH priority transaction for longer than the configured 5-second wait target. Oracle rolled back the lower-priority transaction, allowing the higher-priority transaction to continue.

3. Head-to-Head Comparison

To understand when to use Priority-Based Processing, it helps to compare it with traditional row-level locking using the same salary/bank balance example.

Metric	Traditional Locking	Priority Transactions
Lock Priority Model	First-Come, First-Served (FIFO)	Business-Defined Priority Levels (HIGH, MEDIUM, LOW)
Concurrency Model	Pessimistic locking (row locked during update)	Pessimistic locking with active preemption
Row Contention Behavior	One session blocks others indefinitely on the same row	Lower-priority blockers are automatically evicted after timeout threshold
Wait Event	enq: TX - row lock contention	Short, bounded enq: TX - row lock (HIGH priority) wait
Session Control	Standard session behavior	ALTER SESSION SET TXN_PRIORITY = HIGH | MEDIUM | LOW
System Configuration	Default transaction queue	ALTER SYSTEM SET PRIORITY_TXNS_MODE = 'ROLLBACK' | 'TRACK'
Commit / Blocker Outcome	Holds lock until transaction manually commits or rolls back	Forced rollback (ORA-63302: Transaction must roll back)

Conclusion

In this article, we compared traditional row-level locking with Priority Transactions in Oracle 26ai. With traditional locking, a transaction can block other transactions regardless of their importance.With Priority Transactions, we can assign different priorities to transactions and define how long a higher-priority transaction should wait.

In our example, the HIGH priority transaction was able to continue after Oracle rolled back the blocking LOW priority transaction. This can be useful when critical transactions should not wait too long behind lower-priority work.

However, the application should be prepared to handle the rollback of lower-priority transactions.