Обсуждение: Deadlock problem

Hi, <br />   I am facing a strange problem and thats bugging me for a long time, <br /> I am using postgres version
7.2.1.<br /><br /> I have written an application in C which  tries to drop a trigger and simultaneously read from a
tablethats has data related to that trigger. <br /> the whole database comes into a deadlock stage where drop() keeps
waitingon one hand and select() keeps waiting on other hand. <br /><br /> i have the stack trace of the processes that
areperforming the operation <br /><br /> For Drop: <br /> process doing drop trigger <br /> (gdb) where <br /> #0 
0x420e8a42in semop () from /lib/i686/libc.so.6 <br /> #1  0x0810f028 in IpcSemaphoreLock () <br /> #2  0x081132c0 in
ProcSleep() <br /> #3  0x081124e5 in WaitOnLock () <br /> #4  0x08112289 in LockAcquire () <br /> #5  0x081116d8 in
LockRelation() <br /> #6  0x0807407b in relation_openr () <br /> #7  0x08074195 in heap_openr () <br /> #8  0x080bed34
inDropTrigger () <br /> #9  0x081176c7 in pg_exec_query_string () <br /> #10 0x0811876a in PostgresMain () <br /> #11
0x080fde2ain DoBackend () <br /> #12 0x080fd77d in BackendStartup () <br /> #13 0x080fc8c1 in ServerLoop () <br /> #14
0x080fc2dbin PostmasterMain () <br /> #15 0x080da152 in main () <br /> #16 0x42017499 in __libc_start_main () from
/lib/i686/libc.so.6<br /> (gdb) <br /><br /> For Select() <br /> process's doing select <br /> (gdb) where <br /> #0 
0x420e8a42in semop () from /lib/i686/libc.so.6 <br /> #1  0x0810f028 in IpcSemaphoreLock () <br /> #2  0x081132c0 in
ProcSleep() <br /> #3  0x081124e5 in WaitOnLock () <br /> #4  0x08112289 in LockAcquire () <br /> #5  0x081116d8 in
LockRelation() <br /> #6  0x0807407b in relation_openr () <br /> #7  0x08074195 in heap_openr () <br /> #8  0x0810905a
infireRIRrules () <br /> #9  0x08108e21 in ApplyRetrieveRule () <br /> #10 0x08109134 in fireRIRrules () <br /> #11
0x08108ff9in fireRIRrules () <br /> #12 0x08108e21 in ApplyRetrieveRule () <br /> #13 0x08109134 in fireRIRrules () <br
/>#14 0x08108e21 in ApplyRetrieveRule () <br /> #15 0x08109134 in fireRIRrules () <br /> #16 0x081095ec in QueryRewrite
()<br /> #17 0x081173c9 in pg_analyze_and_rewrite () <br /> #18 0x08117635 in pg_exec_query_string () <br /> #19
0x0811876ain PostgresMain () <br /> #20 0x080fde2a in DoBackend () <br /> #21 0x080fd77d in BackendStartup () <br />
#220x080fc8c1 in ServerLoop () <br /> ---Type <return> to continue, or q <return> to quit--- <br /> #23
0x080fc2dbin PostmasterMain () <br /> #24 0x080da152 in main () <br /> #25 0x42017499 in __libc_start_main () from
/lib/i686/libc.so.6<br /> (gdb) <br /><br /> Looks like both try to have a lock and that leads to a deadlock situation.
<br/> internally does drop  trigger and select try to lock a particluar table? <br /> how can i get details whats
happeninginternally in postgres for these operations? <br /><br /><br /><br /> I can provide more info if needed, hope
thestack traces are helpful. <br /> Kindly help me, this problem is causinig lot of problem for me. <br /><br /> Thanks
inadvance <br /> regards <br /> vatsal <table cellpadding="0" cellspacing="0" width="100%"><tr><td><font
size="2"><b>_</b></font></td></tr></table><table><tr><tdbgcolor="#ffffff"><font
color="#000000"><pre>**************************Disclaimer************************************

Information contained in this E-MAIL being proprietary to Wipro Limited is 
'privileged' and 'confidential' and intended for use only by the individualor entity to which it is addressed. You are
notifiedthat any use, copying 
 
or dissemination of the information contained in the E-MAIL in any manner 
whatsoever is strictly prohibited.

***************************************************************************
</pre></font></td></tr></table>

On 30 Oct 2003, Vatsal Avasthi wrote:

> 
> Hi,
>   I am facing a strange problem and thats bugging me for a long time,
> I am using postgres version 7.2.1.

Is it possible for you to upgrade to 7.2.4 just to make sure it's not a 
problem that was fixed from 7.2.1 to 7.2.4?

Vatsal Avasthi <vatsal.avasthi@wipro.com> writes:
> I am using postgres version 7.2.1.

> Looks like both try to have a lock and that leads to a deadlock
> situation.

It's hard to believe that SELECT and DROP TRIGGER alone could deadlock;
and if they did, you should get a deadlock failure report, not an
indefinite wait.  I think more likely the situation is that some third
client process is holding open a transaction that has some kind of lock
on the table.  DROP TRIGGER would then block waiting for that process
(since it needs to get exclusive lock on the table).  And then, fresh
SELECTs (or anything else) would stack up behind the DROP TRIGGER.  This
is not a deadlock though, just everyone waiting for the original lock
holder to get off his duff and do something.

If you are unconvinced, I would recommend updating to 7.3.4 or 7.4beta
so that you can get more information by looking at the pg_locks system
view.  It's difficult to tell what's happening in 7.2 or older.
        regards, tom lane