SQL NOWAIT

What is SQL NOWAIT?

NOWAIT makes a locking statement return an error immediately instead of waiting when the requested lock is held by another session.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL NOWAIT: Supported: PostgreSQL 9.3+, Oracle 9i+, SQL Server (NOWAIT table hint). Not supported: MySQL, MariaDB, SQLite.

SQL NOWAIT Full Explanation

NOWAIT is an optional clause appended to lock-acquiring statements such as SELECT … FOR UPDATE, SELECT … FOR SHARE, and LOCK TABLE. When present, the database tries to obtain the requested lock only once. If the lock is already held by another transaction, the statement aborts right away with a lock-related error instead of blocking until the lock becomes available. This behavior is crucial for building low-latency, deadlock-resistant applications and for keeping interactive sessions responsive. NOWAIT is not part of the ANSI SQL standard, but several major databases implement it with similar semantics. In PostgreSQL, a NOWAIT failure raises the SQLSTATE 55P03 (lock_not_available). Oracle and SQL Server expose the same idea in slightly different syntaxes, but the outcome is identical: fail fast when contention occurs. Be sure to handle the possible error in application code and to understand that NOWAIT only affects lock acquisition, not statement execution once the lock is granted.

SQL NOWAIT Syntax

-- Row-level lock
SELECT *
FROM   orders
WHERE  id = 42
FOR UPDATE NOWAIT;

-- Share lock
SELECT *
FROM   inventory
FOR SHARE NOWAIT;

-- Table-level lock
LOCK TABLE orders IN EXCLUSIVE MODE NOWAIT;

SQL NOWAIT Parameters

Example Queries Using SQL NOWAIT

-- Session 1 obtains a lock
BEGIN;
SELECT * FROM orders WHERE id = 42 FOR UPDATE;

-- Session 2 attempts to lock the same row but refuses to wait
SELECT * FROM orders WHERE id = 42 FOR UPDATE NOWAIT;  -- ERROR: could not obtain lock on row in relation "orders"

-- Fast-failing maintenance lock
LOCK TABLE products IN ACCESS EXCLUSIVE MODE NOWAIT;

Expected Output Using SQL NOWAIT

If the lock is free, the statement succeeds and returns the selected rows or confirmation of the lock
If the lock is held by another transaction, the database raises a lock_not_available error immediately and the statement fails

Use Cases with SQL NOWAIT

Interactive applications that must stay responsive and cannot afford long blocking waits.
Job schedulers or background workers that can skip or reschedule work instead of waiting.
Deadlock avoidance strategies where rapid failure is preferred to circular waits.
Administrative scripts that should proceed only when no one else is using a table.

Common Mistakes with SQL NOWAIT

Using NOWAIT in databases that do not support it (e.g., MySQL, SQLite).
Forgetting to wrap NOWAIT statements in try/catch logic, causing unhandled errors.
Assuming NOWAIT skips locked rows. It does not; it fails entirely. Use SKIP LOCKED for row skipping.
Expecting NOWAIT to control statement timeout length. It is an immediate check only.

First Introduced In

PostgreSQL 9.3; Oracle 9i; SQL Server 2005 (NOWAIT hint)

Frequently Asked Questions

What happens if the lock is free?

The statement acquires the lock instantly and proceeds to return the requested rows or confirmation, just like a normal locking statement.

What error code does PostgreSQL raise when NOWAIT fails?

PostgreSQL raises SQLSTATE 55P03 (lock_not_available) with the message "could not obtain lock on row/table" depending on the lock type.

Can NOWAIT be combined with SKIP LOCKED?

No. In PostgreSQL you must choose one or the other. NOWAIT aborts on the first locked row, while SKIP LOCKED silently ignores locked rows.

How do I implement a retry strategy with NOWAIT?

Wrap the NOWAIT statement in application code that catches the lock_not_available error, sleeps (optionally with back-off), and retries the statement until it succeeds or a maximum number of attempts is reached.