Question Regarding Locks

Greetings:

I am working on converting a transportation application from a Progress
database to PostgreSQL. This application will be hammered by about 75 users
at any given time. Also, depending on the operation, there are many record
updates that occur at the trigger level. I would like to be able to provide
feedback to the user when they select a row for update (using SELECT FOR
UPDATE). At present, if the row is being accessed (with SELECT FOR UPDATE) by
another user, the application just sits there waiting. I spent some time
looking at pg_locks hoping to be able to gain information from that table so
as to programically return something to the user. The stuff I read didn't
look promising. The only thing I've been able to come up with is having my
own "lock" table and inserting a row into it containing the table name, the
pid, the oid of the row, the user id, and a time stamp. Of course, I'm
searching lock table entries before selecting for update and reporting a lock
situation to the user if I find anything. I'm just wondering if I can use
some system table or combination of tables to provide the feed back I need.
Does anyone have any ideas about how to accomplish this? I searched the FAQ
and didn't find anything.

PostgreSQL 7.3.6-RH on i386-redhat-linux-gnu, compiled by GCC
i386-redhat-linux-gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-39)

Thanks...
--
Quote: 32
"The world we have created is a product of our thinking. It cannot be
changed without changing our thinking."

--Albert Einstein

Work: 1-336-372-6812
Cell: 1-336-363-4719
email: terry(at)esc1(dot)com

Terry Lee Tucker <terry(at)esc1(dot)com> writes:
> I would like to be able to provide feedback to the user when they
> select a row for update (using SELECT FOR UPDATE). At present, if the
> row is being accessed (with SELECT FOR UPDATE) by another user, the
> application just sits there waiting.

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

Thanks for the response on this, especially the tip regarding xmin. I've been
spending much of the night and morning comptemplating this issue. I am glad
to have gotten this information, before going any further. Due to the front
end design, I believe I can implement all this within a short period of time.

Thanks again...

On Wednesday 27 October 2004 06:44 pm, Tom Lane saith:
> Terry Lee Tucker <terry(at)esc1(dot)com> writes:
> > I would like to be able to provide feedback to the user when they
> > select a row for update (using SELECT FOR UPDATE). At present, if the
> > row is being accessed (with SELECT FOR UPDATE) by another user, the
> > application just sits there waiting.
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

--
Work: 1-336-372-6812
Cell: 1-336-363-4719
email: terry(at)esc1(dot)com

Tom,

thanks ! You are even helping lurkers like me that haven't
asked anything :-)

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

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

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

Just so that I am not getting this wrong:

> BTW, a handy proxy for "row has not changed" is to see if its XMIN
> system column is still the same as before.
Considering that my business objects remember XMIN from when
they first got the row would the following sequence make sure
I am in good shape ?

begin;
select ... for update;
update ... set ... where
my_pk=<my_pk_value>
AND
xmin=<the_old_xmin>

This should either update 1 row in which case I can commit or
zero rows in which case I need to rollback and handle the merge
conflict. The reasoning would be that the condition
my_pk=my_pk_value would select the row I am interested in
while xmin=the_old_xmin would ensure that row hasn't been
modified.

Am I right or is there a flaw in my thinking ?

Thanks,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> writes:
> Just so that I am not getting this wrong:
>> BTW, a handy proxy for "row has not changed" is to see if its XMIN
>> system column is still the same as before.
> Considering that my business objects remember XMIN from when
> they first got the row would the following sequence make sure
> I am in good shape ?

> begin;
> select ... for update;
> update ... set ... where
> my_pk=<my_pk_value>
> AND
> xmin=<the_old_xmin>

> This should either update 1 row in which case I can commit or
> zero rows in which case I need to rollback and handle the merge
> conflict. The reasoning would be that the condition
> my_pk=my_pk_value would select the row I am interested in
> while xmin=the_old_xmin would ensure that row hasn't been
> modified.

> Am I right or is there a flaw in my thinking ?

I think you can skip the SELECT FOR UPDATE altogether if you do it that
way. Otherwise it looks fine.

regards, tom lane

> > begin;
> > select ... for update;
> > update ... set ... where
> > my_pk=<my_pk_value>
> > AND
> > xmin=<the_old_xmin>
>
> I think you can skip the SELECT FOR UPDATE altogether if you do it that
> way. Otherwise it looks fine.
Except that there will be other clients accessing those rows,
too, of which I cannot be sure that they employ the same (or
even adequate) locking procedures. So I should still lock the
row for good measure, right ?

The docs say that XMIN is the transaction ID of the *inserting*
transaction for this row version. IOW updates will change XMIN.
Will XMIN also be changed by a *deleting* transaction ? I guess
it depends on how deletion is handled: Either the *unchanged*
row version is marked as deleted (hence XMIN would not change)
OR a new row version is created and marked deleted (which would
indeed change xmin).

IOW, can I also detect my row being *deleted* from under me by
another transation by way of checking XMIN ? Else I would
likely need to check XMAX, too.

Thanks for your help,

Karsten Hilbert, MD
http://www.gnumed.org
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

On Fri, Oct 29, 2004 at 09:22:42AM +0200, Karsten Hilbert wrote:
> The docs say that XMIN is the transaction ID of the *inserting*
> transaction for this row version. IOW updates will change XMIN.
> Will XMIN also be changed by a *deleting* transaction ? I guess
> it depends on how deletion is handled: Either the *unchanged*
> row version is marked as deleted (hence XMIN would not change)
> OR a new row version is created and marked deleted (which would
> indeed change xmin).

You need to look at it in conjunction with XMAX. A newly insert row has
XMIN set and XMAX null. When a row is updated the XMAX of the old row
is set and a new row is created with an XMIN. When you delete a row it
just sets the XMAX.

> IOW, can I also detect my row being *deleted* from under me by
> another transation by way of checking XMIN ? Else I would
> likely need to check XMAX, too.

Easy, look for it. If you can't find it, it got deleted...

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Martijn,

thanks for your clarification.

> You need to look at it (XMIN) in conjunction with XMAX. A newly insert row has
> XMIN set and XMAX null. When a row is updated the XMAX of the old row
> is set and a new row is created with an XMIN. When you delete a row it
> just sets the XMAX.
But, as you say below, it also "disappears"... :-)

> > IOW, can I also detect my row being *deleted* from under me by
> > another transaction by way of checking XMIN ? Else I would
> > likely need to check XMAX, too.
> Easy, look for it. If you can't find it, it got deleted...
Doh, of course you are right. I was thinking of doing this:

(assume a row with pk set to 1)

select xmin, ... from ... where pk=1;

... remember xmin as <old_xmin> ...
... do some time-intensive application work ...

select 1 from ... where pk=1 and xmin=<old_xmin> for update;

Now:
- if one row (eg. the "1") is returned then I locked my row
and can happily update it and commit
- if more than one row is returned I am in deep trouble and
I better consider shutting down both my application and the
database for serious investigation - rollback is in order
- if zero rows are returned my row was either deleted (eg.
nothing found for pk=1) or it was updated by someone else
(eg. xmin != <old_xmin>),
from this point on I am entering the slow path anyways (eg.
notifying the user, merge handling, delete detection etc.),
so rollback is in order, too

IOW I should be fine looking at xmin only for *detecting* a
concurrency conflict - be it concurrent updates or the row
having been deleted.

Am I getting this right ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346