Re: FKs and deadlocks

Thanks a lot Tom.

One more question: i'm surprised there are so many ExclusiveLocks when displaying pg_lock:

33044 32920 11439 RowExclusiveLock t
6514392 14385 ExclusiveLock t
6495858 11439 ExclusiveLock t
...etc...

I found in the documentation "EXCLUSIVE: This lock mode is not automatically acquired by any PostgreSQL command."

I'm not using any TABLE LOCK or SET TRANSACTION ISOLATION call in the whole database, so where do they come from? I'm accessing the database through ODBC, is that maybe the reason?

Philippe

-----Message d'origine-----
De : Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Envoyé : lundi, 25. octobre 2004 16:16
À : Philippe Lang
Cc : pgsql-general(at)postgresql(dot)org
Objet : Re: [GENERAL] FKs and deadlocks

"Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch> writes:
> I got a deadlock in my database this morning.

There is no deadlock here. The ungranted rows in pg_locks all point to the transaction ID 6489299, which belongs to PID 11346, which is this
one:

> 11346 ?? R 236:43.23 postmaster: jlroubaty groupefpdb 172.17.10.14 UPDATE (postgres)

An UPDATE that churns for hours and hours may well represent a bug in your application (unconstrained join maybe?) but it's not a deadlock.

regards, tom lane