Sample Header Ad - 728x90

SQLGrammarException: could not extract ResultSet; Snapshot isolation transaction failed in database

1 vote
0 answers
2906 views
I am trying to reproduce and fix the recently caught exception: [jdbc.spi.SqlExceptionHelper] ? Snapshot isolation transaction failed in database 'config_db' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is disallowed because the metadata is not versioned. A concurrent update to metadata can lead to inconsistency if mixed with snapshot isolation. org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:280) ~[spring-orm-5.1.20.RELEASE.jar:5.1.20.RELEASE] [stdout] at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:254) ~[spring-orm-5.1.20.RELEASE.jar:5.1.20.RELEASE] [stdout] at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:531) ~[spring-orm-5.1.20.RELEASE.jar:5.1.20.RELEASE] [stdout] at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61) ~[spring-tx-5.1.20.RELEASE.jar:5.1.20.RELEASE] [stdout] at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242) ~[spring-tx-5.1.20.RELEASE.jar:5.1.20.RELEASE] [stdout] at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:154) ~[spring-tx-5.1.20.RELEASE.jar:5.1.20.RELEASE] [stdout] at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:140) ~[spring-data-jpa-1.11.23.RELEASE.jar:?] [stdout] at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$ExposeRepositoryInvocationInterceptor.invoke(CrudMethodMetadataPostProcessor.java:347) ~[spring-data-jpa-1.11.23.RELEASE.jar:?] [stdout] at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93) ~[spring-aop-5.1.20.RELEASE.jar:5.1.20.RELEASE] [stdout] at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:57) ~[spring-data-commons-1.13.23.RELEASE.jar:?] [stdout] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.1.20.RELEASE.jar:5.1.20.RELEASE] [stdout] at com.sun.proxy.$Proxy280.findById(Unknown Source) ~[?:?] The generated query (**note:** I removed the column names intentionally) is run within read-only transaction that uses the readonlyTransactionManager which routes the query to SQL Server replica node. The used isolation level is Isolation.DEFAULT: select * from config_db.dbo.Program programdmo0_ left outer join config_db.dbo.SupplementalSection supplement1_ on programdmo0_.Id = supplement1_.ProgramId left outer join config_db.dbo.QuestionBlock questionbl2_ on supplement1_.Id = questionbl2_.SectionId left outer join config_db_db.dbo.Question questions3_ on questionbl2_.Id = questions3_.QuestionBlockId left outer join config_db_db.dbo.QuestionDetails questionde4_ on questions3_.Id = questionde4_.QuestionId where programdmo0_.Id = ? DBA confirmed that DB isolation level is set to READ COMMITTED. Appreciate if anyone can help me to understand why this exception is thrown if the SNAPSHOT isolation level is not enabled?
Asked by Eager (111 rep)
Jul 24, 2022, 08:00 PM
Last activity: Jul 25, 2022, 12:16 AM