Re: basic pg lock question

"Rick Schumeyer" <rschumeyer(at)ieee(dot)org> writes:
> In the meantime, when User B looks at his web page, there will still
> be an 'edit' link for row 1. I'm pretty sure that I don't want User B
> to try to edit the row, but as far as I understand the default postgres
> locking will not prevent this. When user A selects row 1, do I need to
> manually lock the row to prevent another select?

You can, but it's usually considered bad practice to hold locks for long
enough for people to edit rows. (What if they go off to lunch or
something while your app is holding the row locked?)

Better is to add some logic that checks when the user hits "update" to
see if the row has changed since you read it in. If so, you can either
punt or try to merge the changes. This requires a very short-term lock
(just long enough to avoid a race condition from simultaneous update
attempts) which you can grab with SELECT FOR UPDATE while reading the
current value of the row.

There are discussions of this issue in the mailing list archives.

regards, tom lane