Обсуждение: WHY transaction waits for another transaction?
Hallo I'm porting one Oracle Application to Postgres. This Programm uses "long living" Transactions. 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? pg_lock output relation database transacti pid mode granted 251553 251331 18430 AccessShareLock t 251553 251331 18430 RowExclusiveLock t 251487 251331 18430 AccessShareLock t 251487 251331 18430 RowShareLock t 251487 251331 18430 RowExclusiveLock t 251492 251331 18430 AccessShareLock t 251494 251331 18431 AccessShareLock t 251434 251331 18434 AccessShareLock t 251407 251331 18431 AccessShareLock t 251434 251331 18430 AccessShareLock t 251429 251331 18430 AccessShareLock t 251548 251331 18430 AccessShareLock t 251548 251331 18430 RowExclusiveLock t 251575 251331 18430 AccessShareLock t 251458 251331 18430 AccessShareLock t 251524 251331 18434 AccessShareLock t 10293 18434 ExclusiveLock t 251482 251331 18430 AccessShareLock t 251482 251331 18430 RowShareLock t 251492 251331 18431 AccessShareLock t 251577 251331 18431 AccessShareLock t 251477 251331 18431 AccessShareLock t 251567 251331 18431 AccessShareLock t 251417 251331 18431 AccessShareLock t 251492 251331 18434 AccessShareLock t 1247 251331 18431 AccessShareLock t 16839 251331 18438 AccessShareLock t 251494 251331 18430 AccessShareLock t 251494 251331 18430 RowShareLock t 251494 251331 18430 RowExclusiveLock t 251434 251331 18431 AccessShareLock t 251553 251331 18431 AccessShareLock t 251494 251331 18434 AccessShareLock t 251392 251331 18431 AccessShareLock t 251472 251331 18430 AccessShareLock t 251472 251331 18430 RowShareLock t 251526 251331 18431 AccessShareLock t 251577 251331 18430 AccessShareLock t 251482 251331 18431 AccessShareLock t 251482 251331 18431 RowShareLock t 251472 251331 18431 AccessShareLock t 251472 251331 18431 RowShareLock t 251472 251331 18431 RowExclusiveLock t 251487 251331 18434 AccessShareLock t 251553 251331 18434 AccessShareLock t 251407 251331 18430 AccessShareLock t 251407 251331 18430 RowShareLock t 10381 18438 ExclusiveLock t 251487 251331 18431 AccessShareLock t 251487 251331 18431 RowShareLock t 251487 251331 18431 RowExclusiveLock t 251509 251331 18430 AccessShareLock t 251386 251331 18431 AccessShareLock t 251394 251331 18430 AccessShareLock t 10339 18431 ExclusiveLock t 251407 251331 18434 AccessShareLock t 10295 18430 ExclusiveLock t 251422 251331 18430 AccessShareLock t 10295 18431 ShareLock f _________________________________________________________________________ Mit der Gruppen-SMS von WEB.DE FreeMail können Sie eine SMS an alle Freunde gleichzeitig schicken: http://freemail.web.de/features/?mc=021179
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
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/
On Sun, Jun 19, 2005 at 09:58:11AM -0600, Michael Fuhr wrote: > > If possible, it would be best to commit transactions that insert > or update foreign keys as soon as possible. I probably shouldn't have said "best" here, implying that this solution is better than all other possibilities. As Tom Lane pointed out, you could also make the constraints deferrable. If you don't need to know about foreign key violations immediately, then deferring the checks might be a better way to go. -- Michael Fuhr http://www.fuhr.org/~mfuhr/