SELECT FOR UPDATE in SQL: Definition and Examples

SELECT FOR UPDATE is a SQL statement that reads rows from a table and immediately locks them for modification. 

No other transaction can update, delete, or lock those same rows until the current transaction either commits or rolls back. It’s the standard SQL mechanism for pessimistic row-level locking: you’re telling the database, “I’m about to change these rows. Hold them.”

This comes up frequently in high-concurrency workloads where two or more transactions might otherwise race to update the same data: booking systems, inventory management, financial transfers, and any scenario where reading stale data before a write would cause a consistency problem.

What Does SELECT FOR UPDATE Do?

When a transaction runs SELECT FOR UPDATE, the database places an exclusive lock on every row returned by the query. Those locks are held until the transaction ends.

The basic syntax looks like this:

BEGIN;

SELECT balance
FROM accounts
WHERE account_id = 42
FOR UPDATE;

-- application logic here

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 42;

COMMIT;

The FOR UPDATE clause on the SELECT ensures no other transaction can touch account_id = 42 between the read and the write. Without it, a concurrent transaction could read the same balance, make its own update, and commit before yours, leading to a lost update.

What Problems Does SELECT FOR UPDATE Solve?

The core problem it addresses is the lost update anomaly. In a concurrent system, two transactions can read the same value, compute a new value independently, and each write back, with the second write silently overwriting the first.

SELECT FOR UPDATE prevents this by serializing access. Only one transaction can hold the lock on a given row at a time. Others must wait.

What Is the Difference Between SELECT FOR UPDATE and SELECT FOR SHARE?

Both statements lock rows, but they differ in what they block.

SELECT FOR UPDATE acquires an exclusive lock. It blocks any other transaction from modifying, deleting, or locking the rows, including other SELECT FOR UPDATE statements. Use this when you intend to write.

SELECT FOR SHARE acquires a shared lock. Multiple transactions can hold shared locks on the same rows simultaneously, but none of them can write. Use this when you need a consistent read and want to prevent concurrent writes, but don’t need to modify the rows yourself.

A third option, SELECT FOR NO KEY UPDATE, behaves like SELECT FOR UPDATE but acquires a weaker lock that does not block SELECT FOR KEY SHARE. This is relevant when foreign key checks are involved, and you want to avoid unnecessary lock contention.

How Do You Use SELECT FOR UPDATE With SKIP LOCKED and NOWAIT?

By default, a SELECT FOR UPDATE on a locked row will block and wait. SQL gives you two modifiers to change that behavior.

NOWAIT causes the statement to fail immediately if any of the target rows are already locked, rather than waiting:

SELECT * FROM orders
WHERE status = 'pending'
FOR UPDATE NOWAIT;

If another transaction holds a lock on any matching row, the query returns an error instead of blocking. This is useful when your application can handle retry logic and you’d rather fail fast than queue.

SKIP LOCKED skips rows that are already locked and returns only the unlocked ones:

SELECT * FROM jobs
WHERE status = 'queued'
LIMIT 10
FOR UPDATE SKIP LOCKED;

This pattern is common in job queue implementations where multiple workers pull tasks concurrently. Each worker grabs a batch of unlocked rows and processes them independently, with no worker waiting on another.

When Should You Use SELECT FOR UPDATE?

Use it when you need to ensure that a row you read will not be changed before you write to it. Common scenarios:

• Inventory reservation: Read available stock, lock the row, decrement the count
• Seat or room booking: Confirm availability and reserve in a single transaction
• Account transfers: Read balances, lock both accounts, apply debit and credit
• Job queue processing: Claim tasks for exclusive processing without race conditions

Avoid it when the probability of contention is low, and your application can handle retry logic. In those cases, optimistic concurrency control (letting conflicts surface at commit time) is more scalable. Pessimistic locking trades throughput for certainty; it’s the right call when certainty is required.

How Does SELECT FOR UPDATE Work in YugabyteDB?

How Does SELECT FOR UPDATE Work in YugabyteDB?

YugabyteDB is a PostgreSQL-compatible, open source distributed SQL database, and its YSQL API supports SELECT FOR UPDATE with the same syntax as PostgreSQL, including NOWAIT, SKIP LOCKED, and the standard lock modes.

What’s worth understanding is how row-level locking works in a distributed context. YugabyteDB’s default concurrency model uses optimistic locking with conflict detection at commit time. SELECT FOR UPDATE engages explicit pessimistic row-level locking on top of that model, backed by ACID-compliant distributed transactions. Lock acquisition and enforcement happen consistently across nodes and shards, coordinated through a wait queue with built-in deadlock detection, so a transaction blocked on a lock held by another node still gets the same wait-or-abort guarantees you’d expect from a single-node database.

One thing to check before migrating: NOWAIT and SKIP LOCKED support currently varies by isolation level. NOWAIT is supported under Read Committed isolation. SKIP LOCKED is supported under both Read Committed and Snapshot isolation, but not yet under Serializable. Most applications moving from PostgreSQL run under Read Committed and won’t hit this, but if your application locks rows under Serializable isolation, check the current documentation for the latest support status before assuming full parity.

With that caveat noted, SELECT FOR UPDATE query patterns from PostgreSQL generally carry over to YSQL without rewrites.

YugabyteDB Aeon offers a free Sandbox cluster with full YSQL support, making it easy to try SELECT FOR UPDATE in a live cluster.