What does `SELECT ... FOR UPDATE` give you that a plain `SELECT` does not, and how does `FOR SHARE` differ from it?

## The problem it solves Consider decrementing inventory: ```sql SELECT stock FROM items WHERE id = 42; -- returns 1 -- application decides 1 >= 1, so the sale is allowed UPDATE items SET stock = stock - 1 WHERE id = 42; ``` Under MVCC the `SELECT` acquired no lock and returned a snapshot value. Two sessions can both read `1`, both decide the sale is allowed, and both decrement — leaving `-1`. The read told you the truth about the past, not a promise about the future. This is the **read-modify-write race**, and it is the single most common concurrency bug in application code. `SELECT ... FOR UPDATE` converts the read into a **claim**: it takes an exclusive row lock on each row it returns and holds it until the transaction ends. The second session now blocks at the `SELECT`, and when it is released it re-reads the current value — `0` — and correctly refuses the sale. ## The two locking-read modes **`FOR UPDATE`** — exclusive row locks. Incompatible with everything: other `FOR UPDATE` readers, `FOR SHARE` readers, and writers all wait. Use it whenever this transaction intends to modify the rows, or must be the only one allowed to act on them. **`FOR SHARE`** — shared row locks. Compatible with other `FOR SHARE` holders; blocks writers. Use it when you need the rows to remain unchanged for the duration of your transaction but do not intend to modify them yourself — verifying a parent row still exists before inserting a child, for example. This is essentially what foreign-key enforcement does internally. Both are **locking reads**, and both hold their locks to the end of the transaction regardless of isolation level. That last part matters: unlike an ordinary read lock at READ COMMITTED, these are not released when the statement finishes. ## Snapshot bypass A subtlety worth stating in an interview: in a snapshot-based engine at READ COMMITTED, a locking read does not just add a lock — after waiting for a conflicting writer it re-reads the **latest committed version** of the row, not the version from its own snapshot. PostgreSQL calls this the EPQ / re-check path; InnoDB describes it as a consistent-read exception. Without it the pattern would be useless: you would block, then act on the stale value anyway. At REPEATABLE READ the engines differ in how they reconcile the newly-read row with the transaction's snapshot: PostgreSQL raises a serialization failure rather than silently mixing versions, which the application must retry. ## The wait modifiers Standard SQL and the major engines offer three behaviours when the requested rows are already locked: - **Wait (default)** — block until the lock is free or the lock-timeout fires. - **`NOWAIT`** — raise an error immediately instead of queueing. Right for interactive paths where a user is waiting and a fast, honest failure beats an unbounded stall. - **`SKIP LOCKED`** — silently omit locked rows from the result. This is the foundation of the database-as-a-queue pattern: N workers each run `SELECT ... FOR UPDATE SKIP LOCKED LIMIT 10`, and each gets a disjoint batch with no coordination and no contention. It is deliberately non-serializable — the result set depends on who else is running — so use it only where "any available rows" is the intent. ## Conversion deadlocks The most common misuse is `SELECT ... FOR SHARE` followed by an `UPDATE` of the same rows. Two transactions both take S, both then request X, and each waits for the other's S to be released — a deadlock the detector must break. If you know you will write, take `FOR UPDATE` immediately. This is the same problem the update (U) lock mode exists to solve in engines that offer it. ## Locking too much `FOR UPDATE` locks **every row the statement returns**, so `SELECT ... FOR UPDATE` over an unbounded result set locks an unbounded number of rows for the whole transaction. Keep locking reads narrow: a primary-key or small-range predicate, ideally with `LIMIT`. Also beware joins — by default the locks apply to all tables in the query, so a locking read that joins a large lookup table locks rows there too unless you restrict it with `OF <table>`. And because these locks live until commit, the transaction wrapping them should do as little as possible: never an HTTP call, never user think-time, between the locking read and the commit. ## The alternative Pessimistic locking is not the only answer. **Optimistic concurrency** — read a version column, then `UPDATE ... WHERE id = ? AND version = ?` and check the affected-row count — takes no locks and retries on conflict. It wins under low contention and short retry loops; `FOR UPDATE` wins when conflicts are common, when the work between read and write is expensive to redo, or when several rows must be claimed together in a fixed order. ## What interviewers listen for The read-modify-write race as the motivation, the correct mode mapping (exclusive vs shared), locks held to commit, the snapshot-bypass re-read, and at least one of `SKIP LOCKED` / `NOWAIT` used for the right reason. Mentioning the FOR SHARE → UPDATE conversion deadlock, and comparing against optimistic versioning, is what separates a solid answer from a memorised one.