OC4J 10.1.2 Connection Problem Fixed

Another OC4J release, another problem with connection pooling discovered. For the past while we have been wrestling with a fresh bunch of database connection related problems. Initially we were testing on 9.0.4 and it turns out that one of the patches for 9.0.4 introduces a problem whereby the max-connections property in the data-sources.xml is ignored. The patch which introduces the problem is 3871781 looks to be a followup patch for the one we got fixed last year (3674906) - it is not clear under what conditions the problem fixed by 3871781 occurs so we never had a good testcase for it.

Anyway after applying p3674906 we noticed that the max-connections was ignored for two simple tests:

  • a servlet opening a connection from a datasource, selecting from dual, then closing - lots of connections opened, max-connections ignored. The problem was fixed by p4064377 but it did not fix the next problem
  • a more interesting case was also present whereby the max-connections would be ignored if your thread of execution called two EJBs, one with the container-transaction=REQUIRED and the other with container-transaction=SUPPORTS. It doesn’t matter which EJB had which transaction settings. If both were supports or required the problem would not appear. This was fixed in 4160636 for 9.0.4

After some deliberation we decided to take the plunge and move to 10.1.2 since 9.0.4 was turning out to be a hodge-podge of patches. Moving to 10.1.2 also nicely prepares us for the forthcoming 10.1.3 release in the summer. So we decided to test again on 10.1.2 and guess what - the second problem above was present. The behaviour was slightly different in that the number of connections matched max-connections * 2 - it turns out that internally there are two pools at work for each datasource - one pool handles connections that are or have been involved in a transaction recently and the other pool manages connections that have not been transaction aware. I’m not clear on what the fix was (and maybe they just divded max-connections by 2 to divide up the number of connections so that max-connections would be obeyed).

Luckily we got a fix pretty quickly from Oracle support and it can be found if you search for patches that fix bug 4160636 - there are now 9.0.4 and 10.1.2 versions available.

So hopefully that will be case closed and we can stablise on 10.1.2…….

5 Responses to “OC4J 10.1.2 Connection Problem Fixed”

  1. Administrator Says:

    FROM Frances Zhao
    In pre-10.1.3 versions of OC4J the data sources subsystem would create multiple connection pools for the same data source for the following cases. The problem is the cause of the bugs mentioned in this blog:
    - When a connection was used inside a global transaction and outside a global transaction during the same thread of execution (during the execution of a servlet for example.) In this case one connection pool was created for connections used inside global transactions and one connection pool was created for connections used outside of the global transaction.
    - When a connection was retrieved from the data source using the non-default user/password. For example, the use of getConnection() caused one connection pool to be created and getConnection( “user”, “password” ) caused another connection pool to be created. This is especially bad because each user/password combination created another, separate connection pool.
    - Indicating via configuration that a data source’s connections are to be shared caused an additional data source to be created under the covers which would then duplicate all of the connection pool issues described above.

    The problem has been fixed in versions OC4J 10.1.2.0.1 and 10.1.2.0.2. The customer who is using OC4J 10.1.2.0.0 could apply the patch 7478374.

    We also had an one-off 9.0.4.* patch for this issue and it was only available for one particular customer. This one-off patch is not availabe publicly.

  2. obifromsouthlondon Says:

    Hi PJ,

    I read your response on otn forums and was wondering if the patches you mention fixes “java.sql.SQLException: Timed out waiting for an available connection”. The connection pool doesnt seem to be recycling the returned connections. Do you know of a patch?

    thanks

    Obi

  3. Administrator Says:

    Hi Obi,

    Which version of OC4J are you running - I have seen problems on a few different versions for different reasons?

    I have seen serious problems before on all versions were the connection pool becomes exchausted due to leaked resources. Typically this is because the code is not doing the full .close() operation on all resources (resultset,(p)stmt, connection), or else the close() call is not being invoked because an exception is occurring.

    If I remember correctly though there are patches now for the 9.0.4 stream and the 10.1.2 stream which should fix this problem. Let me know the version you are on and I’ll see if I have any more info for you.

    Regards,

    pj.

  4. obifromsouthlondon Says:

    Hi pj,

    Thank you very much.

    version information:

    Oracle App Server 10g 9.0.4.0.0
    JDBC driver: 9.0.1.5.0 (database = oracle 9.2.0.5.0)

    patches:
    4194904 - Timeouts and MDBs hanging
    4233007 - Timeouts and MDBs hanging
    3766407 - Logical database connection limit patch to OC4J
    3466039 - (not sure on this one)

    datasource:

    Application DataSource

    This problem only shows up when I run a large number of queries. my application also uses Oracle Advanced Queues for it’s messaging.

    thankyou once again.

    Obi

  5. obifromsouthlondon Says:

    Hi pj,

    Update. it turns out the oracle support guys hadn’t applied patch 3766407. I have just retested my application against the staging server and everything works fine.

    Thanks for your help