Re: Cancelling idle in transaction state

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