We are having trouble with a Java web application running within Tomcat 6 that uses JDBC to connect to a SQL Server database.
我们在Tomcat 6中运行的Java Web应用程序遇到问题,该应用程序使用JDBC连接到SQL Server数据库。
After a few requests, the application server dies and the in the log files we find exceptions related to database connection failures.
在一些请求之后,应用程序服务器就会死亡,而在日志文件中我们会发现与数据库连接失败相关的异常。
We are not using any connection pooling right now and we are using the standard JDBC/ODBC/ADO driver bridge to connect to SQL Server.
我们现在没有使用任何连接池,我们使用标准的JDBC / ODBC / ADO驱动程序桥连接到SQL Server。
Should we consider using connection pooling to eliminate the problem?
我们应该考虑使用连接池来消除这个问题吗?
Also, should we change our driver to something like jTDS?
另外,我们应该将驱动程序更改为jTDS吗?
3 个解决方案
#1
That is the correct behavior if you are not closing your JDBC connections.
如果您没有关闭JDBC连接,那么这是正确的行为。
You have to call the close() method of each JDBC resource when you are finished using it and the other JDBC resources you obtained with it.
完成后使用它以及使用它获得的其他JDBC资源时,必须调用每个JDBC资源的close()方法。
That goes for Connection, Statement/PreparedStatement/CallableStatement, ResultSet, etc.
这适用于Connection,Statement / PreparedStatement / CallableStatement,ResultSet等。
If you fail to do that, you are hoarding potentially huge and likely very limited resources on the SQL server, for starters.
如果你没有这样做,那么对于初学者来说,你正在囤积SQL服务器上可能非常庞大且可能非常有限的资源。
Eventually, connections will not be granted, get queries to execute and return results will fail or hang.
最终,将不会授予连接,执行查询以及返回结果将失败或挂起。
You could also notice your INSERT/UPDATE/DELETE statements hanging if you fail to commit() or rollback() at the conclusion of each transaction, if you have not set autoCommit property to true.
如果未在每个事务结束时提交()或rollback(),如果尚未将autoCommit属性设置为true,则还会注意到INSERT / UPDATE / DELETE语句挂起。
What I have seen is that if you apply the rigor mentioned above to your JDBC client code, then JDBC and your SQL server will work wonderfully smoothly. If you write crap, then everything will behave like crap.
我所看到的是,如果您将上述严格应用于JDBC客户端代码,那么JDBC和您的SQL服务器将非常顺利地运行。如果你写垃圾,那么一切都会像垃圾一样。
Many people write JDBC calls expecting "something" else to release each thing by calling close() because that is boring and the application and server do not immediately fail when they leave that out.
许多人编写JDBC调用,希望通过调用close()来释放每个东西,因为这很无聊,应用程序和服务器在离开时不会立即失败。
That is true, but those programmers have written their programs to play "99 bottles of beer on the wall" with their server(s).
这是事实,但那些程序员已经编写了他们的程序,用他们的服务器播放“99瓶啤酒”。
The resources will become exhausted and requests will tend to result in one or more of the following happening: connection requests fail immediately, SQL statements fail immediately or hang forever or until some godawful lengthy transaction timeout timer expires, etc.
资源将耗尽,请求将导致以下一种或多种情况发生:连接请求立即失败,SQL语句立即失败或永久挂起或直到某些神圣的冗长事务超时计时器到期等。
Therefore, the quickest way to solve these types of SQL problems is not to blame the SQL server, the application server, the web container, JDBC drivers, or the disappointing lack of artificial intelligence embedded in the Java garbage collector.
因此,解决这些类型的SQL问题的最快方法不是责怪SQL垃圾收集器中嵌入的SQL服务器,应用程序服务器,Web容器,JDBC驱动程序或令人失望的人工智能缺乏。
The quickest way to solve them is to shoot the guy who wrote the JDBC calls in your application that talk to your SQL server with a Nerf dart. When he says, "What did you do that for...?!" Just point to this post and tell him to read it. (Remember not to shoot for the eyes, things in his hands, stuff that might be dangerous/fragile, etc.)
解决这些问题的最快方法是拍摄在您的应用程序中编写JDBC调用的人,他们使用Nerf dart与您的SQL服务器通信。当他说:“你为......做了什么?”只需指出这篇文章并告诉他阅读它。 (切记不要为了眼睛,手中的东西,可能危险/脆弱的东西等射击)
As for connection pooling solving your problems... no. Sorry, connection pools simply speed up the call to get a connection in your application by handing it a pre-allocated, perhaps recycled connection.
至于连接池解决你的问题......没有。对不起,连接池只是通过将预先分配的,可能是回收的连接交给应用程序来加速调用以获得应用程序中的连接。
The tooth fairy puts money under your pillow, the Easter bunny puts eggs & candy under your bushes, and Santa Clause puts gifts under your tree. But, sorry to shatter your illusions - the SQL server and JDBC driver do not close everything because you "forgot" to close all the stuff you allocated yourself.
牙仙子把钱放在你的枕头下,复活节兔子把鸡蛋和糖果放在你的灌木丛下,圣诞老人把礼物放在你的树下。但是,抱歉打破你的幻想 - SQL服务器和JDBC驱动程序不会关闭所有内容,因为你“忘记”关闭你自己分配的所有内容。
#2
I would definitely give jTDS a try. I've used it in the past with Tomcat 5.5 with no problems. It seems like a relatively quick, low impact change to make as a debugging step. I think you'll find it faster and more stable. It also has the advantage of being open source.
我肯定会试试jTDS。我过去在Tomcat 5.5中使用它没有任何问题。这似乎是一个相对快速,影响较小的变化,可以作为调试步骤。我想你会发现它更快更稳定。它还具有开源的优势。
In the long term, I think you'll want to look into connection pooling for performance reasons. When you do, I recommend having a look at c3p0. I think it's more flexible than the built in pooling options for Tomcat and I generally prefer "out of container" solutions so that it's less painful to switch containers in the future.
从长远来看,我认为你会出于性能原因考虑连接池。当你这样做时,我建议你看看c3p0。我认为它比Tomcat的内置池选项更灵活,我通常更喜欢“out of container”解决方案,以便将来切换容器时不那么痛苦。
#3
It's hard to tell really because you've provided so little information on the actual failure:
真的很难说,因为你提供的实际故障信息很少:
After a few requests, the application server dies and the in the log files we find exceptions related to database connection failures.
在一些请求之后,应用程序服务器就会死亡,而在日志文件中我们会发现与数据库连接失败相关的异常。
Can you tell us:
你能告诉我们:
- exactly what the error is that you're seeing
- give us a small example of the code where you connect and service one of your requests
- is it after a consistent number of transactions that it fails, or is it seemingly random
究竟是你所看到的错误
给我们一个代码示例,您可以在其中连接并处理您的一个请求
是在它失败后的一致数量的交易之后,还是看似随机的
I have written a lot of database related java code (pretty much all my code is database related), and used the MS driver, the jdt driver, and the one from jnetDirect.
我编写了很多与数据库相关的java代码(几乎所有代码都与数据库相关),并使用了MS驱动程序,jdt驱动程序和jnetDirect中的代码。
I'm sure if you provide us more details we can help you out.
我相信如果您向我们提供更多详细信息,我们可以帮助您。
#1
That is the correct behavior if you are not closing your JDBC connections.
如果您没有关闭JDBC连接,那么这是正确的行为。
You have to call the close() method of each JDBC resource when you are finished using it and the other JDBC resources you obtained with it.
完成后使用它以及使用它获得的其他JDBC资源时,必须调用每个JDBC资源的close()方法。
That goes for Connection, Statement/PreparedStatement/CallableStatement, ResultSet, etc.
这适用于Connection,Statement / PreparedStatement / CallableStatement,ResultSet等。
If you fail to do that, you are hoarding potentially huge and likely very limited resources on the SQL server, for starters.
如果你没有这样做,那么对于初学者来说,你正在囤积SQL服务器上可能非常庞大且可能非常有限的资源。
Eventually, connections will not be granted, get queries to execute and return results will fail or hang.
最终,将不会授予连接,执行查询以及返回结果将失败或挂起。
You could also notice your INSERT/UPDATE/DELETE statements hanging if you fail to commit() or rollback() at the conclusion of each transaction, if you have not set autoCommit property to true.
如果未在每个事务结束时提交()或rollback(),如果尚未将autoCommit属性设置为true,则还会注意到INSERT / UPDATE / DELETE语句挂起。
What I have seen is that if you apply the rigor mentioned above to your JDBC client code, then JDBC and your SQL server will work wonderfully smoothly. If you write crap, then everything will behave like crap.
我所看到的是,如果您将上述严格应用于JDBC客户端代码,那么JDBC和您的SQL服务器将非常顺利地运行。如果你写垃圾,那么一切都会像垃圾一样。
Many people write JDBC calls expecting "something" else to release each thing by calling close() because that is boring and the application and server do not immediately fail when they leave that out.
许多人编写JDBC调用,希望通过调用close()来释放每个东西,因为这很无聊,应用程序和服务器在离开时不会立即失败。
That is true, but those programmers have written their programs to play "99 bottles of beer on the wall" with their server(s).
这是事实,但那些程序员已经编写了他们的程序,用他们的服务器播放“99瓶啤酒”。
The resources will become exhausted and requests will tend to result in one or more of the following happening: connection requests fail immediately, SQL statements fail immediately or hang forever or until some godawful lengthy transaction timeout timer expires, etc.
资源将耗尽,请求将导致以下一种或多种情况发生:连接请求立即失败,SQL语句立即失败或永久挂起或直到某些神圣的冗长事务超时计时器到期等。
Therefore, the quickest way to solve these types of SQL problems is not to blame the SQL server, the application server, the web container, JDBC drivers, or the disappointing lack of artificial intelligence embedded in the Java garbage collector.
因此,解决这些类型的SQL问题的最快方法不是责怪SQL垃圾收集器中嵌入的SQL服务器,应用程序服务器,Web容器,JDBC驱动程序或令人失望的人工智能缺乏。
The quickest way to solve them is to shoot the guy who wrote the JDBC calls in your application that talk to your SQL server with a Nerf dart. When he says, "What did you do that for...?!" Just point to this post and tell him to read it. (Remember not to shoot for the eyes, things in his hands, stuff that might be dangerous/fragile, etc.)
解决这些问题的最快方法是拍摄在您的应用程序中编写JDBC调用的人,他们使用Nerf dart与您的SQL服务器通信。当他说:“你为......做了什么?”只需指出这篇文章并告诉他阅读它。 (切记不要为了眼睛,手中的东西,可能危险/脆弱的东西等射击)
As for connection pooling solving your problems... no. Sorry, connection pools simply speed up the call to get a connection in your application by handing it a pre-allocated, perhaps recycled connection.
至于连接池解决你的问题......没有。对不起,连接池只是通过将预先分配的,可能是回收的连接交给应用程序来加速调用以获得应用程序中的连接。
The tooth fairy puts money under your pillow, the Easter bunny puts eggs & candy under your bushes, and Santa Clause puts gifts under your tree. But, sorry to shatter your illusions - the SQL server and JDBC driver do not close everything because you "forgot" to close all the stuff you allocated yourself.
牙仙子把钱放在你的枕头下,复活节兔子把鸡蛋和糖果放在你的灌木丛下,圣诞老人把礼物放在你的树下。但是,抱歉打破你的幻想 - SQL服务器和JDBC驱动程序不会关闭所有内容,因为你“忘记”关闭你自己分配的所有内容。
#2
I would definitely give jTDS a try. I've used it in the past with Tomcat 5.5 with no problems. It seems like a relatively quick, low impact change to make as a debugging step. I think you'll find it faster and more stable. It also has the advantage of being open source.
我肯定会试试jTDS。我过去在Tomcat 5.5中使用它没有任何问题。这似乎是一个相对快速,影响较小的变化,可以作为调试步骤。我想你会发现它更快更稳定。它还具有开源的优势。
In the long term, I think you'll want to look into connection pooling for performance reasons. When you do, I recommend having a look at c3p0. I think it's more flexible than the built in pooling options for Tomcat and I generally prefer "out of container" solutions so that it's less painful to switch containers in the future.
从长远来看,我认为你会出于性能原因考虑连接池。当你这样做时,我建议你看看c3p0。我认为它比Tomcat的内置池选项更灵活,我通常更喜欢“out of container”解决方案,以便将来切换容器时不那么痛苦。
#3
It's hard to tell really because you've provided so little information on the actual failure:
真的很难说,因为你提供的实际故障信息很少:
After a few requests, the application server dies and the in the log files we find exceptions related to database connection failures.
在一些请求之后,应用程序服务器就会死亡,而在日志文件中我们会发现与数据库连接失败相关的异常。
Can you tell us:
你能告诉我们:
- exactly what the error is that you're seeing
- give us a small example of the code where you connect and service one of your requests
- is it after a consistent number of transactions that it fails, or is it seemingly random
究竟是你所看到的错误
给我们一个代码示例,您可以在其中连接并处理您的一个请求
是在它失败后的一致数量的交易之后,还是看似随机的
I have written a lot of database related java code (pretty much all my code is database related), and used the MS driver, the jdt driver, and the one from jnetDirect.
我编写了很多与数据库相关的java代码(几乎所有代码都与数据库相关),并使用了MS驱动程序,jdt驱动程序和jnetDirect中的代码。
I'm sure if you provide us more details we can help you out.
我相信如果您向我们提供更多详细信息,我们可以帮助您。