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:[EMAIL PROTECTED] > Gesendet: Dienstag, 24. April 2007 10:31 > An: [email protected] > 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:[EMAIL PROTECTED] > Gesendet: Dienstag, 24. April 2007 10:24 > An: [email protected] > 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 > >
