Thread: Re: [GENERAL] Is Pg 7.0.x's Locking Mechanism BROKEN?

Thanks Fabrice, that will help a lot.

In my applications the conflict was not a direct table conflict e.g.
USER1 locks Table1 record that references Table2 via foreign key with a
cascade update/delete enforced then
USER2 tried to lock Table2 for update on the referenced record - result both
users locked !

Is this the same scenario in your case ?
perhaps a simple test db could used to resolve if this is the issue !


Regards,               Frank.

frank wrote:
> Thanks Fabrice, that will help a lot.
>
> In my applications the conflict was not a direct table conflict e.g.
> USER1 locks Table1 record that references Table2 via foreign key with a
> cascade update/delete enforced then
> USER2 tried to lock Table2 for update on the referenced record - result both
> users locked !
>
> Is this the same scenario in your case ?
> perhaps a simple test db could used to resolve if this is the issue !
   Looks  like  a  deadlock  situation  not seen by the deadlock   detection code.  Unfortunately I'm not able  to
reproduce a   lockup  with  a  simple  test  DB.   Could  you post a simple   (trans1 does  ...,  trans2  does  ...)
sample so  we  coule   reproduce such a lockup?
 


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Jan Wieck wrote:

> frank wrote:
> > Thanks Fabrice, that will help a lot.
> >
> > In my applications the conflict was not a direct table conflict e.g.
> > USER1 locks Table1 record that references Table2 via foreign key with a
> > cascade update/delete enforced then
> > USER2 tried to lock Table2 for update on the referenced record - result both
> > users locked !
> >
> > Is this the same scenario in your case ?
> > perhaps a simple test db could used to resolve if this is the issue !
>
>     Looks  like  a  deadlock  situation  not seen by the deadlock
>     detection code.  Unfortunately I'm not able  to  reproduce  a
>     lockup  with  a  simple  test  DB.   Could  you post a simple
>     (trans1 does  ...,  trans2  does  ...)  sample  so  we  coule
>     reproduce such a lockup?

Hi Jan,

I shall try to reproduce the lockup with  -d2 debug level but, I am not sure this
is the only
lockup problem as it seems far to frequent twice today already and thats in only
4 hours of use :(

Q1. When a system task on a client gets killed how long is it before the database
releases it's record locks ?

Q2. When the Postgres  server is shutdown and re started shouldn't all the record
locks have been removed ?

This situation seems to be getting worse, now I am scared to leave the building.

Regards,               Frank.

Frank and Fabrice,

If you aren't having any luck generating a reproducible example of this
problem, you might try recompiling the backend with LOCK_DEBUG defined
in src/include/config.h --- or just dogmake cleangmake PROFILE=-DLOCK_DEBUG all
This should produce pretty voluminous quantities of info in the
postmaster's stdout/stderr log.  Run the postmaster with -d2 so we
can see the related queries too, and then maybe the log will have enough
info to tell something useful the next time you see it happen.
        regards, tom lane