Re: Detect Locked Row Without Blocking

I see...

For my purposes, I'd still rather notify the user up-front that the
record is "in modify" (kind of like FileMaker does)... even though now
I understand that a row lock is not the right mechanism for that.

Is there a "best-practice" for this approach?

What about using a field to flag the record as "in modify"? But I guess
then you'd have to protect against two different users
selecting/updating the field at roughly the same time, each user then
thinking that he has gained modify privileges for that record. I'm not
sure a row lock would help any in this circumstance... and I don't want
to resort to table locks for performance reasons.

On Nov 8, 2005, at 10:14 AM, Tom Lane wrote:

> Joe Lester <joe_lester@sweetwater.com> writes:
>> In my custom postgres client app I'd like to be able to determine if
>> another user is "modifying" a given record. If so, I would present a
>> dialog to the user such as "Record Locked. Sam Smith is already
>> modifying this record. Try again later."
>
> However, I think the question is moot because it's predicated on a
> terrible underlying approach.  You should NEVER design a DB app to hold
> a lock while some user is editing a record (and answering the phone,
> going out to lunch, etc).
> Fetch the data and then let the user edit
> it while you are not in a transaction.  When he clicks UPDATE, do
>     BEGIN;
>     SELECT the row FOR UPDATE;
>     check for any changes since you fetched the data originally
>     if none, UPDATE and commit
>     else rollback and tell user about it
>
> If you do see conflicting changes, then you have enough info to resolve
> the conflicts or abandon the update.