java - 如何配置 C3P0 或 BoneCP 数据源以承受数据库故障转移/中断

Question

我有一个基于 Spring/Hibernate 的应用程序，我需要对其进行修改以承受长达 1 分钟的数据库中断。我没有自己编写 hack，而是查看了 C3P0 和 BoneCP 数据源，看看是否可以为此目的配置它们。不幸的是，我无法让它适用于任何一个数据源。根据使用的数据源，我的测试程序因各种异常而终止:

使用 c3p0 数据源

9558 [main] ERROR org.hibernate.util.JDBCExceptionReporter - connection exception: connection failure: java.net.SocketException: Broken pipe
Exception in thread "main" org.springframework.dao.DataAccessResourceFailureException: could not inspect JDBC autocommit mode; nested exception is      org.hibernate.exception.JDBCConnectionException: could not inspect JDBC autocommit mode

使用 c3p0 connectionprovider

9341 [main] ERROR org.hibernate.util.JDBCExceptionReporter - connection exception: connection failure: java.io.EOFException
Exception in thread "main" org.springframework.dao.DataAccessResourceFailureException: Cannot open connection; nested exception is org.hibernate.exception.JDBCConnectionException: Cannot open connection
    at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:627)

使用 BoneCP 数据源

12250 [main] ERROR org.hibernate.util.JDBCExceptionReporter - connection exception: connection failure: java.net.SocketException: Broken pipe
Exception in thread "main" org.springframework.dao.DataAccessResourceFailureException: could not inspect JDBC autocommit mode; nested exception is org.hibernate.exception.JDBCConnectionException: could not inspect JDBC autocommit mode
    at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:627)

使用 BoneCP connectionprovider

19356 [main] ERROR org.hibernate.util.JDBCExceptionReporter - connection exception: connection failure: java.io.EOFException
Exception in thread "main" org.springframework.dao.DataAccessResourceFailureException: Cannot open connection; nested exception is org.hibernate.exception.JDBCConnectionException: Cannot open connection
    at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:627)
    at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
    at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:424)
    at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
    at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:921)
    at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:913)
    at my.db.failover.CustomerDao.list(CustomerDao.java:14)

C3P0 文档指出它可以处理这样的情况，而 BoneCP 文档没有直接提及这一点。

为了对此进行测试，我编写了一个小程序，该程序从 HSQLDB 服务器读取一个表并 hibernate 一秒钟，然后再次重复该过程。它可以配置为使用以下配置运行:c3p0_datasource.xml

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="org.hsqldb.jdbcDriver" />
    <property name="jdbcUrl" value="jdbc:hsqldb:hsql://localhost:9002" />
    <property name="user" value="sa" />
    <property name="password" value="" />
    <property name="acquireIncrement" value="2" />
    <property name="minPoolSize" value="3" />
    <property name="maxPoolSize" value="25" />
    <property name="idleConnectionTestPeriod" value="3000" />
    <property name="acquireRetryAttempts" value="30" />
    <property name="acquireRetryDelay" value="1001" />
    <property name="breakAfterAcquireFailure" value="false" />
    <property name="maxIdleTime" value="0" />
    <property name="maxConnectionAge" value="0" />
    <property name="maxIdleTimeExcessConnections" value="0" />
    <property name="automaticTestTable" value="C3P0_TEST" />
</bean>
<bean id="sessionFactory" parent="abstractSessionFactory">
        <property name="dataSource" ref="dataSource"/>
</bean>

c3p0_connectionprovider.xml

<bean id="sessionFactory" parent="abstractSessionFactory">
        <property name="hibernateProperties">
            <props>
                . . .
                <prop key="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</prop>
                <prop key="hibernate.c3p0.acquire_increment">2</prop>
                <prop key="hibernate.c3p0.idle_test_period">300</prop>
                <prop key="hibernate.c3p0.timeout">1800</prop>
                <prop key="hibernate.c3p0.max_size">25</prop>
                <prop key="hibernate.c3p0.min_size">1</prop>
                <prop key="hibernate.c3p0.max_statement">0</prop>
                <prop key="hibernate.c3p0.preferredTestQuery">select 1;</prop>
                <prop key="hibernate.c3p0.validate">true</prop>     
            </props>
        </property>
</bean>

bonecp_datasource.xml

<bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource" destroy-method="close">
   <property name="driverClass" value="org.hsqldb.jdbcDriver" />
   <property name="jdbcUrl" value="jdbc:hsqldb:hsql://localhost:9002" />
   <property name="username" value="sa"/>
   <property name="password" value=""/>
   <property name="idleConnectionTestPeriod" value="60"/>
   <property name="idleMaxAge" value="240"/>
   <property name="maxConnectionsPerPartition" value="30"/>
   <property name="minConnectionsPerPartition" value="10"/>
   <property name="partitionCount" value="3"/>
   <property name="acquireIncrement" value="5"/>
   <property name="statementsCacheSize" value="100"/>
   <property name="releaseHelperThreads" value="3"/>
</bean>
<bean id="sessionFactory" parent="abstractSessionFactory">
    <property name="dataSource" ref="dataSource"/>
</bean>

bonecp_connectionprovider.xml

<bean id="sessionFactory" parent="abstractSessionFactory">
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.connection.provider_class">com.jolbox.bonecp.provider.BoneCPConnectionProvider</prop>
                . . .
                <prop key="bonecp.idleMaxAgeInMinutes">2</prop>
                <prop key="bonecp.idleConnectionTestPeriodInMinutes">3</prop>
                <prop key="bonecp.partitionCount">3</prop>
                <prop key="bonecp.acquireIncrement">10</prop>
                <prop key="bonecp.maxConnectionsPerPartition">60</prop>
                <prop key="bonecp.minConnectionsPerPartition">20</prop>
                <prop key="bonecp.statementsCacheSize">50</prop>
                <prop key="bonecp.releaseHelperThreads">3</prop>
            </props>
        </property>
</bean>

有谁知道这是否可以做到？

附言!您可以从this link下载测试工程。如果有人需要更深入地研究这个:)

以下是构建和运行这些东西的步骤。

1. Build the project with Maven:  
  mvn clean install package appassembler:assemble 
2. Set the start scripts as executable (Unix/linux)
  chmod +x target/appassembler/bin/*  
3. Run the DBServer 
  target/appassembler/bin/dbServer 
4. Run the test client from another shell 
  target/appassembler/bin/client
5. Select one of following configurations to use for the client 
    0 : c3p0_datasource.xml
    1 : c3p0_connectionprovider.xml
    2 : bonecp_datasource.xml
    3 : bonecp_connectionprovider.xml
6. Terminate the dbServer with ctrl c
7. Start it again and the client should survive the DB outage  

但它不会:(

Answer 1

c3p0 将从任意持续时间的数据库中断中恢复，只要 breakOnAcquireFailure 未设置为 true。然而，这并不意味着客户永远不会在获取失败时看到异常。默认情况下，c3p0 将在整轮获取尝试失败后向客户端抛出异常，根据您的配置，这将花费 30030 毫秒(~30 秒)。如果您希望 c3p0 在向客户端抛出异常之前继续尝试获取连接的时间更长，请将 acquireRetryAttempts 设置得更高或将 acquireRetryDelay 设置得更长。一轮获取尝试的总长度为acquireRetryAttempts * acquireRetryDelay

如果您希望客户端在数据库中断时无限期地等待恢复，请将 acquireRetryAttempts 设置为 0。使用此设置，当 c3p0 无法获取连接时，它将在每个 acquireRetryDelay 期间无限期地尝试 毫秒，让客户端挂起，直到成功或太阳熄灭。

请参阅http://www.mchange.com/projects/c3p0/#configuring_recovery

附注您提供的异常(exception)情况非常肤浅。您需要查看日志文件以查看并提供有关正在发生的事情的更好信息。