Priority Transactions in Oracle AI Database 26ai: When a Batch Blocks a Payment, Measured

 

Tested on: Oracle AI Database 26ai (23.26.4.1.0) on Oracle Autonomous AI Database | Level: Intermediate

Every banking DBA knows this incident: a batch job updates an account, keeps its transaction open, and a customer payment on the same account sits waiting on a row lock until someone finds the blocker and kills it. Oracle AI Database 26ai can make that decision automatically. With Priority Transactions, you mark sessions as LOW, MEDIUM or HIGH priority and give high-priority work a maximum wait. When the wait runs out, the database rolls back the lower-priority blocker. I tested it with a batch and a payment fighting over one account: the payment's wait dropped from 12.9 seconds to 4.7 seconds, the batch received ORA-63300, and the account balance stayed exactly right.

Results at a glance

Test	Batch priority	Payment waited	What happened to the batch
T0 Default settings	LOW	12.92 s	Finished and committed after 15 s
T1 HIGH wait target 5 s	LOW	4.68 s	Rolled back with ORA-63300
T2 HIGH wait target 5 s	MEDIUM	3.95 s	Rolled back with ORA-63300
T3 HIGH wait target 5 s	HIGH	9.93 s	Not touched (equal priority), committed after 12 s
T4 TRACK mode (dry run)	LOW	12.92 s	Not touched, but counted in V$SYSSTAT

How Priority Transactions work

• Each session sets its own priority with ALTER SESSION SET txn_priority = LOW | MEDIUM | HIGH. The default is HIGH.
• The DBA sets how long a HIGH or MEDIUM transaction may wait for a row lock held by a lower-priority transaction: priority_txns_high_wait_target and priority_txns_medium_wait_target, in seconds. By default both are effectively unlimited, so nothing changes until you set them.
• When the wait target is reached, the database rolls back the blocking transaction. The blocked transaction gets its lock and continues.
• priority_txns_mode is ROLLBACK by default. TRACK only counts what would have been rolled back, which makes it a safe dry run.

The lab

One table of ten fictional accounts. In each test, a batch session sets its priority, updates account 1 (a debit of 100) and keeps working for 12 to 25 seconds before committing. About 2 seconds later a payment session tries to credit 100 to the same account and has to wait for the row lock.

-- The batch (this session)
ALTER SESSION SET txn_priority = LOW;
UPDATE pt_accounts SET balance = balance - 100 WHERE account_id = 1;
-- ... keeps working, transaction open ...

-- The payment (another session, default priority HIGH)
UPDATE pt_accounts SET balance = balance + 100 WHERE account_id = 1;  -- waits

-- The DBA's settings
ALTER SYSTEM SET priority_txns_high_wait_target   = 5;
ALTER SYSTEM SET priority_txns_medium_wait_target = 10;
ALTER SYSTEM SET priority_txns_mode = 'ROLLBACK';

Both wait targets and the mode were accepted on Autonomous Database as ADMIN, and took effect immediately without a restart.

T0: the classic incident

With default settings the payment simply waited until the batch committed: 12.92 seconds. In production that is the call to the DBA team. While it waited, V$SESSION showed a wait event that now includes the waiter's priority:

EVENT                              SECONDS_IN_WAIT
---------------------------------- ---------------
enq: TX - row lock (HIGH priority)               2

That small detail is useful on its own: from the event name alone you can tell that the stuck session is high-priority work.

T1 and T2: the database makes the call

With a 5-second target for HIGH transactions, the LOW batch was rolled back and the payment got the row after 4.68 seconds. The batch session saw this on its next statement:

ORA-63300: Transaction is automatically rolled back since it is blocking
a higher priority transaction from another session.

A MEDIUM batch was treated the same way, because it is still lower than HIGH: the payment waited 3.95 seconds. In both cases the wait was a little under 5 seconds, so treat the target as approximate rather than a stopwatch.

Two things matter for a bank:

• The batch session stayed connected. Only its transaction was rolled back. My script caught the error, issued ROLLBACK and carried on with the next test in the same session. A real batch job needs exactly this kind of handler: catch ORA-63300, roll back, and retry or skip.
• No money was lost or duplicated. Across all five tests, three batch debits committed, two were rolled back, and five payment credits committed. The final balance was exactly 100,000 + 200 = 100,200.

T3: equal priority is left alone

When the batch was also HIGH, the database did not intervene. The payment waited 9.93 seconds until the batch committed. Priority Transactions only rolls back lower-priority blockers, so important work is never sacrificed for other important work.

T4: TRACK mode, the safe first step

With priority_txns_mode = 'TRACK', the LOW batch was not rolled back and the payment waited the full 12.92 seconds, but the database counted the event:

NAME                                             BEFORE  AFTER
------------------------------------------------ ------  -----
txns rollback priority_txns_high_wait_target          0      2
txns track mode priority_txns_high_wait_target        0      1

The two rollbacks are T1 and T2; the tracked event is T4. This is how I would introduce the feature in production: run in TRACK mode for a few business days, including a close-of-business cycle, and check how often batch work would have been rolled back before switching to ROLLBACK.

Findings specific to Autonomous Database

• Two parameter names. V$PARAMETER lists both priority_txns_* and txn_auto_rollback_* parameters. As ADMIN, priority_txns_mode and the priority_txns_*_wait_target parameters could be changed, while ALTER SYSTEM SET txn_auto_rollback_mode returned ORA-01031. Use the priority_txns_* names on Autonomous.
• Scheduler jobs could not set their priority. ALTER SESSION SET txn_priority worked in my SQL worksheet session, but inside a DBMS_SCHEDULER job it returned ORA-02097 for every value. Job sessions kept the default, HIGH. If your batch runs as a scheduler job on Autonomous, test this before relying on it; I ran the batch from a normal session instead.

Things to know before using it

• Set priorities deliberately. Everything is HIGH by default, so nothing is ever rolled back until you mark batch or reporting sessions as LOW or MEDIUM, for example in a logon trigger or the job's first statement.
• Batch code must handle ORA-63300. The rolled-back session must issue ROLLBACK before it can continue. Batch jobs should commit in small units, so a rollback loses seconds of work, not hours.
• Start in TRACK mode. Measure first, then enforce.
• It is not a replacement for good design. Long-running transactions that hold rows customers need are still the root cause. This feature limits the damage when they happen.

Clean-up

ALTER SYSTEM SET priority_txns_mode = 'ROLLBACK';
ALTER SYSTEM SET priority_txns_high_wait_target   = 2147483647;
ALTER SYSTEM SET priority_txns_medium_wait_target = 2147483647;
DROP TABLE pt_accounts PURGE;
DROP TABLE pt_events PURGE;

Summary

Priority Transactions in Oracle AI Database 26ai turned a classic blocking incident into an automatic decision. With a 5-second target, a high-priority payment waited 4.7 seconds instead of 12.9, the LOW or MEDIUM batch was rolled back with ORA-63300 while its session stayed connected, and the account balance remained exact. Equal-priority work was left alone, and TRACK mode counted what would have happened without touching anything. On Autonomous, use the priority_txns_* parameters, and check how your batch sessions set their priority before you depend on it.

Lab design, testing and all results are my own, run on an Oracle LiveLabs sandbox.

Tamilselvan Perumal is an Oracle ACE Apprentice and Senior DBA with 12+ years in banking databases, working on database performance and benchmarking. Connect on LinkedIn.