Springboot多租户使用MultiTenantConnectionProvider时总是抛出org.apache.tomcat.jdbc.pool.PoolExhaustedException异常

Question

我已经开始将我的现有Spring Boot（1.5.4.RELEASE）应用程序转换为支持多租户功能。这是一个基于模式的多租户解决方案，基于mysql。 正如Hibernate文档所建议的：

https://docs.jboss.org/hibernate/orm/4.2/devguide/en-US/html/ch16.html

我已经实现了MultiTenantConnectionProvider和CurrentTenantIdentifierResolver接口，它可以正常工作。

package com.ifi.aws.tenant.config.hibernate;

import org.hibernate.HibernateException;
import org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.ifi.aws.tenant.entity.TenantContext;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

@Component
public class MultiTenantConnectionProviderImpl implements MultiTenantConnectionProvider {

  private static final long serialVersionUID = 6246085840652870138L;

  @Autowired
  private DataSource dataSource;

@Override
public Connection getAnyConnection() throws SQLException {
  return dataSource.getConnection();
}

@Override
public void releaseAnyConnection(Connection connection) throws SQLException {
  connection.close();
}

@Override
public Connection getConnection(String tenantIdentifier) throws SQLException {
  final Connection connection = getAnyConnection();
  try {
    connection.createStatement().execute( "USE " + tenantIdentifier );
  }
  catch ( SQLException e ) {
    throw new HibernateException(
        "Could not alter JDBC connection to specified schema [" + tenantIdentifier + "]",
      e
      );
  }
  return connection;
}

@Override
public void releaseConnection(String tenantIdentifier, Connection connection) throws SQLException {
  try {
    connection.createStatement().execute( "USE " + TenantContext.DEFAULT_TENANT );
  }
  catch ( SQLException e ) {
    throw new HibernateException(
        "Could not alter JDBC connection to specified schema [" + tenantIdentifier + "]",
      e
      );
  }
  connection.close();
}

@SuppressWarnings("rawtypes")
@Override
public boolean isUnwrappableAs(Class unwrapType) {
  return false;
}

@Override
public <T> T unwrap(Class<T> unwrapType) {
  return null;
}

@Override
public boolean supportsAggressiveRelease() {
  return true;
}

}

---

package com.ifi.aws.tenant.config.hibernate;

import org.hibernate.context.spi.CurrentTenantIdentifierResolver;
import org.springframework.context.annotation.Configuration;

import com.ifi.aws.tenant.entity.TenantContext;

@Configuration
public class TenantIdentifierResolver implements CurrentTenantIdentifierResolver {

  @Override
  public String resolveCurrentTenantIdentifier() {


     String tenantId = TenantContext.getTenantSchema();

    //System.out.println("------------------ resolveCurrentTenantIdentifier = " + tenantId);
    if (tenantId != null) {
        return tenantId;
    }
    return TenantContext.DEFAULT_TENANT;
}
@Override
public boolean validateExistingCurrentSessions() {
    return true;
}
}

然后下面是我的hibernate配置

package com.ifi.aws.tenant.config.hibernate;

import org.hibernate.MultiTenancyStrategy;
import org.hibernate.cfg.Environment;
import org.hibernate.context.spi.CurrentTenantIdentifierResolver;
import 
org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;

import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

@Configuration
public class HibernateConfig {

  @Autowired
  private JpaProperties jpaProperties;

  @Bean
  public JpaVendorAdapter jpaVendorAdapter() {
    return new HibernateJpaVendorAdapter();
  }

  @Bean
  public LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource dataSource,
  MultiTenantConnectionProvider multiTenantConnectionProviderImpl,
  CurrentTenantIdentifierResolver currentTenantIdentifierResolverImpl) {
    Map<String, Object> properties = new HashMap<>();
    properties.putAll(jpaProperties.getHibernateProperties(dataSource));
    properties.put(Environment.MULTI_TENANT, MultiTenancyStrategy.SCHEMA);
    properties.put(Environment.MULTI_TENANT_CONNECTION_PROVIDER, multiTenantConnectionProviderImpl);
    properties.put(Environment.MULTI_TENANT_IDENTIFIER_RESOLVER, currentTenantIdentifierResolverImpl);

    LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
    em.setDataSource(dataSource);
    em.setPackagesToScan("com.ifi.aws");
    em.setJpaVendorAdapter(jpaVendorAdapter());
    em.setJpaPropertyMap(properties);
   return em;
  }
}

然而，系统不时会崩溃并出现以下错误。

Springboot Multi-tenant with MultiTenantConnectionProvider always throw org.apache.tomcat.jdbc.pool.PoolExhaustedException: [http-nio-8086-exec-2] Timeout: Pool empty. Unable to fetch a connection in 30 seconds, none available[size:100; busy:100; idle:0; lastwait:30000].

我在这个网站上阅读了一些内容，并在以下问题中找到了完全相同的问题。

Spring Boot：Apache Derby池为空。无法在30秒内获取连接 Tomcat连接池耗尽

他们建议的解决方法之一是添加以下配置

spring.datasource.tomcat.max-active=100
spring.datasource.tomcat.max-idle=8
spring.datasource.tomcat.min-idle=8

但我仍然遇到相同的错误并且调试代码后发现每次执行数据库调用后都会关闭连接。你们有任何想法吗？

编辑

昨天我发现API根本没有关闭任何连接。我编写了一个简单的实用程序来检查连接状态，如下所示：

    @Autowired
private DataSource ds;

@Before("execution(* com.ifi.aws.*.dao.impl.springData.*.*(..))")
public void logBeforeConnection(JoinPoint jp) throws Throwable {
    logDataSourceInfos("Before", jp);
}

@After("execution(* com.ifi.aws.*.dao.impl.springData.*.*(..)) ")
public void logAfterConnection(JoinPoint jp) throws Throwable {
    logDataSourceInfos("After", jp);
}

public void logDataSourceInfos(final String time, final JoinPoint jp) {
    final String method = String.format("%s:%s", jp.getTarget().getClass().getName(), jp.getSignature().getName());
    logger.debug("--------------------------------------------------------------------------");
    logger.debug(String.format("%s %s: number of connections in use by the application (active): %d.", time, method, ds.getNumActive()));
    logger.debug(String.format("%s %s: the number of established but idle connections: %d.", time, method, ds.getNumIdle()));
    logger.debug(String.format("%s %s: number of threads waiting for a connection: %d.", time, method, ds.getWaitCount()));
}

}

这表明活跃连接不断增长。

Before com.sun.proxy.$Proxy127:findOne: number of connections in use by the application (active): 21.
Before com.sun.proxy.$Proxy127:findOne: the number of established but idle connections: 0.
Before com.sun.proxy.$Proxy127:findOne: number of threads waiting for a connection: 0

-----------------

After com.sun.proxy.$Proxy127:findOne: number of connections in use by the application (active): 21.
After com.sun.proxy.$Proxy127:findOne: the number of established but idle connections: 0.
After com.sun.proxy.$Proxy127:findOne: number of threads waiting for a connection: 0.
o.h.e.t.i.TransactionImpl                : committing

-------------------

Before com.sun.proxy.$Proxy127:findOne: number of connections in use by the application (active): 21.
Before com.sun.proxy.$Proxy127:findOne: the number of established but idle connections: 0.
Before com.sun.proxy.$Proxy127:findOne: number of threads waiting for a connection: 0

-----------------

After com.sun.proxy.$Proxy127:findOne: number of connections in use by the application (active): 22.
After com.sun.proxy.$Proxy127:findOne: the number of established but idle connections: 0.
After com.sun.proxy.$Proxy127:findOne: number of threads waiting for a connection: 0.
o.h.e.t.i.TransactionImpl                : committing

-------------------

然而在我的本地环境中，这是完全正常的，并且它会适当地关闭连接。我的测试环境部署在AWS t2 Windows实例上，此API作为Spring Boot jar文件部署，并且与相同的t2实例中安装了MYSQL服务器。我能看到的唯一区别是操作系统版本和可能的MYSQL服务器配置。

编辑

我按照@xerx593的说明解决了问题。

问题在于supportsAggressiveRelease = true，我像@xerx593建议的那样将其更改为false。然而，我仍然想知道为什么它可以在我的本地环境中工作，而在测试环境中却不能。根据Hibernate文档，它说“此连接提供程序是否支持JDBC连接的积极释放以及稍后重新获取这些连接（如果需要）？”测试和本地环境都具有相同的配置，这可能是操作系统版本或mysql配置的结果吗？

谢谢， Kelum

- keth

---

MultiTenantConnectionProvider 可能是抛出异常的人（他坐在前门口），但连接泄漏可能发生在任何地方...或者没有连接泄漏，但您的应用程序真的很高频（您尝试过 max-active=1000 吗？）...还有什么意思是“time to time”..？ - xerx593

@xerx593，感谢您的回复。Time to time的意思是在我进行测试时，它会崩溃，但我无法看到任何模式。但昨天我发现它根本不关闭任何连接。如果我们设置max-active=1000，那么在1000个数据库调用后它就会崩溃。我编写了一个小工具来检查连接，并且这个API在我的本地环境中可以正确关闭连接，但在部署在AWS t2微型Windows实例上的staging环境中，它根本不关闭任何连接。请注意，MYSQL服务器也安装在该t2实例中。已更新上述描述。 - keth

...所以（对我来说）只剩下随机猜测：1. DEFAULT_TENANT是否在数据库（有关）中？2. 尝试将connection.close()封装到finally块中（MTPImpl）3. 尝试将supportsAggressiveRelease = false放入“try”块中。 - xerx593

@xerx593，谢谢，它起作用了。问题在于supportsAggressiveRelease = true，我按照您的建议将其更改为false。但是我仍然想知道为什么它可以在我的本地环境中工作，而在测试环境中却不能。根据Hibernate文档，它说“此连接提供程序是否支持JDBC连接的积极释放和稍后重新获取这些连接（如果需要）？”测试和本地环境具有相同的配置，这可能是操作系统版本或mysql配置的结果吗？ - keth

但是我必须“猜测”它3次（中的最后一次），是吗!? 墨菲定律！ :) 很高兴，我们“修复”了它... 但要真正“理解”（您环境中的差异），我们需要更多信息/您必须进行比较！;) - xerx593

Answer 1

通过"暴力破解"，我们发现问题出在supportsAggressiveRelease标志上，当设置为true时，在DEV环境中没有问题，但会导致AWS实例出现问题。

解决方案：

@Override
public boolean supportsAggressiveRelease() {
   return false;//!
}

---

这个环境不支持“积极释放”，是由于您的环境配置/性质所致...

• MultiTenantConnectionProvider javadoc（多租户连接提供程序javadoc）
• AWS文档主页（亚马逊云服务文档主页）

- xerx593