This has been tested in Oracle Database 23ai Free Release 23.0.0.0.0 using sample hr schema.
Problem statement:Suppose management decided to increase salary of job clerk whose joining date is less then 01-JAN-2019.Now one of the HR was trying to update through an application but mistakenly chosen wrong year as 2020 but she did not commit by pressing confirmation button. Now the rows are locked as commit is not performed.Same has been tried from a new session but that hangs forever as previous session was not commited. At this stage DBA can be involved and previous session can be killed so that new session can commit.
This problem can be easily solved in 23ai if we set parameter txn_priority and priority_txns_medium_wait_target in session and pdb level
connect to hr schema and pdb.Issue below command which says medium transaction will wait for 180 seconds in case any low priority transaction blocks medium transaction.
alter system set priority_txns_medium_wait_target=180;
From session 1 set transaction priority as below
alter session set txn_priority='LOW';
--Issue below command to increase salary of those employees whose hire date is less then 1st Jan 2020 and belongs to JOB clerk
update employees set salary=salary*1.2 where job_id='ST_CLERK' and hire_date<to_date('01-JAN-2020','DD-MON-YYYY');
--Do not commit here
Lets say someone from another session realized hire date should be less than 01-JAN-2019.So he execute below statement with medium priority
--Session 2 starts here
alter session set txn_priority='MEDIUM';
update employees set salary=salary*1.2 where job_id='ST_CLERK' and hire_date<to_date('01-JAN-2019','DD-MON-YYYY');
--Now session 2 will wait until it reaches180 seconds
After wait of 180 seconds low transaction will be rolled back automatically
During this time open 3rd session and execute below query
select sid,event,seconds_in_wait,blocking_session from v$session where event like '%enq%';
SID EVENT SECONDS_IN_WAIT BLOCKING_SESSION
---------- ---------------------------------------------------------------- --------------- ----------------
49 enq: TX - row lock (MEDIUM priority) 78 35
Once 180 second is past go to session 1 and try to commit.You will see following error
SQL> commit;
commit
*
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/
This proves that since medium transaction has high priority it has rolled back after wait of 300 seconds.
Now you can issue commit in 2nd session to make that change permanently.
I hope you have learned something useful which can be used in your application logic
