UCAExc 3.0.7 invalid transaction state: read-only SQL-transaction Sometimes when saving to .accdb

Question

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";
    }

It looks like Hibernate is creating an HSQLDB transaction that is being forced to read-only mode for some reason, and fooling with the .accdb is causing UCanAccess to re-build the backing database, hence terminating that transaction. Perhaps try explicitly asking Hibernate to commit immediately before trying to apply the update to your database. — Gord Thompson, Dec 20 '16 at 21:47
Thanks for the comment, Gord. I'll try this and let you know what happens. — Zack W, Dec 21 '16 at 14:35
@GordThompson I have added some additional findings to my question. — Zack W, Dec 22 '16 at 18:42
It sounds like the default transaction isolation may be too strict for your application. An answer [here](http://stackoverflow.com/a/16162426/2144390) mentions a property that can be set to control the default transaction isolation that Hibernate uses. Perhaps try setting `1` (READ UNCOMMITTED) and see if that helps. — Gord Thompson, Dec 22 '16 at 19:41
That's the problem, Thanks a ton! That property seems to be outdated, but adding `@Transactional(isolation = Isolation.READ_UNCOMMITTED)` to my repository has fixed the problem. Do you want to make an answer so I can accept it? — Zack W, Dec 22 '16 at 20:09
You did all the hard work, so go ahead and post an answer and I'll upvote it. — Gord Thompson, Dec 22 '16 at 23:44

score 2 · Accepted Answer · edited May 23 '17 at 11:46

2

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.

edited May 23 '17 at 11:46

Community

1
1

answered Dec 23 '16 at 13:44

Zack W

146
6

I am using Spring Boot with DataJPA and it seems that this solution is not working . Link to project https://github.com/mubasherusman/timesheet – Mubasher Apr 02 '18 at 16:09

UCAExc 3.0.7 invalid transaction state: read-only SQL-transaction Sometimes when saving to .accdb

EDIT

1 Answers1

Solution