Re: Question on locking - Mailing list pgsql-novice

I appreciate your reply. I, for one, am at the point where I have to make a
decision regarding how best to handle locking with the tool that I have, that
is, version 7.4. I raised this question several months ago, and the advice
was to provide a table that would contain feedback that could be given to the
user in a locking conflict. At present, I have a "tool kit" function that
aquires a share lock on a give row when the user enters into "Edit" mode. In
addition, a record is inserted into a table called lock which contains the
user id, the pid, the table name, the oid of the record, and a time stamp. A
unique index on the table name and the oid prevent simultaneous, duplicate
entries. Also, built into the tookit functions, is code that checks for an
existing table entry for the same table and row and reports back to the user,
that "billy bob" has the record locked. Should I use this approach in
conjunction with a much more narrow window in which the lock exists, as Tom's
post indicated?

I am interested any approach that will work best for the user, and ultimately,
for me.


On Friday 05 November 2004 05:50 am, M. Bastin saith:
> I don't agree with Tom's approach because it can only work in a very
> limited number of cases. Imagine 3 persons editing the same record at
> about the same time.
>
> The second one to update has to solve the conflict with the first.
> In cases where this is done manually and not automatically, which
> would be the safest thing to do in most cases I guess(1), you can
> even imagine person 2 calling person 1 for clarifications etc.
>
> In the mean time person 3 wants to update but is also confronted with
> conflicts from person 1's update and starts working on solving them.
> When he's done he gets another conflict again (if the program is well
> made) because in the mean time person 2 has resolved his conflict and
> committed his update.  Person 3 can start over with this newest
> conflict.
>
> At the end of the road there's been a lot of confusion, a huge waste
> of man-hours and a disgruntled employer who has to pay the wages for
> this.
>
> And the risk if the program is not well made is that person 3 will
> only notice the conflict with person 1 but not with person 2!
>
> Locking other (human) users out of a record that is being updated is
> the only fool-proof method I have ever been able to imagine.
> PostgreSQL lacks in this aspect.  If it's not good to have lengthy
> transactions for all sorts of reasons then PostgreSQL needs to come
> up with some command 'LOCK TILL UPDATE' that works outside
> transactions.
>
> my 2 cents,
>
> Marc
>
> (1) imagine the one person has changed the prefix of a phone number
> and the second one changed the extension in the same field.  An
> automatic conflict solver can't cope with this.  A manual conflict
> solver would need to show the original record, the changes made by
> the one user, those made by the other, and if a third user comes in
> his changes as well etc.  This would make for a very confusing and
> cumbersome interface and huge complications for the developer.  The
> only elegant way out is locking users out of records and have them do
> their edits one by one, and not all at the same time.
>
> >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
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 7: don't forget to increase your free space map settings
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

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