Skip site navigation (1) Skip section navigation (2)

Re: Cancelling idle in transaction state

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>, Joachim Wieland <joe(at)mcknight(dot)de>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Hannu Krosing <hannu(at)2ndquadrant(dot)com>, James Pye <lists(at)jwp(dot)name>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Cancelling idle in transaction state
Date: 2010-01-03 15:04:39
Message-ID: 603c8f071001030704g1dff2ffdgba911235f257f45c@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Fri, Jan 1, 2010 at 1:39 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> Interesting. It's not obvious to me how killing an *idle* session can
>> resolve a deadlock. AIUI a deadlock requires a cycle in the waits-for
>> graph, and an idle transaction is not waiting for a lock acquisition.
>
> In strict theory, yes.
>
> In practice, many lock contention situations are caused by long running
> idle transactions, so having a deadlock detector be able to resolve a
> situation by deciding that an idle xact is actually in some kind of wait
> state would be very useful.
>
> Some people have asked for a idle-in-transaction-timeout. I would be
> more inclined to have a settable time after which an idle-in-transaction
> session that blocks an active lock requestor can be aborted by the
> deadlock detector as a way of resolving a lock wait. Idle-in-transaction
> sessions that don't hold any locks aren't the same kind of annoyance,
> though there may be other reasons to remove them.

I think the biggest issue with idle-in-transaction sessions is MVCC
bloat, which has been considerably mitigated in 8.4 (shout-out to
Alvaro).  It could still be an issue for serializable transactions,
though.  So I'm not 100% sure what is most useful down the road, but
it seems you've solved the immediate problem here, which is good.

...Robert

In response to

pgsql-hackers by date

Next:From: Andrew DunstanDate: 2010-01-03 16:00:51
Subject: Re: PATCH: Add hstore_to_json()
Previous:From: Robert HaasDate: 2010-01-03 15:00:19
Subject: Re: psql tab completion for DO blocks

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group