Re: Question on locking - Mailing list pgsql-novice

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 tablea for update and then process 2 tries to do the same, am I right inassuming that process 2 will wait until the first process completes thetransaction (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 theuser that the row is tentatively locked and to allow them to abort theupdate 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 toselecting 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-6812Cell: 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