FAQ
hi there,

we have some trouble with our postgres database using cayenne. our application is not writing any data to the db, there is only read-statements. nevertheless we have some connections in "idle in transaction" state, wich indicates some transactions not beeing properly closed.

i found a link about hibernate and this issue:
http://www.ashtech.net/~syntax/blog/archives/56-Hibernate-and-PostgreSQL-Require-Transactions.html

is there a similar approach to this for cayenne?

kind regards,
peter

Search Discussions

  • Andrus Adamchik at Apr 3, 2007 at 4:27 pm
    Per last comment from the link you posted:

    "The 'idle' processes are correct behavior; they are the shared
    persistent connections to the database the connection pool leaves
    open to accelerate database access. Only when you have hanging 'idle
    in transaction' threads do you have a problem, as those consume
    connections permanently since they are hung processes and are not
    shared."

    I think that's what you are seeing - the active connection pool. No
    need to worry unless your app starts hanging, or the pool size starts
    unexpectedly going up.

    Andrus

    On Apr 3, 2007, at 2:57 AM, Peter Schröder wrote:

    hi there,

    we have some trouble with our postgres database using cayenne. our
    application is not writing any data to the db, there is only read-
    statements. nevertheless we have some connections in "idle in
    transaction" state, wich indicates some transactions not beeing
    properly closed.

    i found a link about hibernate and this issue:
    http://www.ashtech.net/~syntax/blog/archives/56-Hibernate-and-
    PostgreSQL-Require-Transactions.html

    is there a similar approach to this for cayenne?

    kind regards,
    peter
  • Peter Schröder at Apr 24, 2007 at 6:48 am
    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
  • Andrus Adamchik at Apr 24, 2007 at 8:24 am
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupuser @
categoriescayenne
postedApr 3, '07 at 6:57a
activeApr 24, '07 at 8:24a
posts4
users2
websitecayenne.apache.org

People

Translate

site design / logo © 2022 Grokbase