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
Last activity: Jul 25, 2022, 12:16 AM