Re: Cancelling idle in transaction state - Mailing list pgsql-hackers

From Robert Haas
Subject Re: Cancelling idle in transaction state
Date
Msg-id 603c8f071001030704g1dff2ffdgba911235f257f45c@mail.gmail.com
Whole thread Raw
In response to Re: Cancelling idle in transaction state  (Simon Riggs <simon@2ndQuadrant.com>)
List pgsql-hackers
On Fri, Jan 1, 2010 at 1:39 PM, Simon Riggs <simon@2ndquadrant.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


pgsql-hackers by date:

Previous
From: Robert Haas
Date:
Subject: Re: psql tab completion for DO blocks
Next
From: Andrew Dunstan
Date:
Subject: Re: PATCH: Add hstore_to_json()