Re: FKs and deadlocks

On Mon, Oct 25, 2004 at 05:56:04PM +0200, Philippe Lang wrote:

> 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?

The bottom two are transaction locks. Those are held while the
transaction is running, and they are a fundamental part of MVCC. They
will only conflict if you try to update a tuple which has already been
updated by that transaction (and I think they are also used in UNIQUE
constraints and FK constraints.)

The RowExclusiveLock I'm not sure about, I think it may be from an ALTER
TABLE or something (assuming it's not on a system catalog).

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La verdad no siempre es bonita, pero el hambre de ella sí"