Re: Question on locking - Mailing list pgsql-novice

At present, I'm doing number 1. I'm aquiring the lock when the user presses
the "Edit" button. I can easily see the need to acquire the lock only when
the user presses "Save". I suppose my question is, should I still try and
provide feedback using the lock table I described?

Thanks...

On Friday 05 November 2004 07:19 am, graeme saith:
> When do you establish the lock?
> 1) When the user gets the data for update
> 2) When the user submits the date for update
>
> I'd put a lock on the second situation. If a user is attempting to
> update "out of date" information you can check for that with a trigger
> and inform them, provide them with the new information and possible info
> on who updated it. With a little work it would also be possible to
> refine this to the field level.
>
> graeme.
>
> Terry Lee Tucker wrote:
> >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