Re: Cancelling idle in transaction state

On Fri, 2010-01-01 at 10:15 -0800, Robert Haas wrote:
> >> Hmm. I don't think I can get a serialization failure, deadlock, or
> >> out
> >> of memory error while my session is idle.
> >
> > Agreed. As a point of note, now that we can cancel idle transactions
> > there isn't any future blocker from making serialization failures or
> > deadlocks cancel such transactions... Other RDBMS have deadlock
> > detectors that can pick any session to resolve, not just the one doing
> > the deadlock checking.
>
> 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.

--
Simon Riggs www.2ndQuadrant.com