Re: Question on locking - Mailing list pgsql-novice

Hi,

Maybe I should explain my approach more so you can explain to me why it
wouldn't work ;)

For each table add an extra field timestamp (with an optional user field
if you want to know who changed it last)
When the user presses edit the row is checked out - no lock is set
When the user presses save the altered data is passed to the row, along
with the original timestamp and if necessary the users unique ID

    An update trigger will now check the timestamp:

        if it is the same then it can issue a lock and update the table
        and get a new timestamp value
        else someone else has updated this row so control needs to be
        returned to the app. The app can now try to manage this update
        situation. It can get the new data and display that or it could
        send to another trigger the original and changed data to see if
        that was altered by ant previous update if the original data is
        unchanged then an update would be "safe" to do.

However I get nervous of the idea of using a lock to manage multiple
user access to data.

graeme.

Terry Lee Tucker wrote:

>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
>>>>
>>>>
>
>
>