pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWHY transaction waits for another transaction?
4+ messages / 3 participants
[nested] [flat]
* WHY transaction waits for another transaction?
@ 2005-06-14 11:12 Vilinski Vladimir <vilinski@web.de>
0 siblings, 2 replies; 4+ messages in thread
From: Vilinski Vladimir @ 2005-06-14 11:12 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: WHY transaction waits for another transaction?
@ 2005-06-19 15:05 Tom Lane <tgl@sss.pgh.pa.us>
parent: Vilinski Vladimir <vilinski@web.de>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2005-06-19 15:05 UTC (permalink / raw)
To: Vilinski Vladimir <vilinski@web.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: WHY transaction waits for another transaction?
@ 2005-06-19 15:58 Michael Fuhr <mike@fuhr.org>
parent: Vilinski Vladimir <vilinski@web.de>
1 sibling, 1 reply; 4+ messages in thread
From: Michael Fuhr @ 2005-06-19 15:58 UTC (permalink / raw)
To: Vilinski Vladimir <vilinski@web.de>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: WHY transaction waits for another transaction?
@ 2005-06-19 16:22 Michael Fuhr <mike@fuhr.org>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 4+ messages in thread
From: Michael Fuhr @ 2005-06-19 16:22 UTC (permalink / raw)
To: Vilinski Vladimir <vilinski@web.de>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-06-19 16:22 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-14 11:12 WHY transaction waits for another transaction? Vilinski Vladimir <vilinski@web.de>
2005-06-19 15:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-19 15:58 ` Michael Fuhr <mike@fuhr.org>
2005-06-19 16:22 ` Michael Fuhr <mike@fuhr.org>
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