Re: WHY transaction waits for another transaction?

Vilinski Vladimir <vilinski(at)web(dot)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