2.420 TXN_PRIORITY

TXN_PRIORITY specifies the priority for all user transactions in a session.

Property Description

Parameter type

String

Syntax

TXN_PRIORITY = { HIGH | MEDIUM | LOW }

Default value

HIGH

Modifiable

ALTER SESSION

Modifiable in a PDB

No

Basic

No

The Priority Transactions feature automatically rolls back low-priority transactions that are blocking higher priority transactions from obtaining row locks.

You can use the TXN_PRIORITY parameter to assign a priority (HIGH, MEDIUM, or LOW) to all transactions in a particular user session. Priority Transactions then operates as follows for the three priorities:

  • HIGH - Priority Transactions never rolls back a HIGH priority transaction in order to release a row lock for another transaction. This is the default.

  • MEDIUM - Priority Transactions will roll back a MEDIUM priority transaction if it is holding a row lock required by a HIGH priority transaction, and the HIGH priority transaction has been waiting for the row lock longer than the time specified by the PRIORITY_TXNS_HIGH_WAIT_TARGET parameter.

  • LOW - Priority Transactions will roll back a LOW priority transaction if it is holding a row lock required by a HIGH or MEDIUM priority transaction, and the HIGH or MEDIUM priority transaction has been waiting for the row lock longer than the time specified by the PRIORITY_TXNS_HIGH_WAIT_TARGET or PRIORITY_TXNS_MEDIUM_WAIT_TARGET parameter, respectively.

    If a LOW priority transaction is blocked waiting for a row lock, Priority Transactions will not roll back the transaction holding the row lock, regardless of its priority.

You can view the priority and maximum wait time for a transaction by querying the TXN_PRIORITY and PRIORITY_TXNS_WAIT_TARGET columns in the V$TRANSACTION view.

Note:

This parameter is available starting with Oracle AI Database 26ai.

See Also: