Learning is not a spectator sport

Anyone who read Animal Farm as a school student, will remember the famous line:

“All animals are equal, but some animals are more equal than others”

The concept of equality has been the same with database transactions since databases began. We all have equal rights in being able to lock a row for our exclusive use. But of course, in real world business scenarios, transaction equality is not like that. Me locking a row in the employee table that subsequently causes the entire company payroll cycle to fail is not an ideal situation. The locks that the payroll application wants to take are (most probably) more important than the casual locks I want to take.

One of the very cool features in 23ai is the ability to reflect those real life scenarios in the database. The full video with the demo is below, but there’s a TL;DR underneath if you want to skip to that.

Firstly we nominate that a high priority session only needs to wait for 10 seconds before it can “take over” a lock taken by a lower priority session

SQL> alter system set priority_txns_high_wait_target = 10;

System altered.

Session 1 is a low priority session, and take a row lock

SQL> alter session set txn_priority = low;

Session altered.

SQL> delete from t;

1 row deleted.

Session 2 is a high priority session and tries lock the same row

SQL> alter session set txn_priority = high;

Session altered.

SQL> delete from t;

[10 seconds elapse]

1 row deleted.

It seems amazing that somehow Session 2 managed to get the row lock, but flipping back to Session 1, we can see we forced it up to give up its lock

SQL> select * from t;
select * from t
*
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.

But the topic of this blog post is not this 23ai feature when it comes to DML. A customer reached out to me and asked if we could leverage this facility for DDL.

We’ll restart the demo, with Session 1 taking a low priority lock

Session 1 is a low priority session, and take a row lock

SQL> alter session set txn_priority = low;

Session altered.

SQL> delete from t;

1 row deleted.

Session 2 will allow itself to wait for a DDL timeout and also make itself a high priority session, and then we’ll try grab an exclusive lock on the table.

SQL> alter session set ddl_lock_timeout = 20;

Session altered.

SQL> alter session set txn_priority = high;

Session altered.

SQL> drop table t;

[20 seconds elapse]

drop table t
*
ERROR at line 1:
ORA-00054: Failed to acquire a lock (Type: "TM", Name: "DML", Description: "Synchronizes accesses to an object") because it is
currently held by another session. The resource being locked can be identified by 79230 ("Table") and 0 ("operation")

As you can see, unfortunately lock prioritisation is for DML not DDL.