Priority Transactions is another very interesting topic in Oracle Database 23. It provides the functionality 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.
This posting explains and demonstrates the newest update on Priority Transactions (old name: Automatic Transaction Rollback) feature. Many thanks to Kishy Kumar (Director of Development at Oracle Transactions) for his lead contribution to this feature and his very valuable comments and additions to this blog.
How does it work?
Let’s assume an application modifies some rows and Oracle acquires a row lock for each row that is modified e.g. by any kind of DML operations. It can block another transaction on a row lock as long as it does not release the locked rows. To release the locked rows, a COMMIT or ROLLBACK must be issued in the session holding the lock. Sometimes it needs a database administrator to manually terminate the blocking transaction by killing the session with ALTER SYSTEM KILL or cancel the SQL statement with ALTER SYSTEM CANCEL SQL.
To implement priority transactions in 23ai a decision needs to be taken in which case a transaction will be rolled back. Therefore different priorities for transactions are introduced. That means it is possible now to define the priority of your transaction. You can decide between LOW, MEDIUM, and HIGH (default). The Priority Transactions feature will then automatically roll back low-priority transactions that are blocking higher-priority transactions from obtaining row locks after a pre-defined wait time. Please consider: The default priority for a transaction is always HIGH.
To implement this feature you need to set two parameters – one for the wait time in seconds for medium or low priority transactions and one for the transaction priority itself. In the following section, you will find a short definition of the two parameters. More information about these parameters can be found in Database Reference.
- There are two parameters to configure the wait time: PRIORITY_TXNS_HIGH_WAIT_TARGET and PRIORITY_TXNS_MEDIUM_WAIT_TARGET. They control the maximum wait duration of time in seconds. A transaction with a priority high and medium will wait for this defined wait time before the database rolls back a lower-priority transaction holding a row lock. There is no low-priority wait target parameter provided since the Oracle database doesn’t roll back a blocker transaction if the waiter’s priority is LOW. The default value is the maximum number value.
Note: You need the ALTER SYSTEM privilege to configure these parameters. It’s modifiable in a PDB. - To set the priority for your transaction, use the parameter TXN_PRIORITY. It can have the values LOW, MEDIUM, or HIGH. It can be defined with an ALTER SESSION command.
Note: All transactions are high-priority transactions by default.
The priority transactions feature is only enabled when both the transaction priority and the wait target parameters are set.
Let’s illustrate the functionality in a simple scenario with three transactions – two low-priority and one high-priority transaction. All three transactions want to change a value in the table MYCHECK with one column T with the actual value 1.
Query the table MYCHECK.
SQL> select * from scott.mycheck;
T
----------
1
First, we need to set the wait time for high-priority transactions to a value (here 10 seconds) with ALTER SYSTEM. In our case, we enable it for the pluggable database US.
SQL> alter system set priority_txns_high_wait_target=10 scope=both; System altered. SQL> alter pluggable database us close; Pluggable database altered. SQL> alter pluggable database us open; Pluggable database altered.
Now let’s check the settings. We changed the value for PRIORITY_TXNS_HIGH_WAIT_TARGET.
Note: As a developer with the new role DB_DEVELOPER_ROLE you can now also check V$PARAMETER.
col name format a50 col value format a10 select name, value from v$parameter where name like 'priority%';
The result looks like:
NAME VALUE -------------------------------------------------- ---------- priority_txns_high_wait_target 10 priority_txns_medium_wait_target 2147483647 priority_txns_mode ROLLBACK
The following table demonstrates the scenario with three transactions, two with lowpriority and one with the default high priority.
| Time | Transaction1 Low | Transaction2 Low | Transaction3 High (Default) |
|---|---|---|---|
| t1 | alter session |
|
|
| t2 | alter session |
||
| t3 | select SYS_CONTEXT('USERENV','SID'); |
||
| t4=t3+10 | 1 row updated. |
||
| t5=t4+10 | SQL> select * from scott.mycheck; |
SQL> select * from scott.mycheck; |
1 row updated.SQL> select * |
What happened?
When a higher priority transaction is blocked by a lower priority transaction, the system waits for at least 10 seconds 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.
In our example, the high priority transaction 3 (1093) waits at least 10 seconds (from time t3) after which transaction 1 (630) is rolled back. After this, transaction 2 gets the row lock, since it has requested the row lock before transaction 3. So transaction 3 would wait for another 10 seconds from the time transaction 2 got the row lock, after which transaction 2 is rolled back. Therefore, the wait target parameter values do not imply the maximum time a high priority waiter will wait before it gets the row locks.
What happens with the low-priority transactions?
The blocker transaction is rolled back but its corresponding session is not killed and stays alive. The automatic rollback of the transaction must be acknowledged before its session can continue executing further SQLs. The acknowledgment can be provided by issuing a transaction rollback. When a transaction is automatically rolled back, the currently executing SQL in an active session or the next SQL statement in an idle session will get the error message ORA-63300. The subsequent SQL statements will throw ORA-63302 until a rollback is issued. Therefore, the application logic must be structured to catch both of the two errors ORA-63300 and ORA-63302, and then issue the rollback.
How can we monitor this?
V$TRANSACTIONS with the two new columns are available to aid in monitoring transactions. TXN_PRIORITY shows the transaction priority and PRIORITY_TXNS_WAIT_TARGET shows the wait target for the transaction specified in seconds.
In our example at t1:
SQL> select txn_priority, priority_txns_wait_target from v$transaction; TXN_PRI TXN_PRIORITY_WAIT_TARGET ------- ------------------------ LOW 0
V$SESSION with the columns EVENT, SECONDS_IN_WAIT and BLOCKING_SESSION is helpful to analyze the locking situation. Let’s filter on “enq: TX” to get information about sessions waiting for a row level lock that is already held by another session.
What happened now between time t1 to t5?
At t1, a user issues an UPDATE in transaction 1, a low-priority transaction, and locks a specific row.
SQL> select sid, event, seconds_in_wait, blocking_session from v$session where event like '%enq%'; no rows selected.
At t2, an UPDATE on the same table is issued in a low-priority transaction 2 (SID 940) from a different session. Now we can see the row lock and the blocking session (SID 630) in V$SESSION.
Transaction 2, a low priority transaction, attempts to lock the same row and waits.
SQL> col event format a35 SQL> select sid, event, seconds_in_wait, blocking_session from v$session where event like '%enq%'; SID EVENT SECONDS_IN_WAIT BLOCKING_SESSION ---------- ----------------------------------- --------------- ---------------- 940 enq: TX - row lock (LOW priority) 16 630
At t3, transaction 3, a high priority transaction, attempts to update the same row. Now we have two entries in V$SESSION and two sessions are waiting.
SQL> select sid, event, seconds_in_wait, blocking_session from v$session where event like '%enq%'; SID EVENT SECONDS_IN_WAIT BLOCKING_SESSION ---------- ----------------------------------- --------------- ---------------- 940 enq: TX - row lock (LOW priority) 38 630 1093 enq: TX - row lock (HIGH priority) 6 630
Between t3 and t4 (= t3+10 seconds), the seconds in wait values increase.
SQL> select sid, event, seconds_in_wait, blocking_session from v$session where event like '%enq%'; SID EVENT SECONDS_IN_WAIT BLOCKING_SESSION ---------- ----------------------------------- --------------- ---------------- 940 enq: TX - row lock (LOW priority) 39 630 1093 enq: TX - row lock (HIGH priority) 7 630
SQL> select sid, event, seconds_in_wait, blocking_session from v$session where event like '%enq%'; SID EVENT SECONDS_IN_WAIT BLOCKING_SESSION ---------- ----------------------------------- --------------- ---------------- 940 enq: TX - row lock (LOW priority) 40 630 1093 enq: TX - row lock (HIGH priority) 10 630
When we reach the maximum wait time at t4, the first row lock is relieved. Transaction 3 (1093) will wait for another 10 secs from the time transaction 2 (940) got the row lock, after which transaction 2 is rolled back.
SQL> select sid, event, seconds_in_wait, blocking_session from v$session where event like '%enq%'; SID EVENT SECONDS_IN_WAIT BLOCKING_SESSION ---------- ----------------------------------- --------------- ---------------- 1093 enq: TX - row lock (HIGH priority) 4 940
After t5 (t4+10) there is no row lock anymore.
SQL> select sid, event, seconds_in_wait, blocking_session from v$session where event like '%enq%'; no rows selected
SQL> select txn_priority, priority_txns_wait_target from v$transaction; TXN_PRI TXN_PRIORITY_WAIT_TARGET ------- ------------------------ HIGH 10
Summary
To use the Priority Transactions feature (aka Automatic Transaction Rollback), two types of parameters are required – one for the wait time and one for the session priority. The default transaction priority is HIGH and does not automatically roll back. When configured, any blocker transaction in a session with lower transaction priority gets aborted. Upcoming SQL statements in this session throw errors until ROLLBACK is sent. After the ROLLBACK is acknowledged, a subsequent transaction can be started in the same session.
You can monitor the behavior in V$SESSION or V$TRANSACTION. Alerts are also shown in the alert log whenever a transaction terminates.
Optionally, you may change the parameter TXN_AUTO_ROLLBACK_MODE. It has two values: ROLLBACK (this is the default) or TRACK if you only want to try out the feature. In the last case, the database will only increment statistics in V$SYSSTAT. For more information, please also review Database Administrator’s Guide.
Further Readings
- Database Administrator’s Guide: Managing Transactions
- Database Reference: TXN_PRIORITY
- Database Reference: TXN_AUTO_ROLLBACK_MODE
- Database Reference: V$TRANSACTION
- Short video presentation Priority Transaction in 23ai
- GitHub Priority Transactions