Priority Transactions in Oracle Database 26ai

The Oracle database supports automatic rollback of transactions and provides parameters to control this behavior.

A row lock restricts access to a single row within a table. Transactions acquire a row lock whenever they execute statements such as `INSERT`, `UPDATE`, `DELETE`, `MERGE`, or `SELECT … FOR UPDATE`. This lock remains in place until the transaction is either committed or rolled back. In certain situations, transactions can hold row locks for an extended period. For example, an application may modify rows but fail to commit or terminate the transaction due to an exception. Traditionally, if a transaction was blocked for a long time by another transaction holding a row lock, the database administrator needed to manually terminate the blocking session using the `ALTER SYSTEM KILL SESSION` command.

Starting with Oracle Database 26ai, administrators can configure parameters to determine which transactions holding row locks can be rolled back automatically and when. The database rolls back the transaction, but the session remains active. The application must issue a `ROLLBACK` statement to acknowledge the automatic rollback.

Applications can also assign a priority to their transactions. If a low-priority transaction blocks a high-priority transaction, Oracle will automatically roll back the low-priority transaction to allow the higher-priority transaction to proceed.

Administrators can set the amount of time a low-priority transaction can hold a lock before it is automatically rolled back.

Priority Transactions introduce a smarter way to handle locking:

• Assign priorities to transactions
• Protect critical operations
• Automatically rollback less important transactions if needed

This shifts Oracle from a “first-come, first-served” model to a “business-priority-driven” model.

A low-value transaction can block a high-value one.

Using Priority Transactions

Oracle Database provides session settings to control the transaction priority.

Transaction 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.

ALTER SESSION SET "txn_priority" = "HIGH";

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.

Wait Threshold (Key Parameter)

ALTER SYSTEM SET priority_txns_high_wait_target = 15;

• A HIGH priority transaction waits up to 15 seconds
• If still blocked → Oracle automatically rolls back the blocking transaction

Setting Priority Transaction Mode

ALTER SYSTEM SET "priority_txns_mode"="TRACK";

ALTER SYSTEM SET "priority_txns_mode"="ROLLBACK";

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"

Below example demonstrate how a HIGH-priority transaction can preempt a LOW-priority transaction that blocks a row.

Step 0: Setup – Create a Test Table

-- Create a simple table
CREATE TABLE orders (
order_id NUMBER PRIMARY KEY,
status VARCHAR2(20)
);

-- Insert some test data
INSERT INTO orders VALUES (1, 'PENDING');
INSERT INTO orders VALUES (2, 'PENDING');
COMMIT;

Step 1: Session 1 – LOW Priority Transaction (Blocking)

1. Open a new SQL session/window.
2. Set the transaction priority to LOW:

ALTER SESSION SET txn_priority = LOW;

3. Begin transaction and lock a row:

-- Lock order_id = 1
UPDATE orders SET status = 'PROCESSING' WHERE order_id = 1;
-- Do not commit yet

At this point, order_id = 1 is locked by this LOW-priority transaction.

Step 2: Session 2 – HIGH Priority Transaction (Waiting)

1. Open a second SQL session/window.
2. Set transaction priority to HIGH:

ALTER SESSION SET txn_priority = HIGH;

3. Begin transaction and try to update the same row:

UPDATE orders SET status = 'COMPLETED' WHERE order_id = 1;

• By default, this HIGH transaction waits because the row is locked by LOW.
• Oracle waits up to the configured wait target (priority_txns_high_wait_target) seconds.

Step 3: System Behavior – Automatic Rollback

1. Configure wait target (if not set):

ALTER SYSTEM SET priority_txns_high_wait_target = 10; -- 10 seconds
ALTER SYSTEM SET priority_txns_mode = ROLLBACK;

2. If the HIGH transaction waits longer than 10 seconds:

• Oracle automatically rolls back the LOW-priority transaction
• The HIGH-priority transaction proceeds

Step 4: Session 1 Reaction

After the automatic rollback:

• LOW transaction is terminated
• HIGH transaction succeeds

Step 5: Verify Results

-- Check status
SELECT * FROM orders;

Step 6: Optional – TRACK Mode Demo

1. Change to TRACK mode first (simulation):

ALTER SYSTEM SET priority_txns_mode = TRACK;

2. Repeat Steps 1–2:

• No rollback occurs
• LOW transaction still blocks HIGH
• Use this to observe which transactions would be rolled back in production without affecting live data

As a result :

• HIGH-priority transaction never waits indefinitely
• LOW-priority transaction can be rolled back automatically
• TRACK mode lets you simulate before enforcing rollback
• Useful for real-time payments, checkout systems, or critical microservices