Postgres FOR SHARE vs FOR UPDATE: The Locking Mode Most Developers Skip
SELECT FOR UPDATE is the row-locking mode every developer eventually learns. SELECT FOR SHARE is the weaker variant most developers never reach for. Knowing when each is right is the difference between a queue that scales and a queue that thrashes.
Postgres has four row-level locking modes that you can request explicitly inside a transaction: FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, and FOR KEY SHARE. The first one is the famous one, the one every textbook covers as the way to prevent the read-modify-write race condition. The other three are less well-known and are skipped over in most application code, which is unfortunate because the wrong choice of locking mode produces contention that the correct choice would avoid.
What each mode actually locks
FOR UPDATE is the strongest mode. It takes a row-level exclusive lock that conflicts with any other locking mode on the same row. A second transaction that runs SELECT FOR UPDATE or FOR SHARE or FOR NO KEY UPDATE on the same row blocks until the first transaction commits or rolls back.
FOR NO KEY UPDATE is slightly weaker. It conflicts with FOR UPDATE and FOR NO KEY UPDATE but not with FOR KEY SHARE. The distinction matters when a foreign key references the locked row: a FOR KEY SHARE lock (which is what Postgres uses internally for foreign-key integrity checks) does not block, and the foreign-key-referencing transaction can proceed.
FOR SHARE is the shared mode. Multiple transactions can hold FOR SHARE on the same row simultaneously, but FOR UPDATE blocks until all share-holders commit. This is the read-with-protection-from-writes mode: you are saying "I am reading this row and I do not want it to change underneath me, but I am happy for other readers to coexist."
FOR KEY SHARE is the weakest. It only blocks UPDATEs and DELETEs that would change the row's primary key. Concurrent UPDATEs to non-key columns proceed without blocking. This is the mode Postgres uses internally for foreign-key checks: the referenced row's key must not change while the referencing row exists, but other columns can change freely.
When FOR SHARE is the right choice
The canonical case is verifying a condition before performing an action elsewhere. A transaction that checks an account balance before debiting from a different account wants to ensure the balance does not change between the read and the debit, but it does not need to prevent other readers from also checking the balance. FOR SHARE on the balance row allows multiple concurrent transactions to verify and act on different downstream rows without serializing on the balance lookup.
The contention pattern is different. Under FOR UPDATE, every transaction that reads the balance row serializes. Under FOR SHARE, multiple readers proceed in parallel and only conflict with writers. The throughput improvement is substantial in read-heavy workloads where the protected row is read more often than it is written.
The second case is foreign-key integrity in application code. If you are about to insert a row that references a parent and you want to ensure the parent is not deleted between your check and your insert, FOR KEY SHARE on the parent is the right lock. It is what Postgres uses internally for the same purpose, and it does not block UPDATEs to the parent's non-key columns, which is the failure mode FOR UPDATE introduces.
The queue worker case
The most operationally important case is queue workers competing to claim work items. The naive pattern is SELECT FOR UPDATE on the work-item row, which serializes the workers and turns the queue into a contention bottleneck. The correct pattern is SELECT FOR UPDATE SKIP LOCKED, which allows workers to skip rows that are already locked by another worker rather than waiting.
FOR SHARE is wrong for queue workers because two workers claiming the same row would both succeed on the FOR SHARE and then conflict when they tried to UPDATE the row to mark it as claimed. The shared lock allows the race condition the exclusive lock prevents.
The SKIP LOCKED modifier is the load-bearing detail. Without it, a worker waiting for a row locked by a stuck worker will block indefinitely. With it, the worker moves on to the next available row immediately. The combination of FOR UPDATE SKIP LOCKED is the production-correct queue claim primitive in Postgres.
The NOWAIT modifier
The third modifier is NOWAIT, which causes the lock acquisition to fail immediately with an error if the row is already locked. This is the fail-fast variant: instead of waiting or skipping, the transaction gets a clear error it can handle in application code.
NOWAIT is right for interactive use where blocking is unacceptable. If a customer's API request is trying to acquire a lock and another request is holding it, returning a 409 Conflict to the customer immediately is usually better than holding the customer's connection open while you wait. The customer can retry; the connection does not have to be held.
NOWAIT is wrong for background workers where retries are cheap and contention is normal. The error-handling overhead exceeds the wait time in most cases. SKIP LOCKED is the better choice when the worker can move on to other work.
What the modes do not solve
None of the row-level locking modes solve the read-after-write consistency problem for cached data. If your application has a read-through cache and the cached value comes from a row that has been updated under FOR UPDATE, the cache is stale until it is invalidated. The locking mode controls the database; it does not control the cache layer.
None of the modes solve the application-level lost-update problem when the application reads outside a transaction and writes inside one. The lock has to be acquired in the same transaction as the read; otherwise the value the application based its update on may have changed before the lock was acquired.
None of the modes prevent the row from being deleted by a transaction that holds a stronger lock. FOR SHARE does not protect against FOR UPDATE; it just makes them mutually blocking.
The deeper observation
Row-level locking in Postgres has more shape than the textbook FOR UPDATE summary suggests. The weaker modes (FOR SHARE, FOR KEY SHARE) are designed for specific concurrency patterns where exclusion is overkill, and using them where they fit reduces contention substantially. The stronger modes (FOR UPDATE, FOR NO KEY UPDATE) are for the cases where exclusion is genuinely needed.
The choice between them is one of the small operational decisions that compounds. A queue worker that uses FOR UPDATE SKIP LOCKED scales differently than one that uses plain FOR UPDATE. A balance check that uses FOR SHARE scales differently than one that uses FOR UPDATE. Both shapes look superficially correct in isolation, and the difference shows up under load.
Read more essays and technical writing at anethoth.com — a notebook on databases, distributed systems, biology, and the engineering that holds the world together.