Right, let’s talk about SELECT FOR UPDATE. You’re about to step out of the cozy, read-only world of SELECT and into the slightly more dangerous territory where you need to make sure the data you’re looking at stays that way until you’re done with it. It’s the difference between window shopping and putting an item on hold. The latter comes with responsibility, and if you do it wrong, you’ll block everyone else in the store.

The core problem it solves is a classic race condition. Imagine you’re building a ticket booking system. Your application logic might do this:

  1. SELECT seats_remaining FROM events WHERE id = 123; (Sees 1 seat left)
  2. If seats_remaining > 0, then UPDATE events SET seats_remaining = seats_remaining - 1 WHERE id = 123;

Seems logical, right? But in the microseconds between step 1 and step 2, another user’s request can execute the exact same SELECT, also see 1 seat left, and also proceed to decrement it. Congratulations, you’ve just oversold by one ticket and now have two very angry customers. This is the “check-then-act” race condition, and it’s the reason FOR UPDATE exists.

SELECT FOR UPDATE is your way of telling the database: “I’m not just reading this data; I intend to change it. Please don’t let anyone else change it (or even SELECT FOR UPDATE it) until I’m done with my transaction.” It places a row-level exclusive lock on all the rows returned by your query. This lock prevents other transactions from modifying these rows or from selecting them with FOR UPDATE or FOR SHARE (they can still do a plain SELECT, but we’ll get to that weirdness later).

The Basic Syntax and Transactional Context

This is the most critical thing to remember: SELECT FOR UPDATE is utterly meaningless outside of a transaction. If you execute it with autocommit on (the default in many clients), it locks the rows and then immediately commits, releasing the locks. It’s a expensive no-op. You must wrap it in a transaction block.

BEGIN; -- Start the transaction. This is non-negotiable.

SELECT id, name, balance
FROM accounts
WHERE id = 42
FOR UPDATE; -- The lock is now acquired for the duration of this transaction.

-- Now you can safely do your application logic, check the balance, etc.
-- ... and then update it.

UPDATE accounts SET balance = 100.00 WHERE id = 42;

COMMIT; -- The locks are released, and your change becomes permanent.

If something goes wrong in your application logic, a ROLLBACK will also release the locks. The transaction boundary is your lock’s lease on life.

The Weirdness of READ COMMITTED and Consistent Reads

Here’s a twist that often catches people off guard. By default, PostgreSQL runs in the READ COMMITTED isolation level. This means that within a single transaction, a subsequent SELECT can see data committed by other transactions after your transaction began.

Now, combine this with how FOR UPDATE works. It locks the current version of the row. But what if another transaction changes the row after you lock it but before you update it? In READ COMMITTED mode, your UPDATE statement will actually re-evaluate its WHERE clause. If the row no longer meets the criteria (e.g., its value changed so it wouldn’t be selected by your original query), your update might not affect it. This is… bizarre but correct behavior.

This is why your UPDATE should almost always use the same criteria as your SELECT FOR UPDATE. It’s not just a best practice; it’s a sanity-preserving necessity.

The NOWAIT and SKIP LOCKED Options

Sitting around waiting for a lock is boring, and in a high-throughput system, it can cause debilitating deadlocks or slowdowns. PostgreSQL gives you two tools to be a more polite citizen.

NOWAIT is the impatient option. It tells the database: “Give me these locks right now, or don’t bother at all.” If the rows are already locked by another transaction, your statement will fail immediately with an error instead of blocking.

BEGIN;
SELECT * FROM accounts WHERE id = 42 FOR UPDATE NOWAIT;
-- ERROR:  could not obtain lock on row in relation "accounts"

SKIP LOCKED is the “avoid the line” option. It’s incredibly useful for building robust job queues or any kind of multi-worker system. It tells the database: “Give me the rows that match my query, but skip over any that are already locked.” This allows multiple workers to concurrently grab different jobs from a queue table without stepping on each other’s toes.

-- Worker 1:
BEGIN;
SELECT * FROM job_queue WHERE status = 'pending' FOR UPDATE SKIP LOCKED LIMIT 1;
-- Locks and returns the first available, unlocked job.

-- Worker 2, running concurrently:
BEGIN;
SELECT * FROM job_queue WHERE status = 'pending' FOR UPDATE SKIP LOCKED LIMIT 1;
-- Will not see the job locked by Worker 1. It locks and returns the *next* available job.

Pitfalls and Best Practices

  1. Lock Only What You Need: Be precise in your WHERE clause. Locking SELECT * FROM accounts FOR UPDATE; because you want to update one row is a fantastic way to grind your entire application to a halt. You’re locking every single account. Don’t do that.

  2. Keep It Short: Hold locks for the absolute minimal amount of time possible. Do your expensive application logic before you SELECT FOR UPDATE, not after. The moment you acquire the lock, the clock is ticking on your application’s performance.

  3. The Order of Operations Matters (A Lot): Always acquire locks in a consistent order across your entire application to prevent deadlocks. If Transaction A locks row 1 then tries to lock row 2, while Transaction B locks row 2 then tries to lock row 1, you have a deadlock. PostgreSQL will detect this after a few seconds and kill one of them, but it’s messy. If everyone always locks row 1 before row 2, this can’t happen.

SELECT FOR UPDATE is a sharp tool. It’s essential for correctness in certain situations, but it’s not something you should reach for by default. Use it deliberately, precisely, and for as short a time as possible. Your database, and your fellow developers, will thank you for it.