Обсуждение: 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