Priority Transactions in Oracle 23ai

Oracle 23ai introduces Priority Transactions — a mechanism to assign priorities to DML transactions so that higher-priority work can preempt lower-priority work when lock contention occurs.

In a busy OLTP system, a long-running batch job holding a lock can block critical customer-facing transactions. Until now, the only options were to kill the batch session manually or accept the latency. Priority Transactions automate this.

Setting a transaction priority:

-- High priority (e.g., customer checkout)
EXEC DBMS_TRANSACTION.SET_PRIORITY('HIGH');

-- Low priority (e.g., background batch)
EXEC DBMS_TRANSACTION.SET_PRIORITY('LOW');

How preemption works:

When a HIGH priority transaction attempts to acquire a lock held by a LOW priority transaction, Oracle evaluates the conflict. If the high-priority transaction has waited longer than a configurable threshold, Oracle can:

1. Abort the low-priority transaction (rollback and release locks)
2. Notify the low-priority session so it can self-abort cleanly
3. Defer the decision based on age and priority differential

Configuration:

ALTER SYSTEM SET TRANSACTION_PRIORITY_POLICY = 'PREEMPTIVE';
ALTER SYSTEM SET PRIORITY_TRANSACTION_WAIT_TIMEOUT = 5; -- seconds

Monitoring:

SELECT sid, serial#, priority, wait_class, seconds_in_wait
FROM   v$session
WHERE  wait_class = 'Concurrency'
ORDER BY priority DESC, seconds_in_wait DESC;

Real-world impact: In e-commerce and financial systems where a payment transaction must complete quickly, Priority Transactions ensure that a poorly-timed batch report doesn’t cause a customer-facing timeout. This is the kind of feature that previously required complex application-layer retry logic.