Understanding Transaction Priority in Oracle Database 23c
Oracle Database 23c introduces a fascinating feature known as Priority Transactions. This functionality allows for control over when and which transactions holding row locks can be automatically rolled back, ensuring high-priority transactions are not unduly delayed by lower-priority ones.
The Evolution of Transaction Handling
In earlier releases, if a transaction did not commit or roll back while holding row locks, it could potentially block other high-priority transactions. Such situations required manual intervention by a database administrator, often involving the use of the ALTER SYSTEM KILL SESSION command to terminate the blocking session.
A transaction acquires a row lock for each modified row through statements like INSERT, UPDATE, DELETE, MERGE, and SELECT ... FOR UPDATE. These row locks persist until the transaction is either committed or rolled back.
The Need for Priority Transactions
Consider a scenario where an application modifies some rows but fails to commit or terminate the transaction due to an exception. Traditionally, this would block other transactions, potentially causing significant delays.
Oracle Database 23c addresses this challenge by introducing transaction priorities: LOW, MEDIUM, and HIGH (default). By implementing priority transactions, the database can automatically roll back low-priority transactions that block higher-priority ones, allowing critical operations to proceed without unnecessary delays.
Implementing Priority Transactions
To enable this feature, two parameters must be set:
- Wait time: The duration in seconds a higher-priority transaction waits before the blocking transaction is rolled back.
- Transaction priority: Specifies the priority level for the transaction.
The priority transactions feature is activated only when both parameters are set. When a higher-priority transaction is blocked by a lower-priority one, the system waits at least 10 seconds before rolling back the blocking transaction. The actual wait time may vary if multiple transactions are competing for the same row lock.
Handling Transaction Priorities
Applications should be structured to catch errors ORA-63300 and ORA-63302 and then issue a ROLLBACK statement. Here’s how transaction priorities are managed:
- HIGH Priority: If blocked, Oracle rolls back the blocking transaction only if its priority is LOW or MEDIUM. High-priority transactions are never rolled back by the system.
- MEDIUM Priority: If blocked, Oracle rolls back the blocking transaction only if it is of LOW priority.
- LOW Priority: If blocked, Oracle does not attempt to roll back the transaction holding the row lock, regardless of its priority.
Setting Session-Level Transaction Priorities
Transaction priority is set at the session level using the ALTER SESSION command on the TXN_PRIORITY parameter. This priority remains consistent for all transactions within that session. For example, to set all transactions in the current session to HIGH priority, use the following command:
ALTER SESSION SET 'txn_priority' = 'HIGH';
The valid values for TXN_PRIORITY are LOW, MEDIUM, and HIGH (default for all new sessions). This parameter should be set based on the criticality of the transaction.
Summary
The Priority Transactions feature in Oracle Database 23c significantly enhances the ability to manage transaction delays caused by row locks. By prioritizing transactions, Oracle Database ensures that critical operations proceed smoothly while maintaining system efficiency. This innovative approach not only simplifies transaction management but also underscores Oracle’s commitment to improving database performance and reliability.
