AW: postgres, idle in transaction

From: Oilid Adsi (Oilid.Ads..reenet-ag.de)
Date: Tue Apr 24 2007 - 05:58:12 EDT

  • Next message: Andrus Adamchik: "Re: postgres, idle in transaction"

    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