Re: Deadlock problem

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

I have written an application in C which tries to drop a trigger and
simultaneously read from a table thats has data related to that trigger.
the whole database comes into a deadlock stage where drop() keeps
waiting on one hand and select() keeps waiting on other hand.

i have the stack trace of the processes that are performing the
operation

For Drop:
process doing drop trigger
(gdb) where
#0 0x420e8a42 in semop () from /lib/i686/libc.so.6
#1 0x0810f028 in IpcSemaphoreLock ()
#2 0x081132c0 in ProcSleep ()
#3 0x081124e5 in WaitOnLock ()
#4 0x08112289 in LockAcquire ()
#5 0x081116d8 in LockRelation ()
#6 0x0807407b in relation_openr ()
#7 0x08074195 in heap_openr ()
#8 0x080bed34 in DropTrigger ()
#9 0x081176c7 in pg_exec_query_string ()
#10 0x0811876a in PostgresMain ()
#11 0x080fde2a in DoBackend ()
#12 0x080fd77d in BackendStartup ()
#13 0x080fc8c1 in ServerLoop ()
#14 0x080fc2db in PostmasterMain ()
#15 0x080da152 in main ()
#16 0x42017499 in __libc_start_main () from /lib/i686/libc.so.6
(gdb)

For Select()
process's doing select
(gdb) where
#0 0x420e8a42 in semop () from /lib/i686/libc.so.6
#1 0x0810f028 in IpcSemaphoreLock ()
#2 0x081132c0 in ProcSleep ()
#3 0x081124e5 in WaitOnLock ()
#4 0x08112289 in LockAcquire ()
#5 0x081116d8 in LockRelation ()
#6 0x0807407b in relation_openr ()
#7 0x08074195 in heap_openr ()
#8 0x0810905a in fireRIRrules ()
#9 0x08108e21 in ApplyRetrieveRule ()
#10 0x08109134 in fireRIRrules ()
#11 0x08108ff9 in fireRIRrules ()
#12 0x08108e21 in ApplyRetrieveRule ()
#13 0x08109134 in fireRIRrules ()
#14 0x08108e21 in ApplyRetrieveRule ()
#15 0x08109134 in fireRIRrules ()
#16 0x081095ec in QueryRewrite ()
#17 0x081173c9 in pg_analyze_and_rewrite ()
#18 0x08117635 in pg_exec_query_string ()
#19 0x0811876a in PostgresMain ()
#20 0x080fde2a in DoBackend ()
#21 0x080fd77d in BackendStartup ()
#22 0x080fc8c1 in ServerLoop ()
---Type <return> to continue, or q <return> to quit---
#23 0x080fc2db in PostmasterMain ()
#24 0x080da152 in main ()
#25 0x42017499 in __libc_start_main () from /lib/i686/libc.so.6
(gdb)

Looks like both try to have a lock and that leads to a deadlock
situation.
internally does drop trigger and select try to lock a particluar table?
how can i get details whats happening internally in postgres for these
operations?

I can provide more info if needed, hope the stack traces are helpful.
Kindly help me, this problem is causinig lot of problem for me.

Thanks in advance
regards
vatsal
_

**************************Disclaimer************************************

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

***************************************************************************

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(dot)avasthi(at)wipro(dot)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