Thread: Question on locking

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

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

That's the best approach we've come up with in the pgSQL4RB
community.  Check out the pgSQL4RB manual on p.92 for the discussion
and appropriate SQL command.
<http://www.aliacta.com/download/>

As I wrote on this list before myself, I would aslo be very happy
with a 'set lock mode not to wait' or a 'SELECT FOR EDIT' as an
alternative to 'SELECT FOR UPDATE' that would return the user name of
the user locking the record in an error event instead of just wait
for the record to be unlocked.

I'm eager to hear about other people's techniques to handle this problem.

Cheers,

Marc

On Fri, Nov 05, 2004 at 07:36:22AM +0000, Steve Tucknott wrote:

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

Yes.  You can watch this happen if you run two instances of psql,
begin a transaction in each, and do a SELECT FOR UPDATE in each.
The first transaction should return immediately; the second should
block until the first transaction commits or rolls back.

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

You could set statement_timeout to make your queries time out and
assume that somebody else has the record locked if that happens.
Pick a value (milliseconds) that's longer than the SELECT should
take but short enough not to be annoying to the user.

BEGIN;
SET statement_timeout TO 1000;
SELECT * FROM foo WHERE id = 1234 FOR UPDATE;

If you get a timeout then the current transaction will be aborted.

You might want to set the timeout back to its original value (probably
0 unless you've changed it) immediately after the SELECT completes to
avoid timeouts on other queries.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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

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

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

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   

Hi Terry,

I figure that if you have your table to track edited records and use
that in conjunction with Tom's approach you have a pretty good system
with only one major problem:

What happens when a user acquires an editable record but doesn't
release it because he gets disconnected disgraciously while editing
(somebody tripped over the ethernet cable)?

You're probably using your time stamp to help you get out of this
situation but you can use just these techniques to circumvent the
problem:

- at connection a client must release all his locks in case he was
disconnected disgraciously just before and had left some locks active.

- a client must release all his locks when disconnecting graciously.

- a client that encounters a record that is already being edited
should check whether the user who's editing it is still really
connected.

- all user names of connected users have to be unique.


If you don't mind transactions that last for the time a user edits a
record then you can go the way described in the pgSQL4RB manual as
mentioned a few posts before where you don't need any of the tables
and techniques outlined above.  Just do a query to verify the record
is unlocked and then acquire a lock on it with SELECT FOR UPDATE for
the duration of the editing process.  That's all.  (If your
transaction lasts too long, like a couple of hours in my tests, you
will loose it though so you might need some code for this situation.)

Marc



>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
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend

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

>I can easily see the need to acquire the lock only when
>the user presses "Save".

If you do that then you're again in a situation where you can undo
the changes of other users or need to go through tedious manual
conflict checking and need to provide a usable interface for this
(that keeps on working even when you change your database structure
afterwards etc.)

So you definitely have to acquire the lock when you press [edit] or,
instead of locking, track edited rows by adding them into your
special table at that moment.

Marc

Note: after thinking a little about it there's no need to use SELECT
FOR UPDATE with Tom's method.  A regular UPDATE will do (it does its
own locking all by itself for only the time needed).

(Therefore, if Tom's method would be the only recommendable one, the
command SELECT FOR UPDATE would be without purpose.  From this again
I deduce that it originally must have been meant for use in long
lasting transactions.)

Retraction of the previous (I realize SELECT FOR UPDATE can be useful
with Tom's method)

>Note: after thinking a little about it there's no need to use SELECT
>FOR UPDATE with Tom's method.  A regular UPDATE will do (it does its
>own locking all by itself for only the time needed).
>
>(Therefore, if Tom's method would be the only recommendable one, the
>command SELECT FOR UPDATE would be without purpose.  From this again
>I deduce that it originally must have been meant for use in long
>lasting transactions.)

See inserts below:

On Friday 05 November 2004 07:30 am, M. Bastin saith:
> Hi Terry,
>
> I figure that if you have your table to track edited records and use
> that in conjunction with Tom's approach you have a pretty good system
> with only one major problem:
>
> What happens when a user acquires an editable record but doesn't
> release it because he gets disconnected disgraciously while editing
> (somebody tripped over the ethernet cable)?
>
> You're probably using your time stamp to help you get out of this
> situation but you can use just these techniques to circumvent the
> problem:
>
> - at connection a client must release all his locks in case he was
> disconnected disgraciously just before and had left some locks active.

Yes, this is handled.

>
> - a client must release all his locks when disconnecting graciously.

Yes, this is handled.

>
> - a client that encounters a record that is already being edited
> should check whether the user who's editing it is still really
> connected.

No, this has not been done.

>
> - all user names of connected users have to be unique.
>
Already accomplished.

>
> If you don't mind transactions that last for the time a user edits a
> record then you can go the way described in the pgSQL4RB manual as
> mentioned a few posts before where you don't need any of the tables
> and techniques outlined above.  Just do a query to verify the record
> is unlocked and then acquire a lock on it with SELECT FOR UPDATE for
> the duration of the editing process.

This probably a stupid question, but how can I tell if a record is unlocked
from a query? And finally, where is the document pgSQL4RB located?

> That's all.  (If your
> transaction lasts too long, like a couple of hours in my tests, you
> will loose it though so you might need some code for this situation.)
>
> Marc
>

Thank you for all your comments.
__
 Work: 1-336-372-6812
 Cell: 1-336-363-4719
email: terry@esc1.com

Nevermind about the, "how do I tell if it is locked question". I just re-read
Michael Fuhr's post on this subject. I knew it was a stupid question ;o)

Thanks...

On Friday 05 November 2004 08:33 am, Terry Lee Tucker saith:
> See inserts below:
>
> On Friday 05 November 2004 07:30 am, M. Bastin saith:
> > Hi Terry,
> >
> > I figure that if you have your table to track edited records and use
> > that in conjunction with Tom's approach you have a pretty good system
> > with only one major problem:
> >
> > What happens when a user acquires an editable record but doesn't
> > release it because he gets disconnected disgraciously while editing
> > (somebody tripped over the ethernet cable)?
> >
> > You're probably using your time stamp to help you get out of this
> > situation but you can use just these techniques to circumvent the
> > problem:
> >
> > - at connection a client must release all his locks in case he was
> > disconnected disgraciously just before and had left some locks active.
>
> Yes, this is handled.
>
> > - a client must release all his locks when disconnecting graciously.
>
> Yes, this is handled.
>
> > - a client that encounters a record that is already being edited
> > should check whether the user who's editing it is still really
> > connected.
>
> No, this has not been done.
>
> > - all user names of connected users have to be unique.
>
> Already accomplished.
>
> > If you don't mind transactions that last for the time a user edits a
> > record then you can go the way described in the pgSQL4RB manual as
> > mentioned a few posts before where you don't need any of the tables
> > and techniques outlined above.  Just do a query to verify the record
> > is unlocked and then acquire a lock on it with SELECT FOR UPDATE for
> > the duration of the editing process.
>
> This probably a stupid question, but how can I tell if a record is unlocked
> from a query? And finally, where is the document pgSQL4RB located?
>
> > That's all.  (If your
> > transaction lasts too long, like a couple of hours in my tests, you
> > will loose it though so you might need some code for this situation.)
> >
> > Marc
>
> Thank you for all your comments.
> __
>  Work: 1-336-372-6812
>  Cell: 1-336-363-4719
> email: terry@esc1.com

--
Quote: 50
"Being a role model is the most powerful form of educating. Youngsters
 need good role models more than they need critics. It is one of a
 parent's greatest responsibilities and opportunities. Too often fathers
 neglect it because they get so caught up in making living they forget to
 make a life."

 --John Wooden, former UCLA Basketball Coach

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

Steve Tucknott <steve@retsol.co.uk> writes:
> 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?

I see the thread moved off in a different direction, but to answer your
original question: what I'd probably do is set STATEMENT_TIMEOUT to the
maximum length of time I was willing to wait for the lock.  (This
assumes of course that the time spent to find the row is reasonably
predictable, since you have to allow for that too.)

            regards, tom lane

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