SELECT FOR UPDATE

DatabasesMiddb

The Question

What does SELECT FOR UPDATE do and when would you use it?

What a Strong Answer Covers

• Rows locked until commit
• other txns block
• use case.

Senior-Level Answer

`SELECT FOR UPDATE` is a SQL statement that reads rows and immediately acquires an exclusive row-level lock on them within the current transaction. Other transactions attempting to modify those rows — or acquire their own `SELECT FOR UPDATE` locks — will block until your transaction completes. Regular reads (without `FOR UPDATE` or `FOR SHARE`) are typically not blocked, depending on the isolation level and database engine.

The primary use case is pessimistic concurrency control: you want to read a value, make a decision based on it, and update it — guaranteeing that no other transaction can change those rows between your read and your write. A classic example is an inventory system: `SELECT quantity FROM inventory WHERE product_id = 42 FOR UPDATE`, followed by a check and `UPDATE inventory SET quantity = quantity - 1`. Without the lock, two concurrent transactions could both read quantity=1, both decide to decrement, and both commit — leaving quantity at -1.

Another common pattern is job queue processing: workers select a batch of pending jobs with `FOR UPDATE SKIP LOCKED` (supported in PostgreSQL, MySQL 8+, Oracle). `SKIP LOCKED` causes the query to skip rows already locked by other workers, enabling efficient parallel queue draining without contention or blocking.

Key operational considerations: `SELECT FOR UPDATE` escalates lock scope to all rows returned, so a poorly written predicate can lock large swaths of the table and cause cascading blocking. Lock duration is tied to transaction duration — long-running transactions holding these locks are a common source of production latency spikes. Deadlocks are also possible when two transactions lock rows in opposite orders; applications must handle deadlock errors with retry logic.

In PostgreSQL, `FOR UPDATE` can be qualified with `OF table_name` in joins to specify which table's rows to lock, avoiding unintended wide locks in complex queries.

What Separates a 2/3 from a 3/3

Common Mistakes

• Thinking SELECT FOR UPDATE blocks all readers — it typically only blocks other writers and FOR UPDATE/FOR SHARE readers, not plain SELECTs.
• Using SELECT FOR UPDATE on a large result set without understanding it locks every matched row.
• Not handling deadlock errors in application code when using FOR UPDATE in concurrent workloads.
• Confusing SELECT FOR UPDATE (pessimistic) with optimistic locking — they solve the same problem with opposite strategies.

Follow-Up Questions

• What is SKIP LOCKED and when would you use it? — Skips rows currently locked by other transactions — enables parallel job queue processing where multiple workers each grab their own non-overlapping batch.
• How would you avoid a deadlock when two transactions both use SELECT FOR UPDATE? — Always acquire locks in a consistent order across transactions. Also implement retry logic with exponential backoff on deadlock detection.
• How does SELECT FOR UPDATE differ from optimistic locking with a version column? — FOR UPDATE is pessimistic — locks rows upfront, blocking contention. Optimistic locking detects conflicts at write time via version check — better for low-contention scenarios.
• If a transaction with SELECT FOR UPDATE is slow, what impact does it have on other transactions? — Other transactions trying to modify those rows queue up — long lock holders cause cascading latency. Monitor long-running transactions in production.

Related Questions

• ACID
• Indexes
• Clustered vs Non-clustered
• Isolation Levels
• Normalization