New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Uncategorized data access exception thrown for deadlock [SPR-2617] #7306

Closed
spring-issuemaster opened this Issue Sep 20, 2006 · 8 comments

Comments

Projects
None yet
1 participant
@spring-issuemaster
Copy link
Collaborator

spring-issuemaster commented Sep 20, 2006

Ilanchezhian opened SPR-2617 and commented

During update by using JdbcTemplate.update(String sql, Object[] args), I encounter exception due to deadlock. This is thrown as an SQLException. Exception log is

exception: PreparedStatementCallback; uncategorized SQLException for SQL [UPDATE EMP_INFO SET ORG_NAME=?, EMP_MAME=?, WHERE EMP_ID=?]; SQL state [40001]; error code [1205]; Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.; nested exception is java.sql.SQLException: Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

In the above scenario, I encounter couple of issues:

  • From application program code there is no way I can capture this exception (SQLException), and provide the information message. To do this I am forced to capture root exception (Exception) and provide necessary information message.
  • Currently JdbcTemplate.update() method throws only 'DataAccessException' but when we look at source code we can see it internally executes PreparedStatementCallback.doInPreparedStatement() method, this method throws SQLException. But as root JdbcTemplate.update() throws only 'DataAccessException' so in this case I am not able to catch any SQLException. Please let me know is this a practice that I should not catch any SQLException in my application code?

Affects: 2.0.6

Issue Links:

  • #6388 SQL Server Error Code 1205 = DeadlockLoser ("duplicates")
@spring-issuemaster

This comment has been minimized.

Copy link
Collaborator

spring-issuemaster commented Jun 27, 2007

Erik Hoogeveen commented

You can capture the SQLException by implementing the SQLExceptionTranslator interface somewhere. This implementation should translate the SQL exception to a specific exception that extends from DataAccessException using the SQL error code. For example in this case it should translate SQLException whit error code 1205 to a DeadlockLoserDataAccessException.
Even better, maybe someone somehwere on the internet has already written a SQLExceptionTranslator for the database you're using, that would save you a lot of effort.

Once the translation is in place you can catch the exceptions you're interested in.

btw, since this is no bug but a usage issue. Please post it to the support forum (http://forum.springframework.org/) next time around. JIRA is meant for bug reports. Not for support.

@spring-issuemaster

This comment has been minimized.

Copy link
Collaborator

spring-issuemaster commented Jun 27, 2007

Juergen Hoeller commented

Indeed, you should be able to catch DataAccessException and check the underlying SQLException root cause there (simply do a getCause call).

As for the exception being thrown as uncategorized, we probably just need to add the specific error code to our default sql-error-codes.xml file (in the org.springframework.jdbc.support package). You may also adapt that file yourself and put it into the root of the classpath; the default exception translator will pick it up there.

Which database are you using there? Let me know; I'll try to extend its deadlock error codes in our default sql-error-codes.xml file then. For that reason, I'm turning this issue into an enhancement request.

Juergen

@spring-issuemaster

This comment has been minimized.

Copy link
Collaborator

spring-issuemaster commented Jun 28, 2007

Donnchadh O Donnabhain commented

Looks like MS SQL server or Sybase. This bug report seems almost identical to #6388.
Looking at the sql-error-codes.sql provided with spring 2.0.6 it appears that error code 1205 is being transalated.

@spring-issuemaster

This comment has been minimized.

Copy link
Collaborator

spring-issuemaster commented Jul 2, 2007

Juergen Hoeller commented

Indeed - I guess we need more details on why this is not automatically translated in the given scenario then...

Juergen

@spring-issuemaster

This comment has been minimized.

Copy link
Collaborator

spring-issuemaster commented Sep 19, 2009

John Nutting commented

We are also seeing this behavior in our application using JPA with Hibernate and the Merlia driver.

In the stack trace below, it looks like the correct error code (1205) is making its way to the SQLStateConverter. I started to wonder whether our DatabaseProductName is not being correctly set to "Microsoft SQL Server", but other exceptions appear to be correctly translated, such as dataIntegrityViolationCodes, so this does not seem likely.

Stack trace excerpt:

com.hare.feed.FeedProcessFatalException: org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on Hibernate data access: SQLException for SQL [select listing0_.ListingGUID as Listing ... order by listing0_.createDate desc]; SQL state [40000]; error code [1205]; could not execute query; nested exception is org.hibernate.exception.GenericJDBCException: could not execute query at com.hare.feed.AbstractFeedProcess.processXMLListing(AbstractFeedProcess.java:1285)
...
Caused by: org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on Hibernate data access: SQLException for SQL [select listing0_.ListingGUID
...
Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
...
Caused by: com.inet.tds.ab: Msg 1205, Level 13, State 51, Line 1, Sqlstate 40000
[AUS-DEV-SQL01\STG]Transaction (Process ID 86) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at com.inet.tds.bb.a(Unknown Source)

Pertinent Spring beans:

<!-- JPA EntityManagerFactory -->
<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
		p:dataSource-ref="dataSource">
	<property name="jpaVendorAdapter">
		<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"
			p:databasePlatform="com.hare.platform.hibernate.UnicodeSQLServerDialect"
			p:generateDdl="false" 
			p:showSql="false"
		/>
	</property>
</bean>

<!-- Merlia pooled datasource -->
<bean id="dataSource" class="com.inet.tds.PDataSource" lazy-init="true">
	...
</bean>
@spring-issuemaster

This comment has been minimized.

Copy link
Collaborator

spring-issuemaster commented Sep 19, 2009

John Nutting commented

I left out one thing:

package com.hare.platform.hibernate;
public class UnicodeSQLServerDialect extends org.hibernate.dialect.SQLServerDialect {
...
}

@spring-issuemaster

This comment has been minimized.

Copy link
Collaborator

spring-issuemaster commented Sep 19, 2009

John Nutting commented

I left out the part of the stack trace where the Spring translation is being done. Here's a more complete one:

com.hare.feed.FeedProcessFatalException: org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on Hibernate data access: SQLException for SQL [select listing0_.ListingGUID as ListingG1_3_0_, ... order by listing0_.createDate desc]; SQL state [40000]; error code [1205]; could not execute query; nested exception is org.hibernate.exceptio
n.GenericJDBCException: could not execute query
at com.hare.feed.AbstractFeedProcess.processXMLListing(AbstractFeedProcess.java:1285)
at com.hare.feed.AbstractFeedProcess.access$000(AbstractFeedProcess.java:97)
at com.hare.feed.AbstractFeedProcess$ProcessListingsCallable.call(AbstractFeedProcess.java:2531)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
at java.util.concurrent.FutureTask.run(FutureTask.java:138)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
at java.util.concurrent.FutureTask.run(FutureTask.java:138)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:619)
Caused by: org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on Hibernate data access: SQLException for SQL [select listing0_.ListingGUID as ListingG1_3_0_, ... order by listing0_.createDate desc]; SQL state [40000]; error code [1205]; could not execute query; nested exception is org.hibernate.exception.GenericJDBCException: could not execute query
at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:642)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:95)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:352)
at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:62)
at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:212)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:146)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy67.findActiveListing(Unknown Source)
at com.hare.feed.AbstractFeedProcess.processXMLListing(AbstractFeedProcess.java:940)
... 10 more
Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2231)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
at org.hibernate.loader.Loader.list(Loader.java:2120)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:361)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1148)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:67)
at com.hare.domain.dao.impl.ListingDaoJpaImpl.findActiveListing(ListingDaoJpaImpl.java:44)
at sun.reflect.GeneratedMethodAccessor84.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:138)
... 18 more
Caused by: com.inet.tds.ab: Msg 1205, Level 13, State 51, Line 1, Sqlstate 40000
[AUS-DEV-SQL01\STG]Transaction (Process ID 86) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at com.inet.tds.bb.a(Unknown Source)
at com.inet.tds.l.b(Unknown Source)
at com.inet.tds.l.a(Unknown Source)
at com.inet.tds.l.a(Unknown Source)
at com.inet.tds.ae.g(Unknown Source)
at com.inet.tds.ae.executeQuery(Unknown Source)
at com.inet.pool.PoolPreparedStatement.executeQuery(Unknown Source)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
at org.hibernate.loader.Loader.doQuery(Loader.java:697)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2228)
... 34 more

@spring-issuemaster

This comment has been minimized.

Copy link
Collaborator

spring-issuemaster commented Oct 26, 2009

Thomas Risberg commented

In the Hibernate/JPA case it is Hibernate that does the translation into a org.hibernate.exception.GenericJDBCException which in turn gets translated to a org.springframework.orm.hibernate3.HibernateJdbcException which extends UncategorizedDataAccessException. So the sql-error-codes.sql is never used for this scenario. The original question was regarding JDBC usage and in this case the sql-error-codes.xml is used to translate and the resulting exception should be a DeadlockLoserDataAccessException.

Original posting is about 3 years old now and we have added the 1205 code to the translation. I think it's safe to finally close this issue.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment