Hello, I think I have discovered a bug in one of the new features of Oracle 23ai, specifically the Priority Transactions feature.
To summarize the issue: When the Priority Transactions feature is activated for the first time, an ORA-63302 error message occurs. At this point, you must explicitly enter the `ROLLBACK` command in order to execute any further commands. However, when the Priority Transactions feature is activated for the second time in the same session, the ORA-63302 error does not occur. In this case, the rollback happens automatically, allowing all commands to function normally.
Could someone please verify this?
Test:
--Set the priority_txns_medium_wait_target to 15 seconds.
SQL> alter system set priority_txns_medium_wait_target = 15 scope = both;
--In session 1, set `txn_priority` to low.
SQL> alter session set txn_priority = low;
--In session 2, set `txn_priority` to medium.
SQL> alter session set txn_priority = medium;
--In session 1, create the table t1 and insert data into it.
SQL>
create table t1 (col1 number);
insert into t1 values (1),(2),(3),(4),(5);
commit;
--In session 1, update the col1 column of the t1 table to set all values to 1111.
SQL> update t1 set col1 = 1111;
5 rows updated.
--In session 2, update the col1 column of the t1 table to set all values to 2222.
--Session 2 will be put on hold, waiting due to the ongoing transaction in session 1.
SQL> update t1 set col1 = 2222;
(waiting 15 seconds...)
--after 15 seconds in session2
--After 15 seconds, the hold in session 2 will be released, and the update will proceed.
SQL> update t1 set col1 = 2222;
5 rows updated.
--In session 1, after 15 seconds, no commands can be executed until the ROLLBACK command is explicitly entered.
SQL> select * from t1;
select * from t1
*
ERROR at line 1:
ORA-63302: Transaction must roll back
ORA-63300: Transaction is automatically rolled back since it is blocking a higher priority transaction from another session.
Help: https://docs.oracle.com/error-help/db/ora-63302/
--Once the ROLLBACK command is entered in session 1, it can execute normal operation.
SQL> rollback;
Rollback complete.
SQL> select * from t1;
COL1
----------
1
2
3
4
5
--For the next test, enter the ROLLBACK command in session 2.
SQL> rollback;
-------This concludes the basic example of Priority Transactions.----------
--However, if the same test is conducted again in this state, unusual results occur.
--In session 1, update the col1 column of the t1 table to set all values to 1111.
SQL> update t1 set col1 = 1111;
5 rows updated.
--In session 2, update the col1 column of the t1 table to set all values to 2222.
--Session 2 will be put on hold, waiting due to the ongoing transaction in session 1.
SQL> update t1 set col1 = 2222;
(waiting 15 seconds...)
--after 15 seconds in session2
--After 15 seconds, the hold in session 2 will be released, and the update will proceed.
SQL> update t1 set col1 = 2222;
5 rows updated.
***Important
--In the normal case, after 15 seconds in session 1, no commands can be executed until the ROLLBACK command is explicitly entered.
--However, during the second test, after 15 seconds, no error occurs even without entering the ROLLBACK command.
--There is no need to manually enter the ROLLBACK command.
SQL> select * from t1;
COL1
----------
1
2
3
4
5
In my opinion, this appears to be a bug. Could someone from Oracle verify this?
And my database version is 23.5.0.24.07.
SQL> select * from v$version;
BANNER
---------------------------------------------------------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BANNER_LEGACY CON_ID
--------------------------------------------------------------------------------------------------------------------------------- ----------
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems 0