如何为c3p0/hibernate配置查询超时阈值

发布于 2024-08-07 09:24:40 字数 1966 浏览 6 评论 0原文

我有一个使用 Hibernate 3、c3p0 和 spring 2.5.6 的应用程序。我们有一个配置为与 postgres 数据库通信的数据源。在应用程序服务器和数据库之间引入防火墙之前，一切都运行良好。在尝试与数据库通信时，我们间歇性地收到 java.net.SocketTimeoutException: Read Timed Out 错误。

我们认为防火墙的开销导致数据库响应延迟。我们希望通过增加查询在被视为超时之前应等待多长时间的阈值来验证这一点（如果可能的话）。这是一个堆栈跟踪片段

org.postgresql.util.PSQLException: An I/O error occured while sending to the backend.
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:218)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
    at org.hibernate.loader.Loader.doQuery(Loader.java:697)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
    at org.hibernate.loader.Loader.loadEntity(Loader.java:1881)
    ... 35 more
Caused by: java.net.SocketTimeoutException: Read timed out
    at java.net.SocketInputStream.socketRead0(Native Method)
    at java.net.SocketInputStream.read(Unknown Source)
    at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:135)
    at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:104)
    at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73)
    at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:259)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1166)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
    ... 44 more

原文

I have an application that is using Hibernate 3, c3p0, and spring 2.5.6. We have a datasource that is configured to speak with a postgres database. Everything was working great until a firewall was introduced between the application server and the database. We intermittently are getting java.net.SocketTimeoutException: Read Timed Out errors while trying to communicate with the database.

We believe the overhead of the firewall is causing a delayed response from the database. We want to verify this by increasing the thresh hold of how long a query should wait before deemed timed out (if that is even possible). Here is a stacktrace snippet

org.postgresql.util.PSQLException: An I/O error occured while sending to the backend.
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:218)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
    at org.hibernate.loader.Loader.doQuery(Loader.java:697)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
    at org.hibernate.loader.Loader.loadEntity(Loader.java:1881)
    ... 35 more
Caused by: java.net.SocketTimeoutException: Read timed out
    at java.net.SocketInputStream.socketRead0(Native Method)
    at java.net.SocketInputStream.read(Unknown Source)
    at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:135)
    at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:104)
    at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73)
    at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:259)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1166)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
    ... 44 more

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

新人笑 2024-08-14 09:24:40

这与 Hibernate 或 C3P0 无关； JDBC 驱动程序超时。

如果您使用的是 8.4 或更高版本，请尝试设置 socketTimeout 在连接字符串中设置为更高的值（甚至为零以禁用它）。

回复收藏 0 原文

萝莉病 2024-08-14 09:24:40

尝试在 jdbc 属性中配置超时，如下所示：

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"
          destroy-method="close">
    <property name="driverClassName" value="org.postgresql.Driver"/>
    <property name="url" value="jdbc:postgresql://localhost:5433/yourdb"/>
    <property name="username" value="some"/>
    <property name="password" value=""/>
    <property name="connectionProperties">
      <props>
       <prop key="socketTimeout">1000000</prop>
      </props>
    </property>
</bean>

根据需要为 socketTimeout 属性设置尽可能多的值

Try to configure timeout in jdbc properties, like this:

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"
          destroy-method="close">
    <property name="driverClassName" value="org.postgresql.Driver"/>
    <property name="url" value="jdbc:postgresql://localhost:5433/yourdb"/>
    <property name="username" value="some"/>
    <property name="password" value=""/>
    <property name="connectionProperties">
      <props>
       <prop key="socketTimeout">1000000</prop>
      </props>
    </property>
</bean>

Set for socketTimeout property as much value as required

回复收藏 0 原文

~没有更多了~