Grokbase › Groups › Cayenne › user › April 2007
FAQ
hi andrus,

thank you for your reply, but we are experiencing "idle in transaction" connections that are hung and have to be killed manually. there are a lot of other connections in state "idle" which we recognized as the connection-pool. so we are already worrying ;-).
we have a lot of background/scheduled tasks in our app, so we dont really know where the problem occurs. currently we are searching our logs to find something usefull.

kind regards,
peter

-----Ursprüngliche Nachricht-----
Von: Andrus Adamchik
Gesendet: Dienstag, 3. April 2007 18:27
An: user@cayenne.apache.org
Betreff: Re: postgres, idle in transaction

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

Search Discussions

  • Tore Halset at Apr 4, 2007 at 8:30 am ⇧
    Hello.

    I am using cayenne with PostgreSQL, but have not seen this problem.
    Are you using a new PostgreSQL release and and a jdbc-driver matching
    that version? Search http://jdbc.postgresql.org/changes.html has one
    bugfix related to this from back in 2004.

    In a (old) non-cayenne environment I have had some problems with
    connections not properly closed after use. I tracked down that
    problem by saving the stacktrace (new Exception()) and a timestamp
    every time a Connection is required. That way I had a register over
    all Connections and was able to find the ones that was not closed
    after use.

    Could you try to create a small example that reproduce this problem?

    - Tore.
    On Apr 4, 2007, at 8:31, Peter Schröder wrote:

    thank you for your reply, but we are experiencing "idle in
    transaction" connections that are hung and have to be killed
    manually. there are a lot of other connections in state "idle"
    which we recognized as the connection-pool. so we are already
    worrying ;-).
    we have a lot of background/scheduled tasks in our app, so we dont
    really know where the problem occurs. currently we are searching
    our logs to find something usefull.
  • Peter Schröder at Apr 24, 2007 at 8:31 am ⇧
    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
    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
  • Oilid Adsi at Apr 24, 2007 at 9:58 am ⇧
    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
    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
    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
  • Andrus Adamchik at Apr 24, 2007 at 10:22 am ⇧

    On Apr 24, 2007, at 12:58 PM, Oilid Adsi wrote:

    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.
    I am sure they did not. I was surprised when Peter reported that
    'commitChanges' worked as a workaround. This shouldn't make any
    difference.
    Postgres-JDBC-driver 8.2-504.jdbc3
    Worth checking a different version as well (I've see strangest cross-
    version issues with the driver in the past).

    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.
    Please do. This will be an indication of whether we need to fix our
    connection pool or not.

    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
    Main reason is consistency. There can be select queries that are not
    just select (e.g. stored procedure queries that both select and
    update). So Cayenne handles all queries the same way, instead of
    trying to analyze each query coming down the pipe.

    Andrus
  • Oilid Adsi at Apr 24, 2007 at 11:12 am ⇧
    Hi Andrus,

    i got an useful hint that this "bug" is based on the jdbc-driver from
    postgres.

    I also found this info:
    http://jdbc.postgresql.org/changes.html#version_dev302

    "Track transaction status and only issue a BEGIN command on the first
    statement executed, not immediately after the previous commit or
    rollback. This should help the long standing, but recently very
    unpopular "idle in transaction" behavior. (jurka)"

    Greetings
    Oilid
  • Oilid Adsi at Apr 24, 2007 at 2:04 pm ⇧
    Hi again,
    -----Ursprüngliche Nachricht-----
    Von: Andrus Adamchik
    Gesendet: Dienstag, 24. April 2007 12:23
    An: user@cayenne.apache.org
    Betreff: Re: postgres, idle in transaction

    On Apr 24, 2007, at 12:58 PM, Oilid Adsi wrote:

    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.
    I am sure they did not. I was surprised when Peter reported that
    'commitChanges' worked as a workaround. This shouldn't make any
    difference.
    Postgres-JDBC-driver 8.2-504.jdbc3
    Worth checking a different version as well (I've see strangest cross-
    version issues with the driver in the past).
    The postgres mailinglist is sure that this problem/bug was fixed (see attachment). I also switched to the newest jdbc-version: PostgreSQL 8.3devel JDBC3 with SSL (build 600).
    << Re JDBC idle in transaction problem.txt >>
    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.
    Please do. This will be an indication of whether we need to fix our
    connection pool or not.
    Same behaviour and still the same problem with DBCP DataSource ;-(
    Sometimes the transaction will be committed sometimes not:
    << postgres_debug_transaction.txt >>
    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
    Main reason is consistency. There can be select queries that are not
    just select (e.g. stored procedure queries that both select and
    update). So Cayenne handles all queries the same way, instead of
    trying to analyze each query coming down the pipe.

    Andrus
  • Andrus Adamchik at Apr 24, 2007 at 2:17 pm ⇧
    Interesting... Looking at your log I see this:

    15:33:08.267 (2) FE=> Parse(stmt=S_1,query="BEGIN",oids={})
    .....
    15:33:08.268 (2) FE=> Parse(stmt=null,query="SELECT ....
    ....
    15:33:28.461 (2) FE=> Parse(stmt=S_2,query="COMMIT",oids={})


    So transaction is clearly committed. But of course connection is not
    closed. It is returned to the pool instead. Still looks like a driver
    bug to me.

    Andrus


    On Apr 24, 2007, at 5:03 PM, Oilid Adsi wrote:

    Hi again,
    -----Ursprüngliche Nachricht-----
    Von: Andrus Adamchik
    Gesendet: Dienstag, 24. April 2007 12:23
    An: user@cayenne.apache.org
    Betreff: Re: postgres, idle in transaction

    On Apr 24, 2007, at 12:58 PM, Oilid Adsi wrote:

    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.
    I am sure they did not. I was surprised when Peter reported that
    'commitChanges' worked as a workaround. This shouldn't make any
    difference.
    Postgres-JDBC-driver 8.2-504.jdbc3
    Worth checking a different version as well (I've see strangest cross-
    version issues with the driver in the past).
    The postgres mailinglist is sure that this problem/bug was fixed
    (see attachment). I also switched to the newest jdbc-version:
    PostgreSQL 8.3devel JDBC3 with SSL (build 600).
    << Re JDBC idle in transaction problem.txt >>
    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.
    Please do. This will be an indication of whether we need to fix our
    connection pool or not.
    Same behaviour and still the same problem with DBCP DataSource ;-(
    Sometimes the transaction will be committed sometimes not:
    << postgres_debug_transaction.txt >>
    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
    Main reason is consistency. There can be select queries that are not
    just select (e.g. stored procedure queries that both select and
    update). So Cayenne handles all queries the same way, instead of
    trying to analyze each query coming down the pipe.

    Andrus
    <Re JDBC idle in transaction problem.txt>
    <postgres_debug_transaction.txt>
  • Andrus Adamchik at Apr 24, 2007 at 2:21 pm ⇧

    On Apr 24, 2007, at 5:17 PM, Andrus Adamchik wrote:

    Interesting... Looking at your log I see this:

    15:33:08.267 (2) FE=> Parse(stmt=S_1,query="BEGIN",oids={})
    .....
    15:33:08.268 (2) FE=> Parse(stmt=null,query="SELECT ....
    ....
    15:33:28.461 (2) FE=> Parse(stmt=S_2,query="COMMIT",oids={})


    So transaction is clearly committed. But of course connection is
    not closed. It is returned to the pool instead. Still looks like a
    driver bug to me.

    Andrus
    Sorry, you said "sometimes". Just noticed that the first case indeed
    doesn't commit. Just thought of another potential source of leaks -
    Cayenne iterated queries. Are you using any of those?

    http://cayenne.apache.org/doc/iterating-through-data-rows.html

    Andrus
  • Oilid Adsi at Apr 25, 2007 at 7:45 am ⇧
    Hi Andrus,

    Thanks for the patch!

    I have deployed the patch yesterday and switched to cayenne data source.

    But unfortunately there is no improvement as you supposed before.

    Is there no way to force this "stupid" COMMIT in a transaction?
    Do you have another idea?

    Kind regards
    Oilid
    -----Ursprüngliche Nachricht-----
    Von: Andrus Adamchik
    Gesendet: Dienstag, 24. April 2007 17:20
    An: user@cayenne.apache.org
    Betreff: Re: postgres, idle in transaction

    I just uploaded a patched 1.2 build with Cayenne connection pool
    rolling back transactions before returning them to the pool. Can you
    try it out (of course resetting DBCP data source back to Cayenne):

    http://people.apache.org/~aadamchik/patched/cayenne-nodeps-1.2.3-dev.jar

    I don't have too much hope this will fix it... but still worth a try.

    Andrus

    On Apr 24, 2007, at 5:28 PM, Oilid Adsi wrote:

    -----Ursprüngliche Nachricht-----
    Von: Andrus Adamchik
    Gesendet: Dienstag, 24. April 2007 16:21
    An: user@cayenne.apache.org
    Betreff: Re: AW: postgres, idle in transaction

    On Apr 24, 2007, at 5:17 PM, Andrus Adamchik wrote:

    Interesting... Looking at your log I see this:

    15:33:08.267 (2) FE=> Parse(stmt=S_1,query="BEGIN",oids={})
    .....
    15:33:08.268 (2) FE=> Parse(stmt=null,query="SELECT ....
    ....
    15:33:28.461 (2) FE=> Parse(stmt=S_2,query="COMMIT",oids={})


    So transaction is clearly committed. But of course connection is
    not closed. It is returned to the pool instead. Still looks like a
    driver bug to me.

    Andrus
    Sorry, you said "sometimes". Just noticed that the first case indeed
    doesn't commit. Just thought of another potential source of leaks -
    Cayenne iterated queries. Are you using any of those?
    No, we are not using performIteratedQuery() with postgres.

    Is there another possibility to debug this problem or verify if
    this is maybe a cayenne or jdbc-postgres bug?

    Oilid
  • Tore Halset at Apr 25, 2007 at 8:09 am ⇧
    Hello.

    I am reading this thread and just want to say that I am using cayenne
    2.0.2, postgresql-8.2-504.jdbc3.jar and postgresql-8.2.3. Cayenne
    uses jndi to get connections from a jboss-4.0.3 connection pooling. I
    do not see the "postgres, idle in transaction" problem.

    - Tore.
    On Apr 25, 2007, at 9:45 , Oilid Adsi wrote:

    Hi Andrus,

    Thanks for the patch!

    I have deployed the patch yesterday and switched to cayenne data
    source.

    But unfortunately there is no improvement as you supposed before.

    Is there no way to force this "stupid" COMMIT in a transaction?
    Do you have another idea?

    Kind regards
    Oilid
    -----Ursprüngliche Nachricht-----
    Von: Andrus Adamchik
    Gesendet: Dienstag, 24. April 2007 17:20
    An: user@cayenne.apache.org
    Betreff: Re: postgres, idle in transaction

    I just uploaded a patched 1.2 build with Cayenne connection pool
    rolling back transactions before returning them to the pool. Can you
    try it out (of course resetting DBCP data source back to Cayenne):

    http://people.apache.org/~aadamchik/patched/cayenne-nodeps-1.2.3-
    dev.jar

    I don't have too much hope this will fix it... but still worth a try.

    Andrus

    On Apr 24, 2007, at 5:28 PM, Oilid Adsi wrote:

    -----Ursprüngliche Nachricht-----
    Von: Andrus Adamchik
    Gesendet: Dienstag, 24. April 2007 16:21
    An: user@cayenne.apache.org
    Betreff: Re: AW: postgres, idle in transaction

    On Apr 24, 2007, at 5:17 PM, Andrus Adamchik wrote:

    Interesting... Looking at your log I see this:

    15:33:08.267 (2) FE=> Parse(stmt=S_1,query="BEGIN",oids={})
    .....
    15:33:08.268 (2) FE=> Parse(stmt=null,query="SELECT ....
    ....
    15:33:28.461 (2) FE=> Parse(stmt=S_2,query="COMMIT",oids={})


    So transaction is clearly committed. But of course connection is
    not closed. It is returned to the pool instead. Still looks like a
    driver bug to me.

    Andrus
    Sorry, you said "sometimes". Just noticed that the first case
    indeed
    doesn't commit. Just thought of another potential source of leaks -
    Cayenne iterated queries. Are you using any of those?
    No, we are not using performIteratedQuery() with postgres.

    Is there another possibility to debug this problem or verify if
    this is maybe a cayenne or jdbc-postgres bug?

    Oilid
  • Peter Schröder at Apr 27, 2007 at 7:34 am ⇧
    hi tore,

    thank you for the hint, we are thankfull for all the help.

    kind regards
    peter

    -----Ursprüngliche Nachricht-----
    Von: Tore Halset
    Gesendet: Freitag, 27. April 2007 08:52
    An: user@cayenne.apache.org
    Betreff: Re: postgres, idle in transaction

    Hello.

    Creating your own DataSource (wrapper) as suggested by Andrus should
    give you good control over things that are happening and perhaps what
    to do/fix.

    I had a problem in some non-cayenne based code that did not close the
    jdbc Connection and it was hard to know where the problem was until I
    started store a "new Exception()" and a timestamp every time I
    created a new Connection. I then had the possibility to log
    stacktraces to places where the code created Connections that was not
    closed. Perhaps you could do something like that to track things in
    your DataSource wrapper?

    - Tore.
    On Apr 27, 2007, at 8:31 , Peter Schröder wrote:

    hi andrus,

    i know that this issue sucks, but we are forced to solve it.

    we did some further investigation of our live-environment and
    noticed that these idle connections appear only while our
    application is plugged into our loadbalancer. first we thought that
    this could be an concurrency or load issue, but regarding our logs,
    the load is close to zero...

    i wondered if there is a way to get the current postgres-connection
    from cayenne. i dont know how jdbc-connections are wrapped bye
    cayenne and i dont want to search through the code, even though i
    might learn something ;-)

    currently i am wondering about why the transaction is not properly
    closed and i think that the postgres people said, that this issue
    had been fixed decades ago, so i guess thats right. is there a way
    that the connection is not properly returned to the connection-pool
    after a request and because of that the transaction will not put to
    commit?

    kind regards
    peter


    -----Ursprüngliche Nachricht-----
    Von: Andrus Adamchik
    Gesendet: Donnerstag, 26. April 2007 13:47
    An: user@cayenne.apache.org
    Betreff: Re: AW: AW: postgres, idle in transaction

    On Apr 26, 2007, at 1:14 PM, Oilid Adsi wrote:

    Or maybe by adding an explicit "COMMIT" after every SELECT-
    Statement when the connection will be returned to the pool? Can
    this implemented as an optional function to the Cayenne framework?
    Per your comment the patch doing "rollback" didn't help, why do you
    think "commit" should help? Commit and rollback both terminate a
    transaction.

    http://objectstyle.org/cayenne/lists/cayenne-user/2007/04/0159.html

    But if you want to explore various scenarios, Cayenne allows you to
    plug a custom implementation of DataSource via the DataSourceFactory
    mechanism. So you don't need to patch Cayenne, but instead you can
    write a custom DataSource (based on Cayenne or DBCP library, or do it
    from scratch) to do any pre- or post- processing of connections
    checked in or out of the pool.

    If you get to the cause of it, please share. Unfortunately I am out
    of ideas regarding this issue.

    Andrus

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupuser @
categoriescayenne
postedApr 4, '07 at 6:31a
activeApr 27, '07 at 7:34a
posts12
users4
websitecayenne.apache.org

4 users in discussion

Oilid Adsi: 4 posts Andrus Adamchik: 3 posts Peter Schröder: 3 posts Tore Halset: 2 posts

Content

People

Support

Translate

site design / logo © 2022 Grokbase