Only one transaction can change a row at a time. This can be a problem if two people try to update the same rows at the same time. The second transaction must wait for the first to complete to make its changes.

Worst case, the first transaction goes missing. Perhaps a network issue cut the application’s connection to the database. Or someone ran a manual update, forgot about it, and went home for the night. Either way, the transaction stays open, and the rows stay locked.

In cases like these, the database is waiting for a commit that never comes. All other DML trying to change the locked rows must wait, suspended until the blocking transaction completes. This leads to the other updates hanging, waiting to lock the rows they want to change.

You can avoid this by running select for update at the start of a transaction when you know which rows you’ll change. This allows you to specify how long to wait for a lock in seconds (wait n) or give up instantly (nowait).

For example, this opens a cursor that waits up to 5 seconds to get a lock on the row for employee_id 142. If it’s still waiting after this time, it’ll raise an error. Otherwise, the update starts, safe in the knowledge that it has the rows locked:

This gives an upper limit for how long your transaction will be blocked waiting for a lock. You can also use select for update to lock rows in a consistent order; a useful tool to avoid deadlocks.

But it’s easy to forget to do this. Also, select for update doesn’t help you if the rows don’t exist at the start of the transaction!

This can happen when you insert data in cases like:

  • Many transactions adding the same values into the columns of primary keys or unique constraints. One of the transactions must complete for the others to do the uniqueness check.
  • Loading values into foreign key columns. If another transaction is deleting the parent row for these values, whether the parent value exists is unknown. So, the insert must wait for the delete to complete and commit to see if the parent is present.

Technically, you can avoid the second problem. Do this by ensuring both the child insert and parent delete issue select for update [no]wait on the parent row first. But when was the last time you did that?!

So, select for update [no]wait only helps when you know which rows you’ll change at the start of the transaction. If you don’t, historically you just had to run your DML statements and hope they weren’t blocked indefinitely.

To fill this gap, Oracle AI Database introduced a wait clause for all DML statements in release 23.26.2.

Examples of the DML [no]wait clause in 23.26.2 insert into … nowait select … for update wait 42 microseconds update … set … wait 42 milliseconds delete from … wait 42 seconds merge into … wait forever You use the [no]wait clauses with any of the statements shown

DML [no]wait clause

The wait clause adds these options to all DML statements (insert, update, delete, and merge):

  • wait forever – default, wait indefinitely when blocked.
  • wait n [ [ milli | micro ]seconds ] – timeout after N time units.
  • nowait – error instantly if blocked.

For example, this waits up to 5 seconds to lock the rows:

In many systems, waiting even just a second for a lock is too long. But you don’t want to fail instantly if another transaction briefly holds the lock. So, this enhancement also extends select for update to allow wait units smaller than seconds.

Sign up for an Always Free Oracle Autonomous Database to try it out today.