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:

• “PRIORITY_TXNS_HIGH_WAIT_TARGET”

• “PRIORITY_TXNS_MEDIUM_WAIT_TARGET”

• “PRIORITY_TXNS_MODE”

• “V$TRANSACTION”

• Oracle AI Database Administrator’s Guide for more information about Priority Transactions