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]
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.
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.