Re: Question on locking

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(at)esc1(dot)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
>>
>>
>
>
>