Hibernate database per tenant multi-tenant connection provider is creating multiple database connections for single tenant

Question

I want to know how hibernate's multi-tenant connection provider handles the data base connections for multiple tenants in separate database multi-tenancy approach. If I hit the API with same tenant, say 1234, then at first hit it should CONNECT to that tenant's specific database and after several hits for same tenant, will it use the same connection of database or it will open the new connection again for the same tenant?

I have used AbstractDataSourceBasedMultiTenantConnectionProviderImpl and CurrentTenantIdentifierResolver implementations in database per tenant approach

class CurrentTenantIdentifierResolver {
    // Other methods + fields
    public String resolveCurrentTenantIdentifier() {        
      String tenantId = TenantContext.getCurrentTenant();       
      return tenantId;  
    }
}

class AbstractDataSourceBasedMultiTenantConnectionProviderImpl {
    // Other methods + fields

    @Override protected DataSource selectDataSource(String tenantIdentifier) {       
      return MultiTenantInitService.getDataSourceMap().get(tenantIdentifier);   
    }
}

class MultiTenantInitService {
    // Other methods + fields

    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName(prop.getProperty("spring.jpa.properties.hibernate.driver-class-name"));
    dataSource.setUrl(prop.getProperty("spring.datasource.url"));
    dataSource.setUsername(prop.getProperty("spring.datasource.username"));
    dataSource.setPassword(prop.getProperty("spring.datasource.password"));
    dataSourceMap.put(ApplicationConstants.DEFAULT_TENANT_ID, dataSource);
}

I expected multi tenant connection provider to connect to the database on first hit of API for single tenant for once only. It should not open the connections again and again for the same tenant. The new connection with data base should only be formed for new tenant. But, if it does open the connections then it should also manage the connection closing on its own.

Damith Damith · Accepted Answer · 2019-05-28T06:39:35

I think what you want is,

spring.datasource.max-active=1

This property limits the maximum active connections to your database. So you can set this property to your DataSource and use it. which means you only have one connection in the connection pool. But there are pros and cons of this approach as if in any means the connection is corrupted, you will have to work on creating a different connection to work on the particular tenant again. So there are pretty good reasons to have a connection pool instead of having just one connection. So the better solution would have a small connection pool as per your requirement. If your application is constantly accessing the database its obvious that you need to have a connection pool. So I would suggest you have small connection pools per tenant.

spring.datasource.max-active=5

Hibernate database per tenant multi-tenant connection provider is creating multiple database connections for single tenant

1 Answers