In Oracle AI Database 26ai the WAIT and NOWAIT clauses are available for all main DML statements: INSERT, UPDATE, DELETE and MERGE.

They let an application decide what to do when a DML statement tries to modify a row that is already locked by another transaction:

• NOWAIT – fail immediately instead of waiting for the lock.
• WAIT n – wait for the specified time and fail if the row is still locked.
• WAIT FOREVER – keep the traditional Oracle behavior and wait indefinitely.

This makes lock handling explicit at statement level and avoids changing a session-wide setting.

Syntax

INSERT ... NOWAIT;
INSERT ... WAIT 5 SECONDS;

UPDATE ...
 WHERE ...
 WAIT 500 MILLISECONDS;

DELETE ...
 WHERE ...
 WAIT 2 SECONDS;

MERGE ...
 WHEN MATCHED THEN ...
 WHEN NOT MATCHED THEN ...
 WAIT FOREVER;

The supported time units are SECONDS, MILLISECONDS and MICROSECONDS. If the unit is omitted, Oracle uses seconds.

If neither clause is specified, the statement keeps the default behavior and waits indefinitely for the row lock.

Test environment

It’s very important to have latest version 26ai. The examples below were verified on:

Oracle AI Database 26ai Free Release 23.26.3.0.0
Version 23.26.3.0.0

Prepare test data

DROP TABLE IF EXISTS dml_wait_demo PURGE;

CREATE TABLE dml_wait_demo
(
  id     NUMBER PRIMARY KEY,
  status VARCHAR2(30)
);

INSERT INTO dml_wait_demo VALUES (1, 'READY');
COMMIT;

NOWAIT example

Open two database sessions. In session 1 update the row, but do not commit or roll back the transaction:

-- session 1
UPDATE dml_wait_demo
   SET status = 'LOCKED_BY_SESSION_1'
 WHERE id = 1;

1 row updated.

The row is now locked by session 1. Run the following statement in session 2:

-- session 2
UPDATE dml_wait_demo
   SET status = 'SESSION_2_NOWAIT'
 WHERE id = 1
NOWAIT;

The statement returns immediately:

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")
ORA-40097: Failure to acquire row lock, 
currently held by transaction ID ...

Elapsed: 00:00:00.01

NOWAIT is useful for interactive applications, job workers and APIs where waiting behind another transaction is not acceptable.

WAIT example

Keep the lock in session 1 and execute the following statement in session 2:

-- session 2
UPDATE dml_wait_demo
   SET status = 'SESSION_2_WAIT'
 WHERE id = 1
WAIT 2 SECONDS;

If session 1 does not release the lock within two seconds, Oracle rolls back only the blocked DML statement and reports the lock error:

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")
ORA-40097: Failure to acquire row lock, 
currently held by transaction ID ...

Elapsed: 00:00:02.02

The session and any earlier work in its transaction remain available. The application can catch the error and decide whether to retry, return a busy response or use another row.

Milliseconds example

Release the lock in session 1:

-- session 1
ROLLBACK;

Now the same type of statement can acquire the row immediately:

-- session 2
UPDATE dml_wait_demo
   SET status = 'UPDATED_AFTER_RELEASE'
 WHERE id = 1
WAIT 500 MILLISECONDS;

1 row updated.

Elapsed: 00:00:00.00

Check the result and roll back the test:

SELECT id, status
FROM dml_wait_demo;

        ID STATUS
---------- ------------------------------
         1 UPDATED_AFTER_RELEASE

ROLLBACK;

WAIT FOREVER

WAIT FOREVER documents that the statement is intentionally allowed to wait without a timeout:

DELETE FROM dml_wait_demo
 WHERE id = 1
WAIT FOREVER;

It is equivalent to the default lock-wait behavior, but makes the intention visible in the SQL text.

Important notes

• The clause controls waiting for row locks encountered by the current DML statement.
• NOWAIT and an expired WAIT roll back the statement, not the complete transaction.
• Use a finite wait when a short delay is acceptable but an unlimited wait is not.
• Use NOWAIT when the caller should immediately retry, skip the work or report that the resource is busy.
• SKIP LOCKED remains a different option for SELECT ... FOR UPDATE queue-style processing: it skips locked rows instead of failing or waiting.

Cleanup

DROP TABLE dml_wait_demo PURGE;

More details are available in the Oracle documentation for UPDATE, DELETE, INSERT and MERGE.

Have a fun 🙂

  Tomasz