Learning is not a spectator sport

One of the cool features in Oracle AI Database 26ai is the ability for sessions to have different levels of priority for their transaction locks. I’ve covered how the feature works in the video below

In a nutshell, a session can nominate whether their locking priority is LOW, MEDIUM or HIGH. When a session tries to obtain a lock on a row in a table, then if that row is already locked by session with a lower priority, then after a timeout period, the higher priority session will automatically obtain the lock (forcing the lower priority session to abort their current transaction).

This is just a quick post to offer some advice on how you should use this feature. It would be an easy interpretation to assume that this feature lets people goes “open season” on the database, because you can always rely on higher priority sessions to always get the locks they need. This is not the intended use case, for a couple of reasons

• Even if you achieve a lock as a higher priority session, you still will be waiting for a number of seconds, which will slow your application down
• Locks are not the same as deadlocks, so simply assigning priorities will not solve poorly designed database APIs

The aim here is for the feature to be used to help prioritise resources that your applications require whilst running on the database. Ideally they should not be competing for resources, but in the rare instances that they do, you can use transaction priorities to arbitrate. The feature is not designed to control ad-hoc users locking rows, because your default position should always be that ad-hoc usage on the database should not be able to take locks at all! In particular, it is important to move away from giving the SELECT privilege to users and give them READ privileges instead. Giving SELECT privileges also gives SELECT FOR UPDATE which is not something ad-hoc usage should ever need.

To demonstrate why transaction priority is not appropriate for ad-hoc users, be aware that anyone can alter their transaction priority. For example, here’s a user DEMO with only the CREATE SESSION privilege.

SQL> create user demo identified by Jas9d0asdh123;

User created.

SQL> grant create session to demo;

Grant succeeded.

However, like many other ALTER SESSION settings, the DEMO user can always change their session level transaction priority even without the ALTER SESSION privilege.

SQL> conn demo/Jas9d0asdh123@sydney26ai_low
Connected.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION

SQL> select * from session_roles;

no rows selected

SQL>
SQL> alter session set txn_priority = low;

Session altered.

SQL> alter session set txn_priority = high;

Session altered.

Similarly ALTER SESSION is not DDL, so trying to stop users from running it via a DDL trigger will not work.

create or replace 
trigger admin.this_does_NOT_catch_alter_session
before ddl on demo.schema
begin
    if ora_sysevent = 'ALTER'  then
       raise_application_error(-20001, 'No no no');
    end if;
end;
/

So use the txn_priority setting in your applications to decide on which applications should take priority when it comes to locking resources. For your end users, avoid the issue entirely by only granting the READ privilege.