Re: Question on locking - Mailing list pgsql-novice

Greetings:

I posted a question regarding this issue about 2 weeks ago. See "Question
Regarding Locks" from 10/27/04. Tom Lane resonded with the following:

"To me, this says that you're already off on the wrong foot.

You don't ever want your client application holding locks while a
human user edits text, drinks coffee, goes out to lunch, or whatever.
A better design is to fetch the data without locking it, allow the
user to edit as he sees fit, and then when he clicks "save" you do
something like

        begin;
        select row for update;
        if [ row has not changed since you originally pulled it ] then
                update row with changed values;
                commit;
        else
                abort;
                notify user of conflicts
                let user edit new data to resolve conflicts and try again
        fi

In this design the row lock is only held for milliseconds.

You need to provide some code to let the user merge what he did with the
prior changes, so that he doesn't have to start over from scratch in the
failure case.  What "merge" means requires some business-logic knowledge
so I can't help you there, but this way you are spending your effort on
something that actually helps the user, rather than just tells him he
has to wait.  Performance will be much better too --- long-lasting
transactions are nasty for all sorts of reasons.

BTW, a handy proxy for "row has not changed" is to see if its XMIN
system column is still the same as before.  If so, no transaction has
committed an update to it.  (This may or may not help much, since you're
probably going to end up groveling over all the fields anyway in the
"notify user" part, but it's a cool hack if you can use it.)

                        regards, tom lane"

I have carefully considered his advice and I will be implementing his
suggestions within a couple weeks.

Thanks...

On Friday 05 November 2004 02:36 am, Steve Tucknott saith:
> PostGreSQL 7.4.5
>
> If I have the situation where process 1 has selected record1 from table
> a for update and then process 2 tries to do the same, am I right in
> assuming that process 2 will wait until the first process completes the
> transaction (I've looked at Chapter 12 and this is intimated).
> How can I detect the lock on process 2? I want to be able to tell the
> user that the row is tentatively locked and to allow them to abort the
> update attempt. I can't see a 'SET LOCK MODE TO NOT WAIT' style command,
> so how do I stop process 2 from waiting?
> Is the suggested route to interrogate the system tables prior to
> selecting for update, to see if a lock has been applied?
>
> Normally we wait on locks , so this is not an issue.
>
>
> Regards,
>
> Steve Tucknott
>
> ReTSol Ltd
>
> DDI: 01903 828769

--

 Work: 1-336-372-6812
 Cell: 1-336-363-4719
email: terry@esc1.com