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:

• http://www.greggbolinger.com/tenant-per-schema-with-spring-boot/
• https://blog.aliprax.me/schema-based-multitenancy/
• https://fizzylogic.nl/2016/01/24/make-your-spring-boot-application-multi-tenant-aware-in-2-steps/

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:

1. 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.
2. 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;
    }
}

Answer 1

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.