Oracle 26ai(23.26.2): WAIT and NOWAIT Support for DELETE, UPDATE, INSERT, and MERGE Statements

 Rows modified by DML statements are locked and, if another session attempts to modify the same row, that session must wait until the row becomes available. In previous Oracle Database releases, the WAIT clause was supported only by the SELECT FOR UPDATE statement, enabling users to specify how long to wait for a row lock before the statement is rolled back.

For example:

Connected to Oracle Database 19c Enterprise Edition Release 19.0.0.0.0

SQL> select * from tbl for update wait 10;
ORA-30006: resource busy; acquire with WAIT timeout expired

Similarly, the NOWAIT clause can be used to instruct the database to return immediately if the requested row is locked by another transaction:

SQL> select * from tbl for update nowait;

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

If neither WAIT nor NOWAIT is specified, Oracle retains its default behavior and waits indefinitely for the row lock to become available:

SQL> select * from tbl for update;

Executing

Starting with Oracle AI Database 23.26.2 (Oracle AI Database 26ai), support for WAIT and NOWAIT has been extended to all DML statements, including INSERT, UPDATE, DELETE, and MERGE. These clauses enable users to control how the database proceeds when a transaction attempts to lock a row that is already locked by another transaction.

Specifying NOWAIT causes the database to roll back the DML statement immediately if a conflicting lock exists, while the WAIT clause allows users to define how long the database should wait before rolling back the statement.

DELETE

Session 1 acquires a row lock:

SQL> delete tbl where id=1;

1 row deleted

Session 2 attempts to delete the same row and waits for five seconds before timing out:

SQL> delete tbl where id=1 wait 5;

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 524315 ("usn<<16 | slot") and 536 ("sequence")

ORA-40097: Failure to acquire row lock, currently held by transaction ID 8.27.536

UPDATE

Session 1 updates a row:

SQL> update tbl set name='Vahid Yousefzadeh' where id=1 nowait;

1 row updated

Session 2 attempts to update the same row using NOWAIT and immediately receives an error:

SQL> update tbl set name='Vahid Yousefzadeh' where id=1 nowait;

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 65540 ("usn<<16 | slot") and 531 ("sequence")

ORA-40097: Failure to acquire row lock, currently held by transaction ID 1.4.531

INSERT

Session 1 inserts a row:

SQL> insert into tbl values(1,'Vahid YousefZadeh') wait 2;

1 row inserted

Session 2 attempts to insert a conflicting row and waits for two seconds before the statement is rolled back:

SQL> insert into tbl values(1,'Vahid YousefZadeh') wait 2;

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 655369 ("usn<<16 | slot") and 528 ("sequence")

ORA-40097: Failure to acquire row lock, currently held by transaction ID 10.9.528

MERGE

Session 1 modifies the target row:

SQL> update tbl set name='Vahid Yousefzadeh' where id=1;

1 row updated

Session 2 executes a MERGE statement with a WAIT clause:

MERGE INTO tbl t
USING (SELECT 1 id, 'Usef' name FROM dual) s
ON (t.id = s.id)
WHEN MATCHED THEN
  UPDATE SET t.name = s.name
WHEN NOT MATCHED THEN
  INSERT (id, name) VALUES (s.id, s.name) wait 2;

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 196640 ("usn<<16 | slot") and 573 ("sequence")

ORA-40097: Failure to acquire row lock, currently held by transaction ID 3.32.573