SQL FOR

What is SQL FOR UPDATE used for?

Introduces row-level locking or special output modes in a SELECT statement, most commonly used as FOR UPDATE or FOR SHARE.

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 FOR: PostgreSQL – full support (UPDATE, SHARE, NOWAIT, SKIP LOCKED) MySQL – supports FOR UPDATE and NOWAIT (8.0+) Oracle – supports FOR UPDATE, NOWAIT, SKIP LOCKED SQL Server – uses UPDATE ... WITH (ROWLOCK, UPDLOCK) instead; FOR JSON/XML differ SQLite – parses but ignores FOR UPDATE

SQL FOR Full Explanation

FOR is a reserved keyword that augments a SELECT query with additional behavior. In transactional databases it most often appears as FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, or FOR KEY SHARE to lock the selected rows until the current transaction ends. This prevents concurrent sessions from modifying (or, with SHARE clauses, modifying or locking) the same rows and eliminates lost-update anomalies.Some dialects also use FOR to switch the result format (FOR JSON, FOR XML), to stream results forward-only (FORWARD_ONLY in SQL Server), or to set read-only intent (FOR READ ONLY in Oracle). The common denominator is that the keyword FOR signals that what follows changes how the result set is treated by the optimizer, the lock manager, or the client API.Key behaviors- Executes immediately after the FROM clause and optional WHERE/GROUP/ORDER clauses.- Applies locks at the row level, not the table level, unless the optimizer cannot find a suitable index.- Honors the transaction isolation level but can still block or be blocked by concurrent statements.- Optional modifiers NOWAIT and SKIP LOCKED control blocking behavior.Caveats- FOR UPDATE rows cannot be updated by other sessions until COMMIT or ROLLBACK.- In PostgreSQL, combining FOR UPDATE with LIMIT requires an ORDER BY to avoid unpredictable locking order.- SQLite ignores FOR UPDATE; using it has no effect.- Misuse may cause deadlocks or performance slowdowns if large result sets are locked.

SQL FOR Syntax

-- Typical row-locking syntax
SELECT column_list
FROM   table_name
WHERE  predicate
FOR UPDATE [OF table_alias [, ...]]
  [NOWAIT | SKIP LOCKED];

-- Shared lock example (PostgreSQL)
SELECT *
FROM   accounts
WHERE  id = 42
FOR SHARE;

-- MySQL equivalent
SELECT *
FROM accounts
WHERE id = 42
FOR UPDATE;

SQL FOR Parameters

UPDATE - keyword specifying exclusive row locks
NO KEY UPDATE - weaker exclusive lock that allows foreign-key updates
SHARE - shared lock that blocks updates but not selects
KEY SHARE - weakest lock, blocks row deletes and key updates only
OF table_list - optional, limit locks to listed tables in JOINs
NOWAIT - raise error if lock cannot be acquired immediately
SKIP LOCKED - skip rows that are currently locked

Example Queries Using SQL FOR

-- Reserve seats in a ticketing system (PostgreSQL)
BEGIN;
SELECT seat_id
FROM   seats
WHERE  event_id = 55
  AND  status = 'open'
ORDER BY seat_id
LIMIT 2
FOR UPDATE SKIP LOCKED;
-- application updates the two rows to status = 'held'
COMMIT;

-- Prevent blocking with NOWAIT (Oracle)
SELECT *
FROM   orders
WHERE  id = 9001
FOR UPDATE NOWAIT;

Expected Output Using SQL FOR

The selected rows are returned to the client and are simultaneously locked
Other sessions that attempt conflicting operations will either wait, raise an error (NOWAIT), or skip the rows (SKIP LOCKED)
No data is modified unless a subsequent UPDATE or DELETE is executed by the current transaction

Use Cases with SQL FOR

Implementing work queues where multiple workers claim tasks without collisions
Guaranteeing consistent reads before performing an update based on the same rows
Building reservation systems (tickets, hotel rooms) that must not double-book
Creating optimistic concurrency fallbacks when a uniqueness check is insufficient
Reading data in a cursor for later positioned updates

Common Mistakes with SQL FOR

Forgetting to wrap FOR UPDATE queries in a transaction, causing the lock to release immediately
Locking an entire joined result set instead of limiting locks with OF table_list
Using FOR UPDATE in read-only replicas where writes are not allowed
Expecting SQLite to enforce locks when it silently ignores FOR UPDATE
Combining SKIP LOCKED with ORDER BY without realizing that the skipped rows can change ordering semantics

First Introduced In

SQL92 (FOR UPDATE added to the SELECT grammar)

Frequently Asked Questions

Does FOR UPDATE guarantee isolation?

It guarantees that no concurrent transaction can modify or delete the locked rows, but it does not protect against phantom rows unless your isolation level is SERIALIZABLE.

Can I use FOR UPDATE without BEGIN/COMMIT?

Yes, but the database will implicitly wrap the statement in a short transaction that ends immediately, releasing the lock as soon as the results are returned. Explicit transactions are recommended.

How does FOR SHARE differ from FOR UPDATE?

FOR SHARE places a shared lock. Other sessions can take the same shared lock and read the row but cannot update or delete it. FOR UPDATE uses exclusive locks that block both reads with FOR SHARE and all writes.

What happens if two sessions run the same FOR UPDATE query?

One session will acquire the row locks first. The second session will wait, raise an error (NOWAIT), or skip the locked rows (SKIP LOCKED) depending on the chosen modifier.