Oracle Database 26AI - Priority Transactions

Effective transaction management is central to running a high‑performance, highly available Oracle Database environment. With Oracle Database 26AI, the database engine introduces enhanced capabilities that improve how blocked transactions are handled, how row‑level locks are controlled, and how long‑running or stalled sessions are recovered. This post provides a clear, DBA‑friendly overview of these features and how to use them in real environments.

What Is a Transaction in Oracle?

A transaction represents a logical, atomic unit of work that includes one or more SQL statements executed as a group. Oracle ensures that the entire group is either fully committed or fully rolled back, maintaining the classic ACID guarantees. This means your database always transitions from one consistent state to another.

Transactions implicitly begin with the first executable SQL statement and end either when you issue a COMMIT, a ROLLBACK, or an implicit commit occurs (for example, during DDL operations).

Understanding Row Locks

Whenever a transaction modifies data via INSERT, UPDATE, DELETE, MERGE, or SELECT…FOR UPDATE - Oracle acquires row‑level locks to ensure data consistency across concurrent sessions. These locks persist until the transaction completes. Long‑running or uncommitted sessions can therefore hold row locks for extended periods and block other operations.

Traditionally, a DBA had to manually terminate blocking sessions using commands like:

ALTER SYSTEM KILL SESSION '<sid>,<serial#>';

But Oracle 26AI brings smarter solutions.

Priority Transactions: One of 26AI’s Most Useful Enhancements

Oracle Database 26c introduces Priority Transactions, a feature that allows the database to automatically roll back low‑priority transactions when they are blocking higher‑priority workloads. This is a major improvement for OLTP systems or mission‑critical applications where latency matters.

Setting Priority Transaction Mode

Priority Transaction supports two modes so that you can try out this feature before enabling it.

The default mode for priority transactions is ROLLBACK. In this mode, if PRIORITY_TXNS_HIGH_WAIT_TARGET and PRIORITY_TXNS_MEDIUM_WAIT_TARGET are appropriately configured, transactions that are holding row locks and blocking higher priority transactions would be automatically rolled back and allow higher priority waiting transactions to progress.

The purpose of TRACK mode is for database administrators to try out the priority transactions feature.

In TRACK mode, the database will increment statistics in V$SYSSTAT, reflecting how many transactions this feature would have rolled back, instead of actually rolling back any transactions. Applications can use this mode to tune the right wait target value before switching to ROLLBACK mode.

To set priority transaction mode to TRACK, use the following command:

ALTER SYSTEM SET “priority_txns_mode”="TRACK";

To set priority transaction mode to ROLLBACK, use the following command:

ALTER SYSTEM SET "priority_txns_mode"="ROLLBACK";

Why this matters

• Prevents system slowdowns caused by forgotten or stuck transactions.
• Reduces DBA intervention for blocking sessions.
• Improves application responsiveness and concurrency.

How to Use TRACK Mode

1.               Set ALTER SYSTEM SET “priority_txns_mode”="TRACK";

2. Run your normal workload for several hours or an appropriate test window.
3. Monitor the wait times for row lock contention events.
4. Identify the maximum normal wait durations for each priority level.

For example:
If HIGH‑priority transactions typically wait up to 10 seconds, Oracle recommends setting PRIORITY_TXNS_HIGH_WAIT_TARGET to something significantly higher, such as 90 seconds.
This prevents Oracle from prematurely rolling back legitimate transactions that are performing meaningful work.

Once you determine the optimal values:

• Turn off TRACK mode
• Switch to ROLLBACK mode
• Configure the system-level wait target parameters
• Begin using priority transactions in production

How Wait Events Behave Under Priority Transactions

When multiple sessions contend for the same row lock, Oracle typically queues them under the wait event: enq: TX - row lock contention

However, with priority transactions enabled, waiting sessions are routed to priority-specific wait events, allowing DBAs to instantly see how locks impact transactions by priority.

Priority-Based Wait Events

Waiting Transaction Priority	Wait Event
HIGH	enq: TX - row lock contention (HIGH pri)
MEDIUM	enq: TX - row lock contention (MEDIUM pri)
LOW	enq: TX - row lock contention (LOW pri)

This granular visibility helps identify whether low‑priority workloads are hindering mission‑critical operations.

Monitoring Priority Transactions

Oracle provides fixed views that allow DBAs to track:

• Transactions affected by priority settings
• Wait events and target timeouts
• Automatically rolled‑back sessions

These views help DBAs fine‑tune timeout settings and verify that high‑priority workloads are progressing efficiently.

Two columns are available in V$TRANSACTION to aid in monitoring transactions. 

TXN_PRIORITY shows the transaction priority

 PRIORITY_TXNS_WAIT_TARGET shows the wait target for the transaction specified in seconds.

Alerts are shown in the alert log whenever a transaction is terminated. For example:

Transaction (sid: 203, serial: 39661, xid: 7.23.1161, txn_priority: "LOW") terminated by transaction (sid: 204, serial: 9266, xid: 13.15.3, txn_priority: "HIGH") because of the parameter "priority_txns_high_wait_target = 10"

TXN_PRIORITY cannot be set for a scheduler job. If it is set for a scheduler job, error ORA-63303 is thrown and reported in the alert log.

The following SQL query shows active sessions waiting on row locks, along with the priority‑specific wait events and the blocking session:

SELECT

    TO_CHAR(xidusn) || '.' || TO_CHAR(xidslot) || '.' || TO_CHAR(xidsqn) AS transaction_id,

    sid,

    event,

    seconds_in_wait,

    blocking_session

FROM

    v$session,

    v$transaction

WHERE

    event LIKE '%enq%'

    AND v$session.saddr = v$transaction.ses_addr;

Sample Output Interpretation

transaction_id	SID	Event	seconds_in_wait	blocking_session
2.17.1619	187	enq: TX - row lock (HIGH priority)	361	204
5.32.1557	51	enq: TX - row lock (LOW priority)	…

 Here, session 204 holds a HIGH‑priority transaction, while other sessions regardless of priority are waiting based on Oracle’s priority-aware queuing.

Distributed and XA Transactions

Distributed and two‑phase commit environments add complexity because the transaction spans multiple nodes or external resources. Oracle's priority rollback rules differ for:

• XA transactions
• Distributed transactions

DBAs should review the restrictions and behaviours documented for priority transactions before enabling them in such environments.

Conclusion

Oracle Database 26AI significantly improves the way transactions are managed, particularly in high‑volume, high‑priority environments. With smarter rollback automation, enhanced monitoring, and clearer rules for priority management, DBAs can ensure that no single stuck session impacts the overall system health.

If you're designing or maintaining OLTP systems, taking advantage of these new features can greatly reduce operational overhead and improve performance stability.