cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Oilid Adsi" <Oilid.A...@freenet-ag.de>
Subject AW: postgres, idle in transaction
Date Tue, 24 Apr 2007 09:58:12 GMT
Hi Andrus,

i have the same problem as peter because we working at the same project.
So maybe I can give some more hints.

> * Do you have other code outside Cayenne using the same connection pool?
No, we don't have.

> * Are you using external transactions (i.e. is "Container Managed
> Transactions" checkbox is checked)? (I don't think you've mentioned
> this earlier in this thread?)
Again, no. The checkbox isn't checked.

Normally I thought we can do the workaround with committing every query (method commitChanges
in the DataContext) or setting the JDBC-parameter defaultAutoCommit="true". But both of these
workarounds didn't work properly. Sometimes I can see the COMMIT-Statement in the debugging
of the postgres JDBC-driver.

So the consequence is that postgres often holds the transactions open and show "<IDLE>
in transaction".

Here some system specifications which can be helpful:
Java 1.5.0_08
Tomcat 5.5.17
Postgres-JDBC-driver 8.2-504.jdbc3
Cayenne-nodeps 1.2.1
Ashwood 1.1
Velocity 1.4

> Also could you possibly switch the DataSource to DBCP [2] and see if
> that DBCP DataSource does the right thing?
We will do this switch and give feedback.

Why cayenne uses a transaction (BEGIN - COMMIT) for performing a select-query? Is this the
useful way as described on this link:
http://www.ashtech.net/~syntax/blog/archives/56-Hibernate-and-PostgreSQL-Require-Transactions.html

Thanks for help!

Best regards
Oilid

> -----Ursprüngliche Nachricht-----
> Von: Peter Schröder [mailto:Peter.Schroeder@freenet-ag.de]
> Gesendet: Dienstag, 24. April 2007 10:31
> An: user@cayenne.apache.org
> Betreff: AW: postgres, idle in transaction
> 
> hi andrus,
> 
> thank u for the reply, i will investigate on your proposals and give some
> feedback.
> 
> kind regards,
> peter
> 
> -----Ursprüngliche Nachricht-----
> Von: Andrus Adamchik [mailto:andrus@objectstyle.org]
> Gesendet: Dienstag, 24. April 2007 10:24
> An: user@cayenne.apache.org
> Betreff: Re: postgres, idle in transaction
> 
> Hi Peter,
> 
> Thanks for reporting the details of the issue. I just ran a few
> select queries in debugger to confirm that even select queries commit
> the transaction before returning connection to the pool (they do).
> So, just to doublecheck:
> 
> * Do you have other code outside Cayenne using the same connection pool?
> * Are you using external transactions (i.e. is "Container Managed
> Transactions" checkbox is checked)? (I don't think you've mentioned
> this earlier in this thread?)
> 
> But anyways, I think regardless of whether Cayenne leaks (something I
> still can't confirm) or not, I think we should log this as a bug in
> Cayenne connection pool, and ensure that all connections returned to
> the pool are rolled back by the PoolManager. Could you please log a
> bug report in Jira [1].
> 
> Also could you possibly switch the DataSource to DBCP [2] and see if
> that DBCP DataSource does the right thing? This may be an easier/
> cleaner workaround than committing before queries.
> 
> [1] https://issues.apache.org/cayenne/
> [2] http://cayenne.apache.org/doc20/dbcpdatasourcefactory.html
> 
> Thanks
> Andrus
> 
> 
> 
> 
> On Apr 24, 2007, at 9:48 AM, Peter Schröder wrote:
> > hi,
> >
> > we are still experiencing trouble with our postgres db and
> > connections hanging "idle in transaction".
> >
> > we debugged the postgres driver and found out that he starts a
> > transaction on every select-query but does not close it.
> >
> > cayenne does not seem to bother and re-uses these connections for
> > the next query, but other apps have trouble with the transactions,
> > cause they lock the used tables.
> >
> > funny thing is that we cannot reproduce this failure with our test-
> > environment wich has the exact same setup as our live-servers...
> >
> > currently we are doing a commitChanges() after every select-query
> > as a workaround. setting autoCommit to true would have the same
> > effect, but i dont like that idea...
> >
> > kind regards,
> > peter
> >


Mime
View raw message