In earlier releases, if a transaction does not commit or roll back for a long time while holding row locks, it can potentially block other high-priority transactions. A transaction acquires a row lock for each row modified by one of the following statements: INSERT, UPDATE, DELETE, MERGE, and SELECT ... FOR UPDATE. The row lock exists until the transaction commits or rolls back. Transactions can hold row locks for a long duration in certain cases.
For example, an application modifies some rows but does not commit or terminate the transaction because of an exception in the application. Traditionally, when a transaction is blocked on a rowlock by another transaction for a long time, it required the database administrator to manually terminate the blocking transaction by killing the session through ALTER SYSTEM KILL SESSION command.
Applications do not prefer to kill the session because losing a database connection causes a glitch in their database workload. Re-establishing the database connection takes time. Connection pools make this less painful, but a disruption still occurs.
Starting with Oracle Database 23ai, the database provides parameters to control when and which transactions holding row locks can be automatically rolled back. Oracle Database rolls back the transaction but the session stays alive. The application must acknowledge the automatic rollback of the transaction by issuing a ROLLBACK SQL statement.
Applications can specify the priority of their transactions. If a low priority transaction blocks a high priority transaction on row locks, Oracle Database will automatically roll back the low priority transaction to let the high priority transaction(s) progress.
Setting Session-Level Transaction Priorities
Transaction priority is set at session level using ALTER SESSION command on the TXN_PRIORITY parameter. Once the transaction priority is set, it will remain the same for all the transactions created in that session.
For example, to set the priority of all transactions in the current session to HIGH, use the following command:
ALTER SESSION SET ‘txn_priority’ = ‘HIGH’;
The valid values for txn_priority are LOW, MEDIUM, and HIGH(the default for all new sessions). This parameter should be set by the application based on understanding the criticality of the transaction.
- If a
HIGHpriority transaction is blocked for a row lock, Oracle Database can roll back the transaction that is holding the row lock only if the holder isLOWorMEDIUMpriority. Oracle Database never rolls back aHIGHpriority transaction. - If a
MEDIUMpriority transaction is blocked for a row lock, Oracle Database can roll back the transaction that is holding the row lock only if the holder isLOWpriority. - If a
LOWpriority transaction is blocked for a row lock, Oracle Database will not attempt to roll back the transaction holding the row lock irrespective of its priority.
The database administrator can configure the time after which the low priority transaction is rolled back.
Setting System-Level Transaction Wait Times
Oracle Database provides system parameters to control after what time a transaction holding row locks can be automatically rolled back, including:
PRIORITY_TXNS_HIGH_WAIT_TARGET
PRIORITY_TXNS_MEDIUM_WAIT_TARGET
These system parameters set the maximum time duration, in seconds, a transaction with priority HIGH and MEDIUM will wait before the database rolls back a lower priority transaction holding a row lock. The blocker transaction is rolled back but its corresponding session is not killed and stays alive.
The application must acknowledge this automatic rollback by catching ORA-63300 and issuing a ROLLBACK SQL statement. If ROLLBACK is not issued, then all the SQL statements in the session will keep receiving ORA-63302. There is no LOW priority wait target parameter provided since Oracle Database does not roll back a blocker transaction if waiter’s priority is LOW.
To set the parameter using the ALTER SYSTEM command, specify the parameters with the wait time values.
For example:
ALTER SYETEM SET priority_txns_high_wait_target = 3;
In this example, if a HIGH priority transaction is blocked for at least 3 seconds on a row lock held by a MEDIUM or LOW priority transaction, the database will automatically attempt to roll back the blocking lower priority transaction.
When a higher priority transaction is blocked by a lower priority transaction, the system waits for at least the specified time before rolling back the blocking transaction. The wait time may be longer than the target time specified when there are multiple blocked transactions trying to get the same row lock.
The Priority Transaction feature is only enabled when both the transaction priority and the wait target parameters are set. Setting the transaction priority with no wait target time does not enable the feature.
Priority Transactions Can Operate in Two Modes – ROLLBACK and TRACK
The default mode for the Priority Transactions feature is ROLLBACK.
In this mode, if the following are configured:
PRIORITY_TXNS_HIGH_WAIT_TARGET
PRIORITY_TXNS_MEDIUM_WAIT_TARGET
, transactions that are holding row locks and blocking higher priority transactions would be automatically rolled back and allow higher priority waiting transactions to progress.
To set Priority Transactions mode to ROLLBACK, use the following command:
ALTER SYSTEM SET ‘priority_txns_mode’ = ‘ROLLBACK’;
TRACK mode is for database administrators to try out the Priority Transactions feature. In TRACK mode, the database will increment statistics in V$SYSSTAT reflecting how many transactions this feature would have rolled back, instead of actually rolling back any transactions. Applications can use this mode to tune the right wait target value before switching to ROLLBACK mode.
To set Priority Transactions mode to TRACK, use the following command:
ALTER SYSTEM SET ‘priority_txns_mode’ = ‘TRACK’;
TRACK mode is intended to aid in setting the appropriate values for:
PRIORITY_TXNS_HIGH_WAIT_TARGET
PRIORITY_TXNS_MEDIUM_WAIT_TARGET
After setting priority_txns_mode to TRACK, run your regular workload for a few hours (or whatever is appropriate) and monitor the time transactions of a certain priority typically wait for the row lock (monitor the row lock contention wait event time).
In the example below, session (sid 204) has a HIGH priority transaction holding the row lock. You can see other transactions wanting the same row lock waiting on different wait events based on their priority.
SELECT TO_CHAR(xidusn) || ‘. ‘ ||TO_CHAR(xidslot) || ‘. ‘ || TO_CHAR(xidsqn) AS transaction_id, sid, event, seconds_in_wait, blocking_session FROM v$session, v$transaction WHERE event LIKE ‘%enq%’ AND v$session.saddr = v$transaction.ses_addr;
TRANSACTION_ID SID EVENT SECONDS_IN_WAIT BLOCKING_SESSION
———————— ——- ————————————————– ————————– ————————–
2.17.1619 187 enq: TX – row lock (HIGH priority) 361 204
5.32.1557 51 enq: TX – row lock (LOW priority) 359 204
When there is a contention for the row lock, the transactions waiting for the row lock wait on a common wait event enq: TX - row lock contention. With Priority Transactions enabled by setting both the txn_priority parameter for transactions and wait_target parameter for the system, waiting transactions would wait on the wait events based on the priority of the waiting transaction.
After determining the appropriate values for these parameters, you can turn off the TRACK mode and switch to the ROLLBACK mode, configure the system-level wait target parameters with these values, and start using the Priority Transactions feature.
Summary
Automating the process of rolling back a low-priority transaction, blocking a high-priority one on row locks, reduces the administrative burden for DBAs while also helping to maintain transaction latencies/SLA on higher priority transactions
For more information, usage details and example, for Priority Transactions, please see the Oracle documentation [here].