Greg Smith wrote:
> The biggest downside of [MVCC] is that if you have an old client
> lingering around, things that happened in the database after it started
> can't be cleaned up.
Just to clarify for readers: Idle clients aren't generally an issue.
It's only clients that are idle with an open transaction that tend to
cause issues.
> In 8.4 this situation is improved for some common use cases. In the 8.3
> you're using, an old transaction will block any VACUUM attempt from
> moving past that point in time forever. You have to figure out how to
> get Hibernate to close the transaction it's leaving open for VACUUM to
> work.
Hibernate is pretty well behaved with transaction management. In fact,
it's downright nuts about keeping transactions open for as short a
period of time as possible. It even implements its own row-versioning
based optimistic locking scheme (oplock) rather than relying on holding
a transaction open with row locks in the database.
If you have connections left idle in transaction by a Hibernate-based
Java app, the problem is probably:
1) Unclosed sessions / EntityManagers or explicit transactions in your
own app code. Check particularly for places where the app may open a
transaction without a finally clause on a try block to ensure the
transaction (and the Session / EntityManager) are closed when the block
is exited.
2) Connections being returned to the connection pool with open
transactions ( probably due to #1 ). The connection pool should take
care of that, but reports suggest that some don't.
3) Autocommit being disabled. At least when using Hibernate via JPA,
that'll cause a major mess and would easily explain the issues you're
seeing. Hibernate manages transactions explicitly when required, and
expects autocommit to be off.
3) Your connection pool software doing something crazy like
intentionally keeping idle connections with transactions open. The
connection pool (c3p0 or whatever) that you use is separate from
Hibernate. I'd be surprised to see this except if autocommit was
disabled and the pooling software expected/assumed it'd be enabled.
--
Craig Ringe