CODEWITHSUNDEEP
springhibernatespring-bootmulti-tenant
jaks
jaks

Reputation: 4587

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?

Upvotes: 1

Views: 1048

Answers (2)

jaks
jaks

Reputation: 4587

closing connection, in case of error solved the problem.

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

    return connection
  }

Upvotes: 0

Barath
Barath

Reputation: 5283

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.

Upvotes: 2

Related Questions