DML WAIT[NOWAIT] in Oracle AI Database 26ai -

Introduction

DML WAIT[NOWAIT], introduced in 23.26.2.0.0, helps to specify how long a DML statement should wait to acquire a lock before rolling back the DML statement and returning session control to the user.

The general syntax is:

{ NOWAIT
  | WAIT { FOREVER | integer [ SECONDS | MILLISECONDS | MICROSECONDS ] }
}

You can specify whether to wait forever, not at all or for n seconds, milliseconds or microseconds. The default is seconds ; specifying WAIT 5 is equivalent to WAIT 5 SECONDS. This can help improve the overall user experience in applications. Users would not have to sit in front of their screens watching a spinning wheel indefinitely.

Example

For example, if we tried to delete the same employee whose department was being changed we can specify a 5 second wait to acquire that lock.

^{from first session:}

update emp
set    dept_id = 3
where  emp_id = 1 ;

1 row updated.

SQL>

^{From second session:}

delete from emp
where emp_id = 1
wait 5 ;

Error report -
ORA-00054: Failed to acquire a lock (Type: "TX", Name: "Transaction", Description: "Lock held by a transaction
to allow other transactions to wait for it") because it is currently held by another session. The resource being
locked can be identified by 655386 ("usn<<16 | slot") and 766 ("sequence")
ORA-40097: Failure to acquire row lock, currently held by transaction ID 10.26.766
Help: https://docs.oracle.com/error-help/db/ora-00054/

As seen above, after 5 seconds the statement errors out with an ORA-00054 error.

Why not just use SELECT FOR UPDATE ?

You may be wondering about this; Well one of the main reasons why this feature is better is because SELECT FOR UPDATE cannot be used for INSERT statements (cannot lock a row that is about to be inserted.). The data has to already exist for it to be specified in a SELECT FOR UPDATE. Additionally, using DML WAIT[NOWAIT] only requires one statement instead of two; A SELECT FOR UPDATE and then the actual DML statement.

What if what I actually want is not to get cancelled?

For some scenarios, you may need a solution that actually allows your DML to run, not cancel it back when blocked. Well there is also another feature for this called Priority Transactions.

It provides the functionality to control when and which transactions holding row locks can be automatically rolled back. A session can nominate their locking priority to be either: LOW, MEDIUM or HIGH. When a higher priority session tries to obtain a lock on a row in a table, then if that row is already locked by another session with a lower priority; the Priority Transactions feature will then automatically roll back the low-priority blocking transactions that are blocking higher-priority transactions from obtaining row locks after a pre-defined wait time.

Because this post is not actually about this feature, here are useful links to read about it:

You can also watch the video:

It’s a very nice feature and indeed one I am very excited about.

Conclusion

The WAIT[NOWAIT] options can be used on DML statements (insert, update, delete, and merge). When a session is blocked by another session, under normal circumstances, that session has to wait until the blocking session issues a COMMIT or ROLLBACK. For multiple reasons, that TCL statement may never come and that session can hang perpetually. This feature can help improve that experience by specifying a wait time for a lock and then returning session control if it elapses.

Learn more

To read more on this feature here’s a link to a blog and video on the subject:

Hope this is helpful, Cheers 🍻.

DML WAIT[NOWAIT] in Oracle AI Database 26ai