Thread: Detect Locked Row Without Blocking

Is there a recommended "postgres way" to determine if a certain row is
locked... without blocking?

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."

I've looked at SELECT FOR UPDATE which looks good except for that it
blocks. I don't want my UI to freeze up... I just want to notify the
user that it's locked and move on. Any thoughts?

Thanks.

Joe Lester <joe_lester@sweetwater.com> writes:
> Is there a recommended "postgres way" to determine if a certain row is
> locked... without blocking?

8.1 has a SELECT FOR UPDATE NOWAIT option.  Alternatively, just do a
wait while having a very short statement_timeout.

> 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.

            regards, tom lane

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.