Month: August 2025

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.

One of Oracle 23ai’s most interesting concurrency features is Lock-Free Reservations. It solves a classic high-concurrency problem: multiple sessions competing to update the same row — like decrementing inventory counts in a flash sale.

The classic problem:

-- Session 1 and Session 2 both try to decrement inventory
UPDATE products SET stock_qty = stock_qty - 1 WHERE product_id = 42;

With thousands of concurrent orders, Sessions 2 through N all block waiting for Session 1’s lock. This creates a hot row contention bottleneck.

Lock-Free Reservation solution:

-- Define the column as a reservation target
ALTER TABLE products MODIFY (
    stock_qty NUMBER RESERVABLE CONSTRAINT stock_non_negative CHECK (stock_qty >= 0)
);

Once a column is declared RESERVABLE, Oracle handles concurrent updates to it using a reservation mechanism rather than row-level locking. Each session “reserves” a portion of the value without blocking others.

How it works under the hood:

Oracle maintains a pending reservation table alongside the main table. Each concurrent update is stored as a reservation. The actual column value is the base value plus the sum of all pending reservations. When a session commits, its reservation is rolled into the base value.

The net effect:

• Multiple sessions can concrement stock_qty simultaneously without blocking each other
• The CHECK constraint (stock_qty >= 0) is still enforced — the sum of all reservations cannot drive the value below zero
• ACID guarantees are maintained

Ideal use cases: Inventory management, ticket booking, seat reservations, resource quotas — any scenario where many sessions update the same numeric column concurrently.

This is a sophisticated concurrency improvement that eliminates a well-known Oracle scalability bottleneck without requiring application-level workarounds.