I'm working on a Spring Boot application that uses Hibernate and mysql for persistence. I get the following errors randomly from @Transactional SELECT queries called from @Service layer.
Could not commit Hibernate transaction; nested exception is org.hibernate.TransactionException: commit failed,
java.sql.SQLException: Can't call commit when autocommit=true
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:930)
com.mysql.jdbc.ConnectionImpl.commit(ConnectionImpl.java:1602)
sun.reflect.GeneratedMethodAccessor148.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:498)
org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:71)
org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer.invoke(ResetAbandonedTimer.java:63)
org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:71)
org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
org.apache.tomcat.jdbc.pool.interceptor.ConnectionState.invoke(ConnectionState.java:153)
org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
org.apache.tomcat.jdbc.pool.TrapException.invoke(TrapException.java:41)
org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:80)
com.sun.proxy.$Proxy160.commit(Unknown Source)
org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doCommit(JdbcTransaction.java:112)", " [wrapped]
org.hibernate.TransactionException: unable to commit against JDBC connection
org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doCommit(JdbcTransaction.java:116)
org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:180)", " [wrapped] org.hibernate.TransactionException: commit failed
org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:187)
org.springframework.orm.hibernate4.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:584)", " [wrapped] org.springframework.transaction.TransactionSystemException: Could not commit
Hibernate transaction; nested exception is org.hibernate.TransactionException: commit failed
org.springframework.orm.hibernate4.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:588)
org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:761)
org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:730)
org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:484)
org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:291)
org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)
com.sun.proxy.$Proxy313.getLibrary(Unknown Source)
The service method is annotated with @Transactional and it calls two different repositories that use different SessionFactory instances. (No need to annotate it with @Transactional, I guess, but that does not explain why it fails, right?). I think that may be the root cause of the error but I'm unable to consistently reproduce the error. Seems like depending on the load or something else that I could not spot yet. I'd love to hear any idea about the possible cause, suggestions on how to debug or further reading.
Already read those:
- Spring: HibernateTransactionManager handling multiple datasources
- https://docs.spring.io/spring-framework/docs/3.0.0.M4/reference/html/ch10s03.html
- Spring-Boot How to properly inject javax.validation.Validator
SessionFactories
There are two sessionFactories for the same mysql connection. One session perform lifecycle event validation while other does not.
@Bean
public SessionFactory sessionFactory() {
LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();
//...
return sessionFactory.getObject();
}
}
@Bean(name = "otherSessionFactory")
public SessionFactory otherSessionFactory() {
LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();
//...
hibernateProperties.setProperty("javax.persistence.validation.mode", "none");
return sessionFactory.getObject();
}
}
PlatformTransactionManager
There is only one PlatformTransactionManager using the unnamed/default sessionFactory. I'm not sure how the otherSessionFactory's sessions are working without setting the PlatformTransactionManager. In transactional test methods, the operations are not rolled back, that's why I assume otherSessionFactory's sessions do not have a PlatformTransactionManager.
@Autowired
protected SessionFactory sessionFactory;
//...
@Bean
public PlatformTransactionManager txManager() {
return new HibernateTransactionManager(sessionFactory);
}
Repositories
@Bean
public BooksRepository booksRepository(@Qualifier("otherSessionFactory") sessionFactory) {
return new HibernateBooksRepository(sessionFactory);
}
@Bean
public AuthorsRepository authorsRepository(sessionFactory) {
return new HibernateAuthorsRepository(sessionFactory);
}
Service
@Transactional
public Library getLibrary() {
List<Author> authors = authorsRepository.getAll();
List<Book> books = booksRepository.getAll(); // throws Exception
//...
}
