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…….