Introduction
Another great feature of the Oracle 23c database. One of my favorites so far.(Probably in the Top 5). Its simple, effective and for those critical jobs your running a great tool for Devs and DBAs to make use of in a production support environment.
So what is Automatic Transaction Rollback?
Whenever a transaction undertakes the modification of data by executing commands like INSERT, UPDATE, DELETE, MERGE, or SELECT … FOR UPDATE a protective lock is taken out, by default a row lock, which continues until the transaction is either committed or aborted. In many systems, it is not uncommon for transactions to maintain these locks for considerable lengths of time, especially in situations where an application alters rows but is unable to conclude or cancel the transaction due to errors.
In the past, if a transaction was stalled because it was blocked by another transaction holding a row lock, the resolution required a database administrator to manually end the transaction using the ALTER SYSTEM KILL SESSION command.
This manual intervention by a DBA, is no longer required if your system makes use of Automatic Transaction Rollback. Instead, you can configure transaction to allow the Oracle database system to automatically rollback transactions based on a priority configuration setting.
How Does it Work?
There are two key components that come into play when using Automatic Transaction Rollback.
- Configuring the Session Level Priority of a Transaction. (This controls what transactions take priority)
- Configuring System Level Wait Targets for Transactions. (This controls how long a transaction
Lets take a look at each of these in turn
Configuring the Session Level Priority of a Transaction
When initiaing a transaction, you can now set a session level parameter to configure the priority of the transaction. Low, Medium or HIGH. For example,
ALTER SESSION SET "txn_priority" = "HIGH";
By default, all transaction get a priority of HIGH. A high priority transaction will never be rolled back and would need a DBA to manually intervene. It is also worth noting that once the transaction has been declared/started, you cannot change its priority. Instead, the next transaction created in the session will use the updated priority.
Here is how Oracle manages transactions based on the priority set.
| Priority Setting | High | Medium | Low |
|---|---|---|---|
| Blocked by High | No Rollback | No Rollback | No Rollback |
| Blocked by Medium | Automatic Rollback | No Rollback | No Rollback |
| Blocked by Low | Automatic Rollback | Automatic Rollback | No Rollback |
- If a HIGH priority transaction is blocked for a row lock, Oracle database can roll back the transaction that is holding the row lock only if the holder is LOW or MEDIUM priority.
- If a MEDIUM priority transaction is blocked for a row lock, Oracle database can roll back the transaction that is holding the row lock only if the holder is LOW priority.
- If a LOW priority 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.
Once we understand the priorities of our transactions in an application, we can set the priority accordingly.
Configuring System Level Wait Targets for Transactions
We have seen our the priority of each transaction affects the priority of another session, but just because a low priority transaction is blocking a high prioirty transactions, it does not mean the low transaction is killed immediately by Oracle. If this were the case, its likely only high level trabnsaction would complete. After all, it is very common for transactions to be blocked for mirco seconds. It would be very unlikely we would want a low priority session to be killed just because it cause a micro second wait on a high prioirty transaction. In most applications, its even unlikely we would want that low transaction to be killed if it caused a 1 second delay.
This is where two new system level parameters come into play
- TXN_AUTO_ROLLBACK_HIGH_PRIORITY_WAIT_TARGET
- TXN_AUTO_ROLLBACK_MEDIUM_PRIORITY_WAIT_TARGET
The parameters are self explanatory. Each parameter defines how long a high transaction or medium transaction should wait before killing a session of a lower priority. Note there is no parameter for low prioirty transactions as these will always wait.
"But wait!", I hear you cry.
"There is the word target on the end of that parameter. We know what that means!"
And your right, its a target parameter, which in Oracle terms, means Oracle will try to meet that value set, but cannot guarantee it. So how does the "target" in this concept work. Lets explore an example offered in the manual
Example
When a transaction (in this example a high priority transaction) is blocked by another transaction (a lower priority), the system waits for at least the specified time set via TXN_AUTO_ROLLBACK_HIGH_PRIORITY_WAIT_TARGET, after which it will rollback the blocking transaction. However, the wait time may be longer than the target time specified, hence why the word target is used. So why or how is it possible and under what circumstances may the transaction wait longer than the target.
Lets assume TXN_AUTO_ROLLBACK_HIGH_PRIORITY_WAIT_TARGET is set to 20 seconds. The following takes place:
- At time t1, transaction 1, a low priority transaction, locks a specific row.
- Ten seconds later at time t2, transaction 2, a low priority transaction, attempts to lock the same row and waits.
- Five seconds later at time t3, transaction 3, a high priority transaction, attempts to update the same row.
This is how the above plays out, assuming no transaction performs a commit or is manually killed.
- The high priority transaction waits at least 20 seconds (from time 3) after which the first transaction is rolled back.
- After this, transaction 2 gets the row lock, since it has requested the row lock before transaction 3.
- So now transaction 3 would have to wait another 20 secs from the time transaction 2 gets the row lock
- After which transaction 2 is rolled back.
This is why the system parameters above are "target" parameters, as the values are just that, targets.
Acknowledgement: A special shoutout to Kishy Kumar, an expert in databases and the key figure behind the Priority Transactions project at Oracle Transactions, for helping me refine this post.
Published 14th February 2024
