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.Schroede..reenet-ag.de]
> Gesendet: Dienstag, 24. April 2007 10:31
> An: use..ayenne.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:andru..bjectstyle.org]
> Gesendet: Dienstag, 24. April 2007 10:24
> An: use..ayenne.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
> >
This archive was generated by hypermail 2.0.0 : Tue Apr 24 2007 - 05:58:46 EDT