"Andy Kriger" <akriger@greaterthanone.com> writes:
> After various responses, it looks like SELECT...FOR UPDATE does fit the bill
> if I use it consistently for querying the records I'm interested in.
That's one way. Another approach to think about is to use serializable
mode for all your updating transactions (whether to use it for read-only
xacts is an orthogonal issue). If you do this, you can skip the FOR
UPDATE, but you have to be prepared to retry any such transaction from
the top if it gets a serialization failure. This is essentially an
optimistic locking approach: assume you don't need a lock, retry if
you're wrong. It will win under light row-level contention, since you
avoid all the work of marking rows FOR UPDATE. It can lose under heavy
contention if you have to retry too often, though. See past discussions
in the archives.
regards, tom lane