I am working on a spring boot application that uses ucanaccess 3.0.7 to connect to an Microsoft Access 2010 database. I am having no problems reading from the db, but I am seeing this 'invalid transaction state: read-only SQL-transaction' error pop up occasionally when trying to save to the db.
I can reproduce this error by navigating to my web page with my .accdb file closed; then when I attempt to save to the db I will receive the error. If I open the .accdb file and try to save again, it will work.
Next, if I reload the web page with the .accdb file open and try to save to the db; I will receive the read-only error again. If I close the .accdb file and try to save again it will work.
Not sure what could be the problem here, hoping someone can help me out.
ERROR:
WARN 8360 --- [io-8080-exec-10] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: -3706, SQLState: 25006
ERROR 8360 --- [io-8080-exec-10] o.h.engine.jdbc.spi.SqlExceptionHelper : UCAExc:::3.0.7 invalid transaction state: read-only SQL-transaction
ERROR 8360 --- [io-8080-exec-10] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.orm.jpa.JpaSystemException: could not prepare statement; nested exception is org.hibernate.exception.GenericJDBCException: could not prepare statement] with root cause
org.hsqldb.HsqlException: invalid transaction state: read-only SQL-transaction
at org.hsqldb.error.Error.error(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.error.Error.error(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.Session.checkReadWrite(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.StatementDMQL.checkAccessRights(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.StatementDML.<init>(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.ParserDML.compileUpdateStatement(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.ParserCommand.compilePart(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.ParserCommand.compileStatement(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.Session.compileStatement(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.StatementManager.compile(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.Session.execute(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
at net.ucanaccess.jdbc.UcanaccessConnection.prepareStatement(UcanaccessConnection.java:524) ~[ucanaccess-3.0.7.jar:3.0.7]
at sun.reflect.GeneratedMethodAccessor73.invoke(Unknown Source) ~[na:na]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_101]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_101]
at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126) ~[tomcat-jdbc-8.5.6.jar:na]
at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108) ~[tomcat-jdbc-8.5.6.jar:na]
at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81) ~[tomcat-jdbc-8.5.6.jar:na]
at com.sun.proxy.$Proxy84.prepareStatement(Unknown Source) ~[na:na]
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$1.doPrepare(StatementPreparerImpl.java:87) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:172) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareStatement(StatementPreparerImpl.java:78) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl.buildBatchStatement(AbstractBatchImpl.java:136) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl.getBatchStatement(AbstractBatchImpl.java:125) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3025) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:2961) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3341) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.action.internal.EntityUpdateAction.execute(EntityUpdateAction.java:145) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:582) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:456) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:337) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1282) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:465) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:2963) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2339) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:485) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:147) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$100(JdbcResourceLocalTransactionCoordinatorImpl.java:38) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:231) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:65) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.jpa.internal.TransactionImpl.commit(TransactionImpl.java:61) ~[hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:517) ~[spring-orm-4.3.4.RELEASE.jar:4.3.4.RELEASE]
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:761) ~[spring-tx-4.3.4.RELEASE.jar:4.3.4.RELEASE]
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:730) ~[spring-tx-4.3.4.RELEASE.jar:4.3.4.RELEASE]
at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:504) ~[spring-tx-4.3.4.RELEASE.jar:4.3.4.RELEASE]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:292) ~[spring-tx-4.3.4.RELEASE.jar:4.3.4.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) ~[spring-tx-4.3.4.RELEASE.jar:4.3.4.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.4.RELEASE.jar:4.3.4.RELEASE]
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136) ~[spring-tx-4.3.4.RELEASE.jar:4.3.4.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.4.RELEASE.jar:4.3.4.RELEASE]
at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133) ~[spring-data-jpa-1.10.5.RELEASE.jar:na]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.4.RELEASE.jar:4.3.4.RELEASE]
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) ~[spring-aop-4.3.4.RELEASE.jar:4.3.4.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.4.RELEASE.jar:4.3.4.RELEASE]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) ~[spring-aop-4.3.4.RELEASE.jar:4.3.4.RELEASE]
at com.sun.proxy.$Proxy112.save(Unknown Source) ~[na:na]
EDIT
After further testing, Hibernate seems to be locking my db after it's first read transactions are complete.
My Controller
@RequestMapping("/")
public String index(Model model) {
List<SystemLuEntity> systems = systemLuRepository.findAll();
List<ControlLuEntity> entities = controlLuRepository.findAll();
model.addAttribute("items", entities);
model.addAttribute("systems", systems);
return "index";
}
Modified Controller to test saving to DB (Receives read-only error)
Saving the entity after findAll()
@RequestMapping("/")
public String index(Model model) {
List<SystemLuEntity> systems = systemLuRepository.findAll();
List<ControlLuEntity> entities = controlLuRepository.findAll();
model.addAttribute("items", entities);
model.addAttribute("systems", systems);
ControlStatusEntity entity = new ControlStatusEntity();
Random random = new Random();
entity.setId(25);
entity.setComment(String.valueOf(random.nextInt()));
controlStatusRepository.save(entity);
return "index";
}
Modified Controller to test saving to DB (saves successfully the first time, fails after)
Saving the entity before findAll()
@RequestMapping("/")
public String index(Model model) {
ControlStatusEntity entity = new ControlStatusEntity();
Random random = new Random();
entity.setId(25);
entity.setComment(String.valueOf(random.nextInt()));
controlStatusRepository.save(entity);
List<SystemLuEntity> systems = systemLuRepository.findAll();
List<ControlLuEntity> entities = controlLuRepository.findAll();
model.addAttribute("items", entities);
model.addAttribute("systems", systems);
return "index";
}
Modified Controller to test saving to DB (saves successfully every time)
Just saving; not calling findAll()
@RequestMapping("/")
public String index(Model model) {
ControlStatusEntity entity = new ControlStatusEntity();
Random random = new Random();
entity.setId(25);
entity.setComment(String.valueOf(random.nextInt()));
controlStatusRepository.save(entity);
return "index";
}
Big thanks to Gord Thompson for helping me figure this out. He ended up pointing me to this SO answer which solved my issue.
Solution
Hibernate was locking my DB when I performed my first read, so the solution was to add
@Transactional(isolation = Isolation.READ_UNCOMMITTED)to my repository and everything now works as it should.