postgresql com.atomikos.连接池已耗尽-尝试增加DataSourceBean上的“maxPoolSize”和/或“borrowConnectionTimeout”

5anewei6  于 2023-04-29  发布在  PostgreSQL
关注(0)|答案(1)|浏览(414)

我们有一个应用程序，在成功地从数据库进行了一系列事务处理后，遇到了这样的错误，我们意识到jdbc连接池充满了不会被关闭的空闲连接，并不断堆积。
以下是来自springboot app的错误日志：

2021-08-12T00:50:28,078 INFO  [org.apache.cxf.binding.soap.interceptor.Soap12FaultOutInterceptor:66] (http-nio-8080-exec-83) eventId=w:ef9c7df4-fae6-11eb-b2fa-000d3a434359, tenantId=, pe=, user=, app=, pid=, message=class org.apache.cxf.binding.soap.interceptor.Soap12FaultOutInterceptor$Soap12FaultOutInterceptorInternalmultipart/related; type="application/xop+xml"; boundary="uuid:c21d3fc7-b321-4895-a608-1e4e58824a90"; start="<root.message@cxf.apache.org>"; start-info="application/soap+xml"
2021-08-12 00:51:28 org.hibernate.engine.jdbc.spi.SqlExceptionHelper:137 [WARN] SQL Error: 0, SQLState: null
2021-08-12 00:51:28 org.hibernate.engine.jdbc.spi.SqlExceptionHelper:142 [ERROR] Connection pool exhausted - try increasing 'maxPoolSize' and/or 'borrowConnectionTimeout' on the DataSourceBean.
2021-08-12T00:51:28,074 WARN  [org.apache.cxf.phase.PhaseInterceptorChain:475] (http-nio-8080-exec-84) eventId=w:e29c0214-fae6-11eb-8a27-000d3a434359, tenantId=, pe=, user=, app=, pid=, message=Interceptor for {urn:ihe:iti:xds-b:2007}HIEDirectXDRService#{urn:ihe:iti:xds-b:2007}DocumentRepository_ProvideAndRegisterDocumentSet-b has thrown exception, unwinding now
org.springframework.orm.hibernate5.HibernateJdbcException: JDBC exception on Hibernate data access: SQLException for SQL [n/a]; SQL state [null]; error code [0]; Unable to acquire JDBC Connection; nested exception is org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection
    at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:252) ~[spring-orm-5.2.8.RELEASE.jar!/:5.2.8.RELEASE]
    at org.springframework.orm.hibernate5.HibernateExceptionTranslator.convertHibernateAccessException(HibernateExceptionTranslator.java:102) ~[spring-orm-5.2.8.RELEASE.jar!/:5.2.8.RELEASE]
......
Caused by: org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47) ~[hibernate-core-5.4.18.Final.jar!/:5.4.18.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) ~[hibernate-core-5.4.18.Final.jar!/:5.4.18.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99) ~[hibernate-core-5.4.18.Final.jar!/:5.4.18.Final]
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:107) ~[hibernate-core-5.4.18.Final.jar!/:5.4.18.Final]
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:134) ~[hibernate-core-5.4.18.Final.jar!/:5.4.18.Final]
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.connection(StatementPreparerImpl.java:50) ~[hibernate-core-5.4.18.Final.jar!/:5.4.18.Final]
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:149) ~[hibernate-core-5.4.18.Final.jar!/:5.4.18.Final]
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:176) ~[hibernate-core-5.4.18.Final.jar!/:5.4.18.Final]
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:151) ~[hibernate-core-5.4.18.Final.jar!/:5.4.18.Final]
......
Caused by: com.atomikos.jdbc.AtomikosSQLException: Connection pool exhausted - try increasing 'maxPoolSize' and/or 'borrowConnectionTimeout' on the DataSourceBean.
    at com.atomikos.jdbc.AtomikosSQLException.throwAtomikosSQLException(AtomikosSQLException.java:29) ~[transactions-jdbc-4.0.6.jar!/:?]
    at com.atomikos.jdbc.AbstractDataSourceBean.throwAtomikosSQLException(AbstractDataSourceBean.java:76) ~[transactions-jdbc-4.0.6.jar!/:?]
    at com.atomikos.jdbc.AbstractDataSourceBean.throwAtomikosSQLException(AbstractDataSourceBean.java:71) ~[transactions-jdbc-4.0.6.jar!/:?]
    at com.atomikos.jdbc.AbstractDataSourceBean.getConnection(AbstractDataSourceBean.java:351) ~[transactions-jdbc-4.0.6.jar!/:?]
    at com.atomikos.jdbc.nonxa.AtomikosNonXADataSourceBean.getConnection(AtomikosNonXADataSourceBean.java:189) ~[transactions-jdbc-4.0.6.jar!/:?]
    at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122) ~[hibernate-core-5.4.18.Final.jar!/:5.4.18.Final]
    at org.hibernate.internal.NonContextualJdbcConnectionAccess.obtainConnection(NonContextualJdbcConnectionAccess.java:38) ~[hibernate-core-5.4.18.Final.jar!/:5.4.18.Final]
......

我们尝试增加maxpoolsize和borrowConnectionTimeout，但没有成功解决这个问题。该应用程序使用atomikos作为事务管理器，我们正在试图找到如何关闭空闲连接几分钟后没有任何活动，并保持连接总数下降，较少的空闲连接挂起意味着更多的活动连接可以希望去。我们在atomikos中使用XA事务。请告知我们如何才能实现目标，保持空闲连接下降，并关闭或刷新空闲连接后，一定时间内没有活动的事务，以允许更多的活动连接开始wtihout运行的连接池。谢谢。顺便说一句，我们用atomikos 4。0.6.
申请表。yml看起来像：

app:
  datasource:
    transaction:
      transaction_timeout: 600
    postgres:
      dialect: org.hibernate.dialect.PostgreSQL82Dialect
      driverClassName: org.postgresql.Driver
    tac:
      borrow_conn_timeout: 30
      max_idle_time: 300
      min_pool_size: 5
      max_pool_size: 20
      show_sql: false
      max_lifetime: 1800
      test_query: 'select 1'
hibernate:
  connection:
    release_mode: after_transaction
  current_session_context_class: jta
spring:
  profiles:
    active: PostgreSql
  jpa:
    hibernate:
      use-new-id-generator-mappings: false
    properties:
      hibernate:
        id:
          new_generator_mappings: false
  jta:
    atomikos:
      connectionfactory:
        ignore-session-transacted-flag: false
        max-pool-size: 50
        min-pool-size: 5
      datasource:
        max-pool-size: 50
        min-pool-size: 5
      properties:
        service: com.atomikos.icatch.standalone.UserTransactionServiceFactory
    enabled: true

以下是DatabaseTransactionConfiguration：

@Configuration
public class DatabaseTransactionConfiguration {

    @Value("${app.datasource.transaction.transaction_timeout}")
    Integer transactionTimeout = 300;

    @Bean(name = "userTransaction")
    public UserTransaction userTransaction() throws Throwable {
        final UserTransactionImp userTransactionImp = new UserTransactionImp();
        userTransactionImp.setTransactionTimeout(transactionTimeout);
        return userTransactionImp;
    }

    @Bean(name = "atomikosTransactionManager", initMethod = "init", destroyMethod = "close")
    public TransactionManager atomikosTransactionManager() throws Throwable {
        final UserTransactionManager userTransactionManager = new UserTransactionManager();
        userTransactionManager.setTransactionTimeout(transactionTimeout);
        userTransactionManager.setStartupTransactionService(false);
        userTransactionManager.setForceShutdown(false);
        return userTransactionManager;
    }

    @Bean(name = { "transactionManager", "jtaTransactionManager" })
    @DependsOn({ "userTransaction", "atomikosTransactionManager" })
    public PlatformTransactionManager transactionManager() throws Throwable {
        final UserTransaction userTransaction = userTransaction();
        final TransactionManager atomikosTransactionManager = atomikosTransactionManager();
        return new JtaTransactionManager(userTransaction, atomikosTransactionManager);
    }
}

下面是 www.example.com

@Configuration
@EnableTransactionManagement
public class DataSourceConfigurationTAC {

    @Value("${app.datasource.postgres.driverClassName}")
    String postgresDriverClass;

    @Value("${app.datasource.oracle.dialect}")
    String oracleDialect;

    @Value("${app.datasource.postgres.dialect}")
    String postgresDialect;
    
    @Value("${app.datasource.danc.borrow_conn_timeout}")
    Integer borrowConnectionTimeout;
    
    @Value("${app.datasource.danc.max_idle_time}")
    Integer maxIdleTime;
    
    @Value("${app.datasource.danc.min_pool_size}")
    Integer minPoolSize;
    
    @Value("${app.datasource.danc.max_pool_size}")
    Integer maxPoolSize;
    
    /**
     * Maximum amount of seconds that a connection is kept in the pool before 
     * it is destroyed automatically. Optional, defaults to 0 (no limit).
     */
    @Value("${app.datasource.tac.max_lifetime:0}")
    Integer maxLifetime;
    
    /**
     * SQL query or statement used to validate a connection before returning it. Optional.
     */
    @Value("${app.datasource.tac.test_query:@null}")
    String testQuery;

    @Bean(name = "dataSource")
    @Primary
    public DataSource tacDataSource() throws SQLException {
        AtomikosNonXADataSourceBean bean = new AtomikosNonXADataSourceBean();
        bean.setUniqueResourceName(dataSourceName);
        bean.setDriverClassName(OdxcEnvironment.isPostgreSql() ? postgresDriverClass : oracleDriverClass);
        bean.setBorrowConnectionTimeout(borrowConnectionTimeout);
        bean.setMaxIdleTime(maxIdleTime);
        bean.setMinPoolSize(minPoolSize);
        bean.setMaxPoolSize(maxPoolSize);
        bean.setMaxLifetime(maxLifetime);
        bean.setTestQuery(testQuery);
        return bean;
    }

    @Bean(name = { "sessionFactory" })
    @Primary
    public LocalSessionFactoryBean customerEntityManager(
            @Autowired @Qualifier("jtaTransactionManager") PlatformTransactionManager transactionManager,
            @Autowired @Qualifier("dataSource") DataSource tacDataSource) throws Throwable {
        LocalSessionFactoryBean sessionFactoryBean = new LocalSessionFactoryBean();
        sessionFactoryBean.setDataSource(tacDataSource);

        Properties hibernateProperties = new Properties();
        hibernateProperties.setProperty("hibernate.dialect", postgresDialect);
        hibernateProperties.setProperty("hibernate.transaction.jta.platform", AtomikosJtaPlatform.class.getName());
        hibernateProperties.setProperty("javax.persistence.transactionType", "JTA");
        hibernateProperties.setProperty("hibernate.transaction.coordinator_class", "jta");
        hibernateProperties.setProperty("hibernate.jdbc.use_streams_for_binary", Boolean.FALSE.toString());
        hibernateProperties.setProperty("hibernate.id.new_generator_mappings", Boolean.FALSE.toString());
        hibernateProperties.setProperty("hibernate.show_sql", Boolean.FALSE.toString());
        hibernateProperties.setProperty("hibernate.format_sql", Boolean.FALSE.toString());
        hibernateProperties.setProperty("hibernate.use_sql_comments", Boolean.FALSE.toString());
        hibernateProperties.setProperty("hibernate.default_batch_fetch_size", "16");
        hibernateProperties.setProperty("hibernate.cache.use_second_level_cache", Boolean.TRUE.toString());
        hibernateProperties.setProperty("hibernate.cache.region.factory_class",
                "org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory");
        hibernateProperties.setProperty("hibernate.cache.region_prefix", "connnectivity_springboot");
        hibernateProperties.setProperty("hibernate.cache.use_query_cache", Boolean.TRUE.toString());
        hibernateProperties.setProperty("hibernate.generate_statistics", Boolean.FALSE.toString());
        sessionFactoryBean.setHibernateProperties(hibernateProperties);
        sessionFactoryBean.setJtaTransactionManager(transactionManager);
        return sessionFactoryBean;
    }
}
postgresql

1条答案

按热度按时间
hivapdat

hivapdat1#

但是，很晚了，我可以看到这里有一个潜在的问题，可能会泄露您的连接。您正在将hibernate连接模式设置为after_transaction，而对于JTA数据源，您应该将其设置为after_statement。

表3.4。Hibernate JDBC和连接属性

属性名称目的
hibernate.connection.释放方式指定Hibernate何时释放JDBC连接。默认情况下，JDBC连接将一直保持到会话显式关闭或断开连接。对于应用服务器JTA数据源，您应该使用after_statement在每次JDBC调用之后主动释放连接。对于非JTA连接，通常在每个事务结束时通过使用after_transaction释放连接是有意义的。auto将为JTA和CMT事务策略选择after_statement，为JDBC事务策略选择after_transaction。例如auto（默认）/on_close/after_transaction/after_statement

对于应用服务器JTA数据源，您应该使用after_statement在每次JDBC调用之后主动释放连接。
你可以在这里看到更多细节：Why is hibernate.connection.release_mode after_transaction not recommended for JTA?

赞(0）分享  回复(0）举报  2023-04-29
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com