英文:
Java - Orcale could not open JDBC connection for transaction
问题
我正在处理一个非常庞大但实现不佳的项目，我们在数据库方面遇到了严重的性能问题。
我们正在使用Oracle、Exadata等等。
应用服务器：Tomcat
驱动程序：ojdbc6
以下是相关配置：
<property name="connectionCachingEnabled" value="true" />
<property name="connectionCacheProperties">
<value>
MinLimit:180
MaxLimit:200
InitialLimit:50
ConnectionWaitTimeout:120
InactivityTimeout:180
....
该应用程序几乎有15个独立运行的模块，但也有一些模块包含其他模块，并使用它们的数据源。
是的，我知道你在想什么。在我来的时候，情况已经是这样了，我需要在团队着力进行重新设计的同时进行修补。
问题是这15个模块应该占用200个连接，但在这个混乱的情况下，每个模块还会占用所包含模块的连接。
这就是一个连接的混乱！
但是现在问题是，由于数据库没有更多资源，一些模块无法获取它们的200个连接，所以关于“ConnectionWaitTimeout”的配置会将一个漂亮的空连接返回到连接池，并显示以下消息：
无法为事务打开JDBC连接；嵌套异常是java.lang.IllegalArgumentException：连接不能为空
在数据库中检查，大多数模块占用了200个连接，但只有7个是活动的，197个是非活动的。
我无法找到正确的配置来释放这些非活动的连接。
我尝试使用了InactivityTimeout和AbandonedConnectionTimeout，但问题仍然存在。
英文:
I'm working on a very very big but poorly implemented project and we have a big performance issue related to de Database.
We are using Oracle, exadata and bla bla bla.
Application Server: tomcat
Diver: ojdbc6
And the next configs:
<property name="connectionCachingEnabled" value="true" />
<property name="connectionCacheProperties">
<value>
MinLimit:180
MaxLimit:200
InitialLimit:50
ConnectionWaitTimeout:120
InactivityTimeout:180
....
The application has almost 15 modules running independently but also some modules includes others and uses their datasources.
> Yes I know... what you are thinking. When I arrived it was already like this and I need to patch it meanwhile the team works strongly on
> reengineering
The issue is that the 15 modules should take 200 connections but with this spaghetti, each one takes also the included modules connections.
> this is a connections soup!
But at this point the issue is that some modules cannot take their 200 connections because the database has no more resources so.... related to the "ConnectionWaitTimeout" config it returns a beautifull null to the pool with the next message:
> Could not open JDBC Connection for transaction; nested exception is java.lang.IllegalArgumentException: Connection must not be null
Checking on the database most of modules takes 200 connections but have only 7 active and 197 inactives.
I cannot find the right config to break free the inactive ones.
I used InactivityTimeout and AbandonedConnectionTimeout but the issue persists.
答案1
得分: 1
没有分析这个应用程序，我们只能就为什么会出现这个问题进行猜测。
- 由于已经创建了197个连接（超过180个），看起来应用程序从池中请求了很多连接。
- 既然无法通过非活动超时来解决，我们必须假设这些连接仍然被应用程序保留着。
- 现在既然放弃超时也没有帮助，有两种可能性。
A) 应用程序确实在超时期间内查询了数据库。
B) 连接池已经回收了这些连接，应用程序应该捕获异常并进行重试。
我建议您了解其中一个模块中的代码，以了解连接的使用模式。
英文:
Without analysing the application we can just speculate on why this issue is happening.
- Since 197 connections have been created (more than 180), looks like application has requested so many connections from the pool.
- Since, Inactive timeout did not help, we must assume the connections are still retained by the application.
- Now since abandon timeout did not help, there are 2 possibilities.
A) Application is indeed querying DB within the timeout period.
B) Pool reclaimed the connections, application should have caught the exception and retried
I suggest you understand the code in 1 of the modules to understand the connection usage pattern.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论