Spring Boot + Hibernate Multi-tenancy: @Transactional not working

Question

I have a Spring Boot 2 + Hibernate 5 Multi-tenant application connecting to a single PostgreSQL database. I have set this up according to these guides:

This works fine as long as I set the tenantId in a Filter or Interceptor before hitting the Controller endpoints.

However, I need to set the tenant inside the controller, as follows:

@RestController
public class CarController {
    @GetMapping("/cars")
    @Transactional
    public List<Car> getCars(@RequestParam(name = "schema") String schema) {
        TenantContext.setCurrentTenant(schema);
        return carRepo.findAll();
    }
}

But at this point a Connection has already been retrieved (for the public schema) and setting the TenantContext has no effect.

I figured @Transactional was supposed to force the method to be run in a separate transaction, and thus the creation of the Hibernate Session would be postponed until the carRepo.findAll() method was called. This does not seem to be the case, since @Transactional does nothing.

This leads me to 2 questions:

How can I defer the creation of a Hibernate Session during a request until I managed to set the correct tenant based on some logic not available in a Filter/Interceptor? @Transactional does not seem to do anything.
How can I talk to different schemas in the same request or block of code? Imagine 1 repository being only available in the public schema and 1 being in a tenant schema.

Other relevant classes (only relevant parts are shown!)

MultiTenantConnectionProviderImpl.java:

@Component
public class MultiTenantConnectionProviderImpl implements MultiTenantConnectionProvider {
    @Override
    public Connection getConnection(String tenantIdentifier) throws SQLException {
        final Connection connection = getAnyConnection();
        connection.setSchema(tenantIdentifier);
        return connection;
    }

    @Override
    public void releaseConnection(String tenantIdentifier, Connection connection) throws SQLException {
        connection.setSchema(null);
        releaseAnyConnection(connection);
    }
}

TenantIdentifierResolver.java

@Component
public class TenantIdentifierResolver implements CurrentTenantIdentifierResolver {

    @Override
    public String resolveCurrentTenantIdentifier() {
        String tenantId = TenantContext.getCurrentTenant();
        return (tenantId != null) ? tenantId : "public";
    }
    @Override
    public boolean validateExistingCurrentSessions() {
        return true;
    }
}

HibernateConfig.java:

@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<>(jpaProperties.getProperties());
        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.example");
        em.setJpaVendorAdapter(jpaVendorAdapter());
        em.setJpaPropertyMap(properties);
        return em;
    }
}

https://stackoverflow.com/a/47776205/8918302 possible solution — Suban Dhyako, Oct 31 '19 at 08:59

score 5 · Answer 1 · answered May 03 '19 at 21:54

While @tan-mally explains my issue with @Transaction clearly and how to solve it, the actual problem was caused by a different Spring Boot configuration default: spring.jpa.open-in-view=true

When setting this to false, I don't need the @Transaction annotation at all. The retrieval of the Connection will be deferred until it hits the repo's findAll() method, after calling TenantContext.setCurrentTenant(schema).

Apparently spring.jpa.open-in-view=true always eagerly creates a Hibernate session around the entire request.

Hopefully this helps the next person running into this issue. I was only hinted at this property by a warning that pops up during startup about this default setting. See this Github issue for a discussion on this topic.

score 4 · Accepted Answer · answered May 03 '19 at 18:20

In spring the transaction is called when we call the method from another bean class .In this case, if you move the findAll call to a service class and add the transaction on that method then the behavior would be as you expect. The transaction will start when you call the service method by then the schema value is set on TenantContext

Note: Remove the @Transactional from Controller. Since you are doing a read it is better to add readonly property to @Transactional added to service method 'getAllCars()'

@RestController
public class CarController {

    @GetMapping("/cars")
    public List<Car> getCars(@RequestParam(name = "schema") String schema) {
        TenantContext.setCurrentTenant(schema);
        return carService.getAllCars();
    }
}

@Service
public class CarService{

    @Transactional(readOnly=true)
    public List<Car> getAllCars() {
        return carRepo.findAll();
    }
}

Spring Boot + Hibernate Multi-tenancy: @Transactional not working

2 Answers2

Linked