Oracle 23ai: Priority Transactions, Automatic rollback

Introduction to Oracle DB Transaction Locks

In Oracle databases, a common issue faced by DBAs involves the enq: TX – row lock contention wait event. This occurs when one session modifies data in a table and does not commit, causing subsequent DML (Data Manipulation Language) transactions on the same rows by other sessions to be suspended. These sessions remain suspended until the session holding the lock commits or rolls back, thus releasing the locked rows.

Automation to Resolve to Lock Contention

Newer Oracle versions (19c and 20c) introduce parameters like max_idle_blocker_time automatically terminating sessions that block others, reducing manual intervention requirements.

Oracle 23c's Transaction Priority Feature

A significant new feature in Oracle 23ai is “Transaction Priority,” allowing the designation of transaction priority levels (LOW, MEDIUM, HIGH), with HIGH being the default. This functionality automatically processes transactions (either commit or rollback, with rollback as default) that block higher-priority transactions after a pre-set time. This automation considerably lowers the administrative overhead associated with managing locked transactions.

Database Parameters and Dictionary Views

The introduction of transaction priority has brought new parameters and dictionary view columns:

• TXN_PRIORITY Assigns transaction priority in a session.
• txn_auto_rollback_medium_priority_wait_target And similar parameters define auto-abort times for transactions based on priority.
• Views like V_$TRANSACTION and GV_$TRANSACTION now include columns such as TXN_PRIORITY and PRIORITY_TXNS_WAIT_TARGET.

Enhanced Wait Events

Oracle 23ai enhances wait events by associating them with transaction priorities. For instance, wait events for row locks now distinguish between different transaction priorities (e.g., enq: TX - row lock (HIGH priority)), providing more precise insights into the wait reasons and involved transaction priorities.

Errors

Attempting to set the wait time for medium-priority transactions or modifying the transaction priority via session settings can trigger errors if the database edition does not support these features. The error messages guide towards the necessary actions, such as the need for rollback or proper transaction end commands for XA transactions.

Note: This feature cannot be experienced in the dev-free version and requires the enterprise version.

Summary

The transaction priority feature simplifies database management by automatically terminating lower-priority transactions that block higher-priority ones. This automatic handling is crucial for maintaining database performance and avoiding manual intervention. The feature also introduces considerations for application design, as the application must adequately handle errors related to automatic rollbacks. The effectiveness and behaviour of this feature still need further real-world testing and verification.

#Oracle #Database #23ai #Oracle23ai #Transactions #OracleACE