From: Tom Lane <tgl@sss.pgh.pa.us>
To: Vilinski Vladimir <vilinski@web.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: WHY transaction waits for another transaction?
Date: Sun, 19 Jun 2005 11:05:22 -0400
Message-ID: <17562.1119193522@sss.pgh.pa.us> (raw)
In-Reply-To: <141218891@web.de>
References: <141218891@web.de>
Vilinski Vladimir <vilinski@web.de> writes:
> 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?
Presumably it is blocked on a row lock that the first transaction
holds. There isn't any really good way to find out exactly which
row is involved in existing releases (8.1 will be better). If you're
desperate you could go in with a debugger, but it's probably easier
to reason it out, because there are not that many possibilities.
One way to get this would be if the two transactions tried to update
the same rows in different orders. But I think that would be a deadlock
condition in Oracle too, so if your code worked on Oracle that's
probably not it. The more likely suspect is a foreign key conflict.
Are both transactions inserting/updating rows that could reference
the same row(s) in a master table? PG takes a row lock on the
referenced row (to make sure it won't disappear before commit) and
so you can get deadlocks in the master table.
PG 8.1 will have sharable row locks and use those for foreign key
interlocks, so this problem should essentially disappear in 8.1.
At the moment, the best workaround I know of is to make the foreign key
checks all deferred, so that they're not checked until the transaction
is about to commit. This is not bulletproof, but because it
considerably reduces the time window for a conflict, it may do as a
workaround until 8.1 is ready.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, vilinski@web.de
Subject: Re: WHY transaction waits for another transaction?
In-Reply-To: <17562.1119193522@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox