Date: Sun, 19 Jun 2005 09:58:11 -0600
From: Michael Fuhr <mike@fuhr.org>
To: Vilinski Vladimir <vilinski@web.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: WHY transaction waits for another transaction?
Message-ID: <20050619155811.GA18897@winnie.fuhr.org>
References: <141218891@web.de>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <141218891@web.de>

On Tue, Jun 14, 2005 at 01:12:13PM +0200, Vilinski Vladimir wrote:
> 
> During the execution of transaction Nr:10295 (PID:18430) one new transaction with
> Nr:10339 (PID:18431) starts, that writes one record into the table. But this new
> transaction never stops, because it tries to set one ShareLock to its
> parrent transaction Nr:10295. 
> 
> My problem is, how can i found out - WHY the second transaction waits
> for end of first transaction? 
> Is there a tool for analyzing such deadlocks?

A note on terminology: what you describe doesn't sound like deadlock,
it sounds like blocking.  Deadlock occurs, for example, when two
transactions each hold a lock that the other is waiting for;
PostgreSQL should detect this situation and raise an error in one
of the transactions.  What you describe sounds more like one
transaction blocking because of a lock held by another transaction.

You say that process 18431 "writes one record into the table," so
I assume it's doing an INSERT.  That should acquire a RowExclusiveLock
on the table being inserted into; based on your pg_locks output
that would be either relation 251472 or 251487.  Does that table
have foreign key references to other tables?  I see several instances
of RowShareLock, which is acquired by SELECT FOR UPDATE, which is
done by foreign key constraint checks (although that's not the only
possibility -- you might have executed SELECT FOR UPDATE yourself).
In released versions of PostgreSQL, if two transactions insert or
update records that refer to the same foreign key, then one transaction
will block until the other completes, due to that SELECT FOR UPDATE
(which prevents the foreign key from changing before the referring
transaction completes; this blocking situation will be fixed in the
next release).

I'm guessing that foreign key references are causing the blocking.
If possible, it would be best to commit transactions that insert
or update foreign keys as soon as possible.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/