springhibernatespring-bootmulti-tenant

Throwing exception in MultiTenantConnectionProvider, exhausts the connections in Connection Pool


I am using multi tenancy by schema in MySQL as,

class SchemaPerTenantConnectionProvider : MultiTenantConnectionProvider {

  @Autowired
  private lateinit var dataSource: DataSource

  @Throws(SQLException::class)
  override fun getAnyConnection() = this.dataSource.connection

  @Throws(SQLException::class)
  override fun releaseAnyConnection(connection: Connection) {
    connection.close()
  }

  @Throws(SQLException::class)
  override fun getConnection(tenantIdentifier: String): Connection {
    val connection = this.anyConnection
    try {
      connection.createStatement().execute("USE $tenantIdentifier ")
    } catch (e: SQLException) {
      throw SQLException("Could not alter JDBC connection to schema [$tenantIdentifier]")
    }

    return connection
  }
...
}

My connection pool size is 10, now if any invalid tenantIdentifier is passed 10 times, 10 connections are exhausted, after that application is unable to acquire any connection.

Tried throwing Exception, HibernateException and it didn't help. Using connection with default schema will fetch wrong results. Is there a way to handle this scenario in getConnection(), to not to exhaust connection limits?


Solution

  • Below configuration should work, overriding public void releaseConnection(String tenantIdentifier, Connection connection) will ensure connection get released back to the connection pool.

       public class MultiTenantConnectionProviderImpl
                implements MultiTenantConnectionProvider, Stoppable {
            private final ConnectionProvider connectionProvider = ConnectionProviderUtils.buildConnectionProvider( "master" );
    
            @Override
            public Connection getAnyConnection() throws SQLException {
                return connectionProvider.getConnection();
            }
    
            @Override
            public void releaseAnyConnection(Connection connection) throws SQLException {
                connectionProvider.closeConnection( connection );
            }
    
            @Override
            public Connection getConnection(String tenantIdentifier) throws SQLException {
                final Connection connection = getAnyConnection();
                try {
                    connection.createStatement().execute( "USE " + tenanantIdentifier );
                }
                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 master" );
                }
                catch ( SQLException e ) {
                    // on error, throw an exception to make sure the connection is not returned to the pool.
                    // your requirements may differ
                    throw new HibernateException(
                            "Could not alter JDBC connection to specified schema [" +
                                    tenantIdentifier + "]",
                            e
                    );
                }
                connectionProvider.closeConnection( connection );
            }
    
            ...
        }
    

    Next, fine tuning the datasource configuration in spring boot:

    # Number of ms to wait before throwing an exception if no connection is available.
    spring.datasource.tomcat.max-wait=10000
    
    # Maximum number of active connections that can be allocated from this pool at the same time.
    spring.datasource.tomcat.max-active=50
    

    Reference : Working with datasources

    If the issue still persist, go ahead with datasource connection pooling mechanism support such as Hikari etc.