LazyInitializationException when connected to two databases

Question

Recently I configured second DB connection in my application and everything has been working fine untill I tried to fetch lazily initialized collection from one entity (IncomeTariff). I have two seperated data sources, entity managers etc. What is important is fact that LazyInitializationException occures only when I'm fetching entity from newly added DB. What is more is that when I moved table with IncomeTariff to old DB I was able to get this collection without any problem. I tried many things: adding @Transactional, using Hibernate.initialize() etc. In logs it's just looks like hibernate is closing session too early and I just can't get it work. I hope that code snippets will make this more clear.

Data sources configuration:

    <!-- data source 1 -->
<bean id="dataSource1" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close">
    <property name="driverClassName" value="${mysql.jdbc.driver}" />
    <property name="url" value="${mysql.jdbc.uri}" />
    <property name="username" value="${mysql.jdbc.username}" />
    <property name="password" value="${mysql.jdbc.password}" />
    <property name="maxActive" value="${mysql.jdbc.maxActive}" />
    <property name="testOnBorrow" value="true" />
    <property name="testOnReturn" value="true" />
    <property name="testWhileIdle" value="true" />
    <property name="timeBetweenEvictionRunsMillis" value="30000" />
    <property name="validationQuery" value="select 1" />
    <property name="validationInterval" value="30000" />
    <property name="jdbcInterceptors"
        value="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer" />
    <property name="jmxEnabled" value="true" />
    <property name="logAbandoned" value="true" />
    <property name="maxWait" value="10000" />
    <property name="minEvictableIdleTimeMillis" value="30000" />
    <property name="removeAbandoned" value="true" />
    <property name="removeAbandonedTimeout" value="1200" />
</bean>

<bean id="defaultJdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"
    p:dataSource-ref="dataSource1" primary="true" />

<bean id="defaultNamedParameterJdbcTemplate"
    class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
    <constructor-arg ref="dataSource1" />
</bean>

<!-- data source 2 -->
<bean id="dataSource2" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close">
    <property name="driverClassName" value="${mysql.jdbc.driver}" />
    <property name="url" value="${mysql.ds2.uri}" />
    <property name="username" value="${mysql.ds2.username}" />
    <property name="password" value="${mysql.ds2.password}" />
    <property name="maxActive" value="${mysql.jdbc.maxActive}" />
    <property name="testOnBorrow" value="true" />
    <property name="testOnReturn" value="true" />
    <property name="testWhileIdle" value="true" />
    <property name="timeBetweenEvictionRunsMillis" value="30000" />
    <property name="validationQuery" value="select 1" />
    <property name="validationInterval" value="30000" />
    <property name="jdbcInterceptors"
        value="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer" />
    <property name="jmxEnabled" value="true" />
    <property name="logAbandoned" value="true" />
    <property name="maxWait" value="10000" />
    <property name="minEvictableIdleTimeMillis" value="30000" />
    <property name="removeAbandoned" value="true" />
    <property name="removeAbandonedTimeout" value="1200" />
</bean>

JPA config for old DB:

@Configuration
@EnableJpaRepositories(basePackages = { "pl.application.first.repository"})
@EnableTransactionManagement
public class JpaConfig {

@Autowired
@Qualifier("dataSource1")
private DataSource dataSource;

@Bean
@Primary
public EntityManagerFactory entityManagerFactory() {
    HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    vendorAdapter.setGenerateDdl(false);
    vendorAdapter.setShowSql(false);
    vendorAdapter.setDatabasePlatform("MYSQL");

    LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();
    factoryBean.setDataSource(dataSource);
    factoryBean.setPersistenceUnitName("unit1");
    factoryBean.setJpaProperties(prepareProperties());
    factoryBean.afterPropertiesSet();

    return factoryBean.getObject();
}

@Bean
@Primary
public PlatformTransactionManager transactionManager() {
    JpaTransactionManager txManager = new JpaTransactionManager();
    txManager.setEntityManagerFactory(entityManagerFactory());
    return txManager;
}

private Properties prepareProperties() {
    Properties prop = new Properties();
    prop.put("hibernate.cache.use_second_level_cache", true);
    prop.put("hibernate.cache.use_query_cache", false);
    prop.put("hibernate.cache.region.factory_class", "org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory");
    prop.put("hibernate.cache.provider_class", "org.hibernate.cache.SingletonEhCacheProvider");
    prop.put("javax.persistence.validation.mode", "none");
    return prop;
}

}

JPA config for new DB:

@Configuration
@EnableJpaRepositories(basePackages = {
    "pl.application.second.repository" }, entityManagerFactoryRef = "secondEntityManagerFactory", transactionManagerRef = "secondTransactionManager")
@EnableTransactionManagement
public class NewJpaConfig {

@Autowired
@Qualifier("dataSource2")
private DataSource dataSource;

@Bean(name = "secondEntityManagerFactory")
public EntityManagerFactory entityManagerFactory() {
    HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    vendorAdapter.setGenerateDdl(false);
    vendorAdapter.setShowSql(true);
    vendorAdapter.setDatabasePlatform("MYSQL");

    LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();
    factoryBean.setDataSource(dataSource);
    factoryBean.setJpaProperties(prepareProperties());
    factoryBean.setPackagesToScan("pl.application.operator", "pl.application.common.db.converters");
    factoryBean.setPersistenceUnitName("unit2");
    factoryBean.setPersistenceProviderClass(HibernatePersistenceProvider.class);
    factoryBean.afterPropertiesSet();

    return factoryBean.getObject();
}

@Bean(name = "secondTransactionManager")
public PlatformTransactionManager transactionManager() {
    JpaTransactionManager txManager = new JpaTransactionManager();
    txManager.setEntityManagerFactory(entityManagerFactory());
    return txManager;
}

private Properties prepareProperties() {
    Properties prop = new Properties();
    prop.put("hibernate.cache.use_second_level_cache", true);
    prop.put("hibernate.cache.use_query_cache", false);
    prop.put("hibernate.cache.region.factory_class", "org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory");
    prop.put("hibernate.cache.provider_class", "org.hibernate.cache.SingletonEhCacheProvider");
    prop.put("javax.persistence.validation.mode", "none");
    prop.put("hibernate.connection.autocommit", "false");
    prop.put("javax.persistence.lock.timeout", "90000");
    return prop;
}

}

IncomeTariff class which causes problem:

@Entity
@Table(name = "income_tariff")
public class IncomeTariff extends Entity {

@OneToMany(mappedBy = "incomeTariff")
private List<IncomeTariffEntry> incomeTariffEntries;

public List<IncomeTariffEntry> getIncomeTariffEntries() {
    return incomeTariffEntries;
}

public void setIncomeTariffEntries(List<IncomeTariffEntry> incomeTariffEntries) {
    this.incomeTariffEntries = incomeTariffEntries;
}

    @Override
public int hashCode() {
    int hash = 7;
    hash = 23 * hash + Objects.hashCode(this.getId());
    return hash;
}

@Override
public boolean equals(Object obj) {
    if (obj == null) {
        return false;
    }
    if (getClass() != obj.getClass()) {
        return false;
    }
    final IncomeTariff other = (IncomeTariff) obj;
    if (!Objects.equals(this.getId(), other.getId())) {
        return false;
    }
    return true;
}

}

BillingAccount entity class from old DB which works fine:

@Entity
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "type", discriminatorType = DiscriminatorType.STRING)
@Table(name = "api_billing_account")
public class BillingAccount extends Entity {


@OneToMany(mappedBy = "billingAccount")
private List<User> users = new ArrayList<>();

public List<User> getUsers() {
    return users;
}

public void setUsers(List<User> users) {
    this.users = users;
}

}

And IncomeTariffService, as you can see now method findAllTariffs() doesn't make any sense, I modified it just to show that fetching users from BillingAccount works fine. Repositories are just regular spring-data based repositories:

@Service
@Transactional
public class IncomeTariffService {

@Autowired
private final IncomeTariffRepository incomeTariffRepository; // fetching data from new DB

@Autowired
private BillingAccountRepository billingAccountRepository; //fetching data from old DB

private static final Logger logger = LoggerFactory.getLogger(IncomeTariffService.class);

public List<IncomeTariff> findAllTariffs() {
    BillingAccount findOne = billingAccountRepository.findOne(666000);
    logger.info("TEST");
    logger.info("" + findOne.getUsers().size());
    return null;
}

public List<IncomeTariffEntry> findPositions(Integer tariffId) {
    IncomeTariff findOne = incomeTariffRepository.findOne(tariffId);
    logger.info("TEST");
    List<IncomeTariffEntry> incomeTariffEntries = findOne.getIncomeTariffEntries();
    logger.info("" + incomeTariffEntries.size());
    return incomeTariffEntries;
}

}

And here what is happening in logs when I'm fetching users list frombilling account:

11:33:48.988 [http-nio-8080-exec-33] TRACE org.hibernate.type.CollectionType - Created collection wrapper: [pl.application.billing.model.BillingAccount.users#666000]
11:33:48.989 [http-nio-8080-exec-33] INFO  p.s.a.c.t.s.IncomeTariffService - TEST
11:33:48.990 [http-nio-8080-exec-33] TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [1] as [INTEGER] - [666000]
11:33:48.997 [http-nio-8080-exec-33] INFO  p.s.a.c.t.s.IncomeTariffService - 0
11:33:48.997 [http-nio-8080-exec-33] DEBUG o.s.orm.jpa.JpaTransactionManager - Initiating transaction commit
11:33:48.997 [http-nio-8080-exec-33] DEBUG o.s.orm.jpa.JpaTransactionManager - Committing JPA transaction on EntityManager [org.hibernate.jpa.internal.EntityManagerImpl@72eaf768]
11:33:48.999 [http-nio-8080-exec-33] DEBUG o.s.orm.jpa.JpaTransactionManager - Not closing pre-bound JPA EntityManager after transaction
11:33:49.001 [http-nio-8080-exec-33] DEBUG o.s.o.j.s.OpenEntityManagerInViewFilter - Closing JPA EntityManager in OpenEntityManagerInViewFilter
11:33:49.001 [http-nio-8080-exec-33] DEBUG o.s.o.jpa.EntityManagerFactoryUtils - Closing JPA EntityManager

I'm able to get users collection size and it looks like transaction is closed at the end of the method

Here's what's happening when I'm fetching IncomeTariff (without @Transactional annotation):

13:09:31.285 [http-nio-8080-exec-51] TRACE org.hibernate.type.CollectionType - Created collection wrapper: [pl.application.operator.pricelist.income.IncomeTariff.incomeTariffEntries#1]
13:09:31.288 [http-nio-8080-exec-51] DEBUG o.s.orm.jpa.JpaTransactionManager - Initiating transaction commit
13:09:31.288 [http-nio-8080-exec-51] DEBUG o.s.orm.jpa.JpaTransactionManager - Committing JPA transaction on EntityManager [org.hibernate.jpa.internal.EntityManagerImpl@427c27e2]
13:09:31.316 [http-nio-8080-exec-51] DEBUG o.s.orm.jpa.JpaTransactionManager - Closing JPA EntityManager [org.hibernate.jpa.internal.EntityManagerImpl@427c27e2] after transaction
13:09:31.317 [http-nio-8080-exec-51] DEBUG o.s.o.jpa.EntityManagerFactoryUtils - Closing JPA EntityManager
13:09:31.317 [http-nio-8080-exec-51] INFO  p.s.a.c.t.s.IncomeTariffService - TEST
13:09:31.342 [http-nio-8080-exec-51] ERROR p.s.a.r.c.e.GlobalExceptionHandler - failed to lazily initialize a collection of role: pl.application.operator.pricelist.income.IncomeTariff.incomeTariffEntries, could not initialize proxy - no Session

and with @Transactional annotation:

12:24:14.599 [http-nio-8080-exec-42] TRACE org.hibernate.type.CollectionType - Created collection wrapper: [pl.application.operator.pricelist.income.IncomeTariff.incomeTariffEntries#1]
12:24:14.600 [http-nio-8080-exec-42] DEBUG o.s.orm.jpa.JpaTransactionManager - Initiating transaction commit
12:24:14.600 [http-nio-8080-exec-42] DEBUG o.s.orm.jpa.JpaTransactionManager - Committing JPA transaction on EntityManager [org.hibernate.jpa.internal.EntityManagerImpl@4b5b7e77]
12:24:14.601 [http-nio-8080-exec-42] DEBUG o.s.orm.jpa.JpaTransactionManager - Closing JPA EntityManager [org.hibernate.jpa.internal.EntityManagerImpl@4b5b7e77] after transaction
12:24:14.601 [http-nio-8080-exec-42] DEBUG o.s.o.jpa.EntityManagerFactoryUtils - Closing JPA EntityManager
12:24:14.601 [http-nio-8080-exec-42] DEBUG o.s.orm.jpa.JpaTransactionManager - Resuming suspended transaction after completion of inner transaction
12:24:14.601 [http-nio-8080-exec-42] INFO  p.s.a.c.t.s.IncomeTariffService - TEST
12:24:14.604 [http-nio-8080-exec-42] DEBUG o.s.orm.jpa.JpaTransactionManager - Initiating transaction rollback
12:24:14.604 [http-nio-8080-exec-42] DEBUG o.s.orm.jpa.JpaTransactionManager - Rolling back JPA transaction on EntityManager [org.hibernate.jpa.internal.EntityManagerImpl@5e7c4044]
12:24:14.606 [http-nio-8080-exec-42] DEBUG o.s.orm.jpa.JpaTransactionManager - Not closing pre-bound JPA EntityManager after transaction
12:24:14.612 [http-nio-8080-exec-42] ERROR p.s.a.r.c.e.GlobalExceptionHandler - failed to lazily initialize a collection of role: pl.application.operator.pricelist.income.IncomeTariff.incomeTariffEntries, could not initialize proxy - no Session

Transaction is closed before "TEST" is even logged and then it's just impossible to get the collection from entity. As I mentioned before, when I moved income_tariff to the old DB everything was working, so maybe there is something wrong with my configuration but I'm struggling with this for two days now and I'm out of ideas.

Answer 1

Since you're using 2 transaction managers you might look into specifying which one you want to use within the @Transactional annotation( See http://docs.spring.io/spring/docs/current/spring-framework-reference/htmlsingle/#tx-multiple-tx-mgrs-with-attransactional )

For example I'd remove the @Transactional at the class level and add transaction manager specific @Transactional() above each find method.

Like :

@Transactional("secondTransactionManager)
public List<IncomeTariff> findAllTariffs() {}

With transaction at the service level, if the collection is reached outside of the transactional boundaries you will get such exception. Try set the collection non lazy or move the transaction higher up such as a business layer.

Hope this helps

A.