Re: Question Regarding Locks

Thanks for the response on this, especially the tip regarding xmin. I've been
spending much of the night and morning comptemplating this issue. I am glad
to have gotten this information, before going any further. Due to the front
end design, I believe I can implement all this within a short period of time.

Thanks again...

On Wednesday 27 October 2004 06:44 pm, Tom Lane saith:
> Terry Lee Tucker <terry@esc1.com> writes:
> > I would like to be able to provide feedback to the user when they
> > select a row for update (using SELECT FOR UPDATE). At present, if the
> > row is being accessed (with SELECT FOR UPDATE) by another user, the
> > application just sits there waiting.
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

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