WAIT and NOWAIT: Giving Every DML Statement a Lock Timeout

Who this is for: anyone who has run a one-off UPDATE, gotten pulled away before committing it, and come back to find three other sessions queued up behind a lock nobody could tell how long to wait for.

A teammate was fixing a bad row in production last summer, a quick UPDATE to correct a mis-set status column. Before she could commit, a VPN prompt popped up, then a call came in, and the fix sat there uncommitted for close to two hours. Nothing else touching that row had any say in the matter. The support ticket that needed the same row updated just hung. A batch job that touched the same table queued up behind it. Everyone downstream had exactly one option: wait, with no way to know if “almost done” or “forgotten until Monday” was more likely. SELECT FOR UPDATE has let you say “wait 5 seconds and give up” for decades. Plain INSERT, UPDATE, DELETE, and MERGE never had that option, you were stuck with whatever the default lock wait behavior gave you. Oracle AI Database 26ai’s April 2026 quarterly update (23.26.2) closes that gap.

Why This Matters

Every row a DML statement touches gets locked until the transaction that touched it commits or rolls back. If a second transaction wants that same row, it waits. That’s correct and necessary, two transactions can’t both change the same row at once, but “wait” has traditionally meant “wait indefinitely” for ordinary DML. If the blocking transaction never comes back (a dropped connection, someone stepping away mid-edit, a batch job that hung), everything queued behind it just sits there too, with no timeout and no way to fail fast and retry.

SELECT ... FOR UPDATE has always let you get ahead of this, by locking the rows you’re about to change before you change them, with a WAIT n or NOWAIT clause controlling how long you’re willing to sit there. The catch is that this only helps when you know which rows you’re about to touch before you touch them, and plenty of DML doesn’t work that way. An INSERT can collide with another session inserting the same primary key or unique value. A child-table INSERT can end up waiting on a parent-table DELETE that hasn’t committed yet, because until that delete resolves, nobody knows if the foreign key value is actually still there. In both cases there was nothing to run SELECT FOR UPDATE against ahead of time. You just issued the DML and hoped.

Starting with this release, INSERT, UPDATE, DELETE, and MERGE all accept the same kind of wait clause SELECT FOR UPDATE has had, plus a bit more precision than before.

The syntax

Three forms, and they all attach directly to the end of the statement:

			
-- Default behavior, unchanged: wait indefinitely
UPDATE accounts SET status = 'CLOSED' WHERE account_id = 501 WAIT FOREVER;
-- Give up immediately if the row is locked
DELETE FROM accounts WHERE account_id = 501 NOWAIT;
-- Wait up to a fixed amount of time, then error out
INSERT INTO ledger (account_id, amount) VALUES (501, 100) WAIT 5 SECONDS;
MERGE INTO accounts a USING staging s ON (a.account_id = s.account_id)
  WHEN MATCHED THEN UPDATE SET a.status = s.status
  WAIT 500 MILLISECONDS;

		

WAIT FOREVER is the default and matches how DML has always behaved, so existing statements don’t change behavior unless you add a clause. NOWAIT fails the statement the instant it hits a lock. WAIT n takes an integer and, unlike the old SELECT FOR UPDATE WAIT n which only ever meant seconds, now accepts SECONDS, MILLISECONDS, or MICROSECONDS as a unit, with seconds as the default if you leave the unit off. That sub-second granularity matters more than it sounds: waiting a full second for a lock is often too long for a high-throughput OLTP path, but failing instantly on the first sign of contention is too aggressive when the blocking transaction is likely to release in a few milliseconds anyway. The same finer-grained units now also apply to SELECT FOR UPDATE, so that statement gets the upgrade too, not just the newly-covered ones.

Diagram showing three ways a DML statement can meet a locked row: WAIT FOREVER waits indefinitely, NOWAIT fails instantly with ORA-00054, and WAIT 5 SECONDS waits then fails with the same error — The default hasn’t changed. NOWAIT and WAIT n are the new options for every DML statement.

What actually happens when a wait times out

Open two sessions against the same row and you can see it directly. In session 1, update a row and don’t commit:

			
-- Session 1
UPDATE accounts SET status = 'REVIEW' WHERE account_id = 501;
-- no commit

In session 2, try the same row with a short wait:

			
-- Session 2
UPDATE accounts SET status = 'CLOSED' WHERE account_id = 501 NOWAIT;
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

That’s the same ORA-00054 that SELECT FOR UPDATE NOWAIT and LOCK TABLE have always raised, extended to cover the rest of DML. Nothing about your error handling for lock contention needs to learn a new error code, you’re catching the same one, just from statements that couldn’t raise it before. A WAIT n clause that times out raises the identical error once the clock runs out; the difference from NOWAIT is purely how long the database was willing to sit there first.

Where This Requires Care

Adding NOWAIT or a short WAIT n to a statement means you’re now responsible for catching ORA-00054 and deciding what to do about it: retry with backoff, surface an error to the user, log it and move on. If you bolt NOWAIT onto existing DML without adding that handling, you’ve just turned an occasional slow statement into an occasional hard failure, which is worse for a workload that used to tolerate waiting.

Picking a wait value is a judgment call specific to your workload, and it’s easy to get wrong in either direction. Too short, and normal, brief contention (two sessions both touching a hot row for a few milliseconds) starts throwing errors that a slightly longer wait would have avoided entirely. Too long, and you’ve recreated the original problem, just with extra syntax. This pairs naturally with automatic transaction rollback for priority transactions, also new in 26ai: that feature proactively rolls back a low-priority blocker before a high-priority waiter even gets stuck, while WAIT/NOWAIT is the reactive tool you reach for on any individual statement, regardless of whether priority-based rollback is configured at all. They solve overlapping problems from different directions and you can use either without the other.

None of this replaces actually fixing the reason a transaction stays open too long. A WAIT clause makes the downstream symptom (everyone else hanging) controllable, it doesn’t shorten how long the original transaction sits uncommitted. If your application logic routinely leaves transactions open for minutes at a time, that’s still worth fixing on its own.

Quick Reference

New in Oracle AI Database 26ai, release update 23.26.2 (April 2026): INSERT, UPDATE, DELETE, and MERGE all accept a WAIT/NOWAIT clause, matching what SELECT FOR UPDATE has always had.
Three forms: WAIT FOREVER (default, unchanged behavior), NOWAIT (fail instantly on a lock), WAIT n [SECONDS|MILLISECONDS|MICROSECONDS] (timeout after n units, seconds by default).
The new sub-second units (MILLISECONDS, MICROSECONDS) also apply to SELECT FOR UPDATE, not just the newly-covered statements.
A timed-out or failed wait raises the same ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired you already know from SELECT FOR UPDATE NOWAIT and LOCK TABLE.
Adding a short wait or NOWAIT means you now own catching ORA-00054 and deciding whether to retry, surface the error, or log it.
Complements, but is independent from, automatic transaction rollback for priority transactions: that feature is proactive and priority-based, this one is a per-statement, reactive timeout you can use regardless.

My Take

This is a small piece of syntax with an outsized practical payoff. It doesn’t need a licensing check, a system parameter, or any setup at all, you just add a clause to a statement you were already writing. The part I’d actually spend time on is deciding where it belongs: I wouldn’t sprinkle NOWAIT across every DML statement in an application, but for the specific paths where a hung lock is worse than a fast, retryable failure (an interactive UI action, a queue worker that can just re-pick the message), this is exactly the knob that was missing. My teammate’s two-hour stuck UPDATE wouldn’t have been prevented by this feature, but everything that piled up behind it could have failed fast and retried instead of quietly waiting for someone to notice.

Arvind Toorpu