File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes Deadlock problems with Hibernate/Spring/MS-SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Deadlock problems with Hibernate/Spring/MS-SQL" Watch "Deadlock problems with Hibernate/Spring/MS-SQL" New topic
Author

Deadlock problems with Hibernate/Spring/MS-SQL

Yuval Goldstein
Greenhorn

Joined: Dec 27, 2006
Posts: 18
Hi all,
We just started to test our system with some modest users load and we are experiencing some critical database access related problems.
Our application runs on Jboss 4.0.4 on a Red-Hat Linux machine (but we also see the problems at our development boxes on windows).
We use JDK 5 and Hibernate 3.2 (with annotions), our MS-SQL 2005 database runs (of course) on a windows machine,
We also use a POJO development model with Spring 2.0 that takes care of transaction demarcation (that plugs into Jboss's JTA).
We use the latest jdbc driver from Microsoft.

Some general data: we monitored the datasource connection-pool and it doesnt seem to leak, the memory
doesnt seem to be leaking either also and the cpu (both at the appserver and database machine) is very much un-utilized.


Top exceptions on the score board:

com.microsoft.sqlserver.jdbc.SQLServerException: Server failed to resume the transaction
com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 100) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Our exception trace:

2007-04-05 17:12:08,858 ERROR [org.hibernate.util.JDBCExceptionReporter] Transaction (Process ID 100) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
2007-04-05 17:12:08,858 WARN [org.hibernate.util.JDBCExceptionReporter] SQL Error: 1205, SQLState: 40001
2007-04-05 17:12:08,858 ERROR [org.hibernate.util.JDBCExceptionReporter] Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
2007-04-05 17:12:08,858 WARN [org.hibernate.util.JDBCExceptionReporter] SQL Error: 1205, SQLState: 40001
2007-04-05 17:12:08,858 ERROR [org.hibernate.util.JDBCExceptionReporter] Transaction (Process ID 77) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
2007-04-05 17:12:08,860 WARN [org.hibernate.util.JDBCExceptionReporter] SQL Error: 1205, SQLState: 40001
2007-04-05 17:12:08,858 ERROR [someproject.ticket.service.impl.TicketServiceImpl] [getCountTicketsPerGame] Failed
someproject.common.exceptions.DaoException: org.hibernate.exception.LockAcquisitionException: could not execute query
at someproject.ticket.dao.impl.TicketDaoHibernateImpl.getCountTicketsPerGame(Unknown Source)
at someproject.ticket.service.impl.TicketServiceImpl.getCountTicketsPerGame(Unknown Source)
at sun.reflect.GeneratedMethodAccessor432.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:203)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:162)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:209)
at $Proxy74.getCountTicketsPerGame(Unknown Source)
at sun.reflect.GeneratedMethodAccessor432.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:203)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:162)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:209)
at $Proxy75.getCountTicketsPerGame(Unknown Source)
at sun.reflect.GeneratedMethodAccessor432.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:203)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:162)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:209)
at $Proxy75.getCountTicketsPerGame(Unknown Source)
at someproject.game.view.handler.TicketViewHandler.prepareTicketLeftMenu(Unknown Source)
at sun.reflect.GeneratedMethodAccessor516.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at uk.ltd.getahead.dwr.impl.ExecuteQuery.execute(ExecuteQuery.java:248)
at uk.ltd.getahead.dwr.impl.DefaultExecProcessor.handle(DefaultExecProcessor.java:48)
at uk.ltd.getahead.dwr.impl.DefaultProcessor.handle(DefaultProcessor.java:81)
at uk.ltd.getahead.dwr.AbstractDWRServlet.doPost(AbstractDWRServlet.java:162)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:175)
at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:74)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWorkerThread.java:112)
at java.lang.Thread.run(Thread.java:595)
Caused by: org.hibernate.exception.LockAcquisitionException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:82)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2147)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028)
at org.hibernate.loader.Loader.list(Loader.java:2023)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:393)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:780)
... 60 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 100) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.buildNextRowset(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getPrepExecResponse(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:236)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1668)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2144)
... 68 more

Other exception we receive:

2007-04-10 06:04:24,228 ERROR [someproject.game.service.impl.TicketAuctionServiceImpl] [closeAuctionsSequence] Failed to fetch list of auctions to close

someproject.common.exceptions.DaoException: org.hibernate.exception.SQLGrammarException: could not execute query

at someproject.game.dao.impl.TicketAuctionDaoHibernateImpl.readAuctionsToClose(Unknown Source)

at someproject.game.service.impl.TicketAuctionServiceImpl.closeAuctionsSequence(Unknown Source)

at sun.reflect.GeneratedMethodAccessor513.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:585)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:203)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:162)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:209)

at $Proxy77.closeAuctionsSequence(Unknown Source)

at sun.reflect.GeneratedMethodAccessor513.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:585)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:203)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:162)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:209)

at $Proxy78.closeAuctionsSequence(Unknown Source)

at sun.reflect.GeneratedMethodAccessor513.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:585)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:203)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:162)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:209)

at $Proxy80.closeAuctionsSequence(Unknown Source)

at someproject.game.CloseAuctionJob.execute(Unknown Source)

at org.quartz.core.JobRunShell.run(JobRunShell.java:202)

at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:529)

Caused by: org.hibernate.exception.SQLGrammarException: could not execute query

at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)

at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)

at org.hibernate.loader.Loader.doList(Loader.java:2147)

at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028)

at org.hibernate.loader.Loader.list(Loader.java:2023)

at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:393)

at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)

at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)

at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)

at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)

... 35 more

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Server failed to resume the transaction, desc: 3d00000010.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)

at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getPrepExecResponse(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)

at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)

at org.jboss.resource.adapter.jdbc.CachedPreparedStatement.executeQuery(CachedPreparedStatement.java:90)

at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:236)

at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)

at org.hibernate.loader.Loader.getResultSet(Loader.java:1668)

at org.hibernate.loader.Loader.doQuery(Loader.java:662)

at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)

at org.hibernate.loader.Loader.doList(Loader.java:2144)

... 42 more

2007-04-10 06:04:24,230 ERROR [someproject.game.CloseAuctionJob] [execute] Failed

someproject.common.exceptions.ApplicationException: someproject.common.exceptions.DaoException: org.hibernate.exception.SQLGrammarException: could not execute query

at someproject.game.service.impl.TicketAuctionServiceImpl.closeAuctionsSequence(Unknown Source)

at sun.reflect.GeneratedMethodAccessor513.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:585)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:203)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:162)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:209)

at $Proxy77.closeAuctionsSequence(Unknown Source)

at sun.reflect.GeneratedMethodAccessor513.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:585)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:203)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:162)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:209)

at $Proxy78.closeAuctionsSequence(Unknown Source)

at sun.reflect.GeneratedMethodAccessor513.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:585)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:203)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:162)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:209)

at $Proxy80.closeAuctionsSequence(Unknown Source)

at someproject.game.CloseAuctionJob.execute(Unknown Source)

at org.quartz.core.JobRunShell.run(JobRunShell.java:202)

at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:529)

Caused by: someproject.common.exceptions.DaoException: org.hibernate.exception.SQLGrammarException: could not execute query

at someproject.game.dao.impl.TicketAuctionDaoHibernateImpl.readAuctionsToClose(Unknown Source)

... 34 more

Caused by: org.hibernate.exception.SQLGrammarException: could not execute query

at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)

at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)

at org.hibernate.loader.Loader.doList(Loader.java:2147)

at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028)

at org.hibernate.loader.Loader.list(Loader.java:2023)

at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:393)

at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)

at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)

at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)

at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)

... 35 more

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Server failed to resume the transaction, desc: 3d00000010.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)

at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getPrepExecResponse(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)

at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)

at org.jboss.resource.adapter.jdbc.CachedPreparedStatement.executeQuery(CachedPreparedStatement.java:90)

at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:236)

at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)

at org.hibernate.loader.Loader.getResultSet(Loader.java:1668)

at org.hibernate.loader.Loader.doQuery(Loader.java:662)

at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)

at org.hibernate.loader.Loader.doList(Loader.java:2144)

... 42 more

2007-04-10 06:05:00,010 DEBUG [someproject.game.UpdateGameOnlineInfoJob] [execute] Staring

2007-04-10 06:05:00,011 DEBUG [someproject.common.dao.impl.GenericDaoHibernateImpl] [updateGameOnlineInfo] query:UPDATE GOI SET GOI.Numer_Of_Tickets=tmp_GOI.tickets, GOI.Prize_Pool=tmp_GOI.pool FROM (SELECT G.Game_Id, count(T.Ticket_Id) as tickets ,count(T.Ticket_Id)*GA.Ticket_Price*(1-GA.Game_Profit) as pool FROM GAME_ONLINE_INFO G join GAME GA on G.Game_Id = GA.Game_Id and GA.Status_Id=5 left join TICKET T on g.Game_Id=T.Game_Id and T.Type_Id=1 and T.Status_Id=1 GROUP BY G.Game_Id,GA.Ticket_Price ,GA.Game_Profit) tmp_GOI, GAME_ONLINE_INFO GOI WHERE GOI.Game_Id=tmp_GOI.Game_Id

2007-04-10 06:05:00,012 ERROR [someproject.game.service.impl.GameServiceExtendedImpl] Publish games Error:

someproject.common.exceptions.DaoException: org.hibernate.exception.SQLGrammarException: could not execute update query

at someproject.game.dao.impl.GameExtendedDaoHibernateImpl.publishGames(Unknown Source)

at someproject.game.service.impl.GameServiceExtendedImpl.publishGames(Unknown Source)

at sun.reflect.GeneratedMethodAccessor688.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:585)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:203)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:162)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:209)

at $Proxy102.publishGames(Unknown Source)

at sun.reflect.GeneratedMethodAccessor688.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:585)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:203)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:162)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:209)

at $Proxy103.publishGames(Unknown Source)

at sun.reflect.GeneratedMethodAccessor688.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:585)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:203)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:162)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:209)

at $Proxy109.publishGames(Unknown Source)

at someproject.game.PublishGamesJob.execute(Unknown Source)

at org.quartz.core.JobRunShell.run(JobRunShell.java:202)

at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:529)

Caused by: org.hibernate.exception.SQLGrammarException: could not execute update query

at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)

at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)

at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:84)

at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:396)

at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259)

at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1141)

at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)

... 35 more

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Server failed to resume the transaction, desc: 3d00000010.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)

at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getPrepExecResponse(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)

at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(Unknown Source)

at org.jboss.resource.adapter.jdbc.CachedPreparedStatement.executeUpdate(CachedPreparedStatement.java:95)

at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:251)

at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:75)

... 39 more

2007-04-10 06:05:18,009 DEBUG [someproject.common.OnLoadListener] No rule found for view /include/header.jsp

Any help will be greatly appreciated!

Thanks,
Yuval.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Ignore Hibernate/Spring/JDBC and look at your SQL Server instance first. SQL Server 2005 Profiler has deadlock analysis tools that are pretty useful. Try running your application with a trace running and you should be able to find out what SQL is causing this and get an idea of why.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Yuval Goldstein
Greenhorn

Joined: Dec 27, 2006
Posts: 18
We had a look over there.
We see a simple insert query that performs an exclusive lock. But, we have never used xlock in our sql/hql syntax or other locking hints beside nolock.
The inserts look painfully straight-forward.

We have tried similar load of the same query as a database script and we cant seem to reproduce it but when we run our java integration testing code that uses hibernate and spring (but no jboss or JTA), the problem show up again.


We also tried using several isolation level (on hibernate.connection.isolation) and didnt see any change.

Any possibility it may a driver issue (did someone try the jtds driver for sql 2005?) ?



Any ideas?

Thanks!
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


We see a simple insert query that performs an exclusive lock. But, we have never used xlock in our sql/hql syntax or other locking hints beside nolock.

By default data modification statements in SQL Server use exclusive row locks.

I would be suprised if it were the driver, but there is probably no harm is trying jTDS (its a better driver anyway).

I can't really offer much more help other than general suggestions: Does your application use long transactions? Have you tried setting READ_COMMITTED_SNAPSHOT=ON?
Yuval Goldstein
Greenhorn

Joined: Dec 27, 2006
Posts: 18
First tried it on the connection level and it didnt work.
Then changed the database default behavior and it works.
Also fixed some scope_ident issues.

Thank a million.
Stas Sokolov
Ranch Hand

Joined: Apr 13, 2004
Posts: 117

For MS SQL solution is pretty simple.
To avoid deadlocks never read after you wrote. Point is if you begin transaction then you are in risk of dead locks. So do it this way: first load all the data you need then process this data and then do all updates. None of the update can cause dead lock if you do not read after update. We use this technique in our application and we have no dead locks. Before we had them in many places. Problem that you have to cache updates but it is also good for performance because in case if you rollback transaction it costs nothing because you have not physically updated anything.


Good luck for yourself.
Yuval Goldstein
Greenhorn

Joined: Dec 27, 2006
Posts: 18
Things get a bit more complicated if you perform a write, rely on some database trigger to update/insert more information and then read this information in the same transactions.

Yes, i guess triggers are evil...
juilee rawale
Greenhorn

Joined: Aug 31, 2005
Posts: 7
Hi,
I am also getting similar exception. I am using spring-hibernate to insert/update data and then executing procedures using CallableStatement.

It gives error as

2007-06-20 12:49:02:422|DEBUG - HibernateBreaksDataRepository.saveOrUpdate (54 ) | Hibernate Breaks Data Repository, Going to save Broker Breaks Confirms
2007-06-20 12:49:02:552|WARN - JDBCExceptionReporter.logExceptions (71 ) | SQL Error: 1205, SQLState: 40001
2007-06-20 12:49:02:553|ERROR - JDBCExceptionReporter.logExceptions (72 ) | Your server command (family id #0, process id #896) encountered a deadlock situation. Please re-run your command.

2007-06-20 12:49:03:390|WARN - JDBCExceptionReporter.logExceptions (71 ) | SQL Error: 1205, SQLState: 40001
2007-06-20 12:49:03:391|ERROR - JDBCExceptionReporter.logExceptions (72 ) | Your server command (family id #0, process id #3270) encountered a deadlock situation. Please re-run your command.

2007-06-20 12:49:03:396|DEBUG - HibernateBreaksDataRepository.updateActiveStatus(129 ) | -------------------Active Status updated successfully.
2007-06-20 12:49:03:401|DEBUG - HibernateBreaksDataRepository.saveOrUpdate (54 ) | Hibernate Breaks Data Repository, Going to save Broker Breaks Confirms
2007-06-20 12:49:03:411|ERROR - BreaksDataReceiver.processBreaksData(129 ) | Unable to persist the Broker Breaks Confirms :
org.springframework.dao.CannotAcquireLockException: could not insert: [com.ubsw.lfs.breaksReporting.breaks.domain.BrokerConfirmBreaks]; nested exception is org.hibernate.exception.LockAcquisitionException: could not insert: [com.ubsw.lfs.breaksReporting.breaks.domain.BrokerConfirmBreaks]
Caused by:
org.hibernate.exception.LockAcquisitionException: could not insert: [com.ubsw.lfs.breaksReporting.breaks.domain.BrokerConfirmBreaks]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:82)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:1986)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2405)
at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:37)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:269)
at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:167)
at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:101)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:186)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:175)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:98)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70)
at org.hibernate.impl.SessionImpl.fireSaveOrUpdate(SessionImpl.java:502)
at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:494)
at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:490)
at org.springframework.orm.hibernate3.HibernateTemplate$16.doInHibernate(HibernateTemplate.java:684)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:367)
at org.springframework.orm.hibernate3.HibernateTemplate.saveOrUpdate(HibernateTemplate.java:681)
at com.ubsw.lfs.breaksReporting.breaks.repository.HibernateBreaksDataRepository.saveOrUpdate(HibernateBreaksDataRepository.java:55)
at sun.reflect.GeneratedMethodAccessor48.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:299)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:139)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:161)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at $Proxy1.saveOrUpdate(Unknown Source)
at com.ubsw.lfs.breaksReporting.breaks.service.DefaultBreaksDataService.saveBreaksData(DefaultBreaksDataService.java:28)
at com.ubsw.lfs.breaksReporting.breaks.BreaksDataReceiver.processBreaksData(BreaksDataReceiver.java:126)
at com.ubsw.lfs.breaksReporting.breaks.BreaksDataReceiver$$FastClassByCGLIB$$e590f10e.invoke(<generated>
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:621)
at com.ubsw.lfs.breaksReporting.breaks.BreaksDataReceiver$$EnhancerByCGLIB$$6e7c5448.processBreaksData(<generated>
at sun.reflect.GeneratedMethodAccessor26.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.springframework.util.MethodInvoker.invoke(MethodInvoker.java:270)
at org.springframework.jms.listener.adapter.MessageListenerAdapter.invokeListenerMethod(MessageListenerAdapter.java:451)
at org.springframework.jms.listener.adapter.MessageListenerAdapter.onMessage(MessageListenerAdapter.java:353)
at org.springframework.jms.listener.AbstractMessageListenerContainer.doInvokeListener(AbstractMessageListenerContainer.java:480)
at org.springframework.jms.listener.AbstractMessageListenerContainer.invokeListener(AbstractMessageListenerContainer.java:441)
at org.springframework.jms.listener.AbstractMessageListenerContainer.doExecuteListener(AbstractMessageListenerContainer.java:414)
at org.springframework.jms.listener.AbstractPollingMessageListenerContainer.doReceiveAndExecute(AbstractPollingMessageListenerContainer.java:293)
at org.springframework.jms.listener.AbstractPollingMessageListenerContainer.receiveAndExecute(AbstractPollingMessageListenerContainer.java:239)
at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.invokeListener(DefaultMessageListenerContainer.java:872)
at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.run(DefaultMessageListenerContainer.java:812)
at java.lang.Thread.run(Thread.java:595)
Caused by: com.sybase.jdbc2.jdbc.SybSQLException: Your server command (family id #0, process id #896) encountered a deadlock situation. Please re-run your command.

at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2408)
at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:1844)
at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:201)
at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:182)
at com.sybase.jdbc2.jdbc.SybStatement.executeLoop(SybStatement.java:1535)
at com.sybase.jdbc2.jdbc.SybStatement.execute(SybStatement.java:1527)
at com.sybase.jdbc2.jdbc.SybPreparedStatement.execute(SybPreparedStatement.java:511)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:168)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:1953)
... 47 more
2007-06-20 12:49:03:412|DEBUG - HibernateBreaksDataRepository.executeFIPBLoad(205 ) | ------------------FIPB US Breaks loaded successfully.

ne ideas how can that be resolved.. do i need to change ne hibernate related setting??

pls help

Thanks
Aljaxey Dashkevich
Greenhorn

Joined: Jun 22, 2007
Posts: 2
We use Spring+DAO+Hibernate and we have a lot of long concurrent transactions (thousands of records or even millions). It is difficult to change logic in our app to get modular and small transactions. So we found better way to restart transaction if it failed. For this propose we use Spring AOP. Below example of MethodInterceptor which restart transaction.

In Java:

public class RestartTransactionAdviser implements MethodInterceptor {
private static Logger log = Logger.getLogger(RestartTransactionAdviser.class);

public Object invoke(MethodInvocation invocation) throws Throwable {
return restart(invocation, 1);
}

private Object restart(MethodInvocation invocation, int attempt) throws Throwable {
Object rval = null;
try {
rval = invocation.proceed();
} catch (Exception e) {
Throwable thr = ExceptionUtils.getRootCause(e);
if (thr == null) {
throw e;
}

if (StringUtils.contains(thr.getMessage(), "deadlock")
|| StringUtils.contains(thr.getMessage(), "try restarting transaction")
|| StringUtils.contains(thr.getMessage(),
"failed to resume the transaction")) {
if (attempt > 300) {
throw e;
}
int timeout = RandomUtils.nextInt(2000);
log.warn("Transaction rolled back. Restarting transaction.");
log.debug("Spleep for " + timeout);
log.debug("Restarting transaction: invocation=[" + invocation
+ "], attempt=[" + attempt + "]");
Thread.sleep(timeout);
attempt++;
return restart(invocation, attempt);
} else {
throw e;
}
}
return rval;
}
}


In Spring configuration:


<!-- transaction restart interceptor -->
<bean id="restartTransactionInterceptor" class="RestartTransactionAdviser"/>

<bean id="addressDaoTarget" class="HibernateAddressDao">
<property name="hibernateTemplate">
<ref bean="hibernateTemplate"/>
</property>
</bean>

<bean id="addressDao" class="org.springframework.aop.framework.ProxyFactoryBean">
<property name="target">
<ref local="addressDaoTarget"/>
</property>
<property name="interceptorNames">
<list>
<value>restartTransactionInterceptor</value>
</list>
</property>
</bean>
Yuval Goldstein
Greenhorn

Joined: Dec 27, 2006
Posts: 18
What isolation strategy are you using (on connection and database level?)

Also what kind of locking do you monitor at the database during the deadlock (row, table, page?)

Yuval.
akash kumar
Ranch Hand

Joined: Jun 13, 2007
Posts: 63
Hi Friends!
I am trying to develop a sample application in Hibernate with MSsql server. But I am not able to find any examples. If you can provide or know site that has an example please provide me. It will greatly help me. Thank you!
Abbas Gadhia
Greenhorn

Joined: Sep 11, 2008
Posts: 4
I had the same issue.

My code was somewhat like this.


mutiple threads in different transactions calling this method were deadlocking after the first few completed successfully.

since the first query was "find", successive threads had to wait for their predecessors to complete as the predecessors had acquired "read" locks. so the first few threads completed as they were within timeout limits, but other succeeding threads were not so lucky.

[This is MY assumption of what was happening]

Simple soln: Make the method synchronized if its not too much of a performance hit
[ September 11, 2008: Message edited by: Abbas Gadhia ]
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


Simple soln: Make the method synchronized if its not too much of a performance hit

Simple, but a bit of a hammer to crack a nut, so to speak. Deadlocks are expected (though hopefully exceptional) behaviour in database applications. Rather than synchronising access to a resource that has been carefully desinged to support high levels of concurrency why not retry the thread that lost out in the deadlock? Second time round it may well work fine.

Also:

since the first query was "find", successive threads had to wait for their predecessors to complete as the predecessors had acquired "read" locks. so the first few threads completed as they were within timeout limits, but other succeeding threads were not so lucky.

That doesn't sound like deadlock, that sounds like blocking. There are easier ways to fix blocking. SQL Server's none-too-clever "shared" lock strategy for selects is usually the cause of this. One of the easiest ways to fix it is to analyse the query being produced and add any indices suggested. Its also worth checking (if you already have all the indices suggested) whether your statistics are up to date. Have a search through MSDN; there are loads of articles on these topics.
[ September 12, 2008: Message edited by: Paul Sturrock ]
Abbas Gadhia
Greenhorn

Joined: Sep 11, 2008
Posts: 4
hi are you referring to "Locking optimizer hints" such as
NOLOCK,HOLDLOCK,UPDLOCK,TABLOCK etc???
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

No. You could use lock optimisation hints if you was incontrol of the SQL used in the query. But Hibernate is the one generating this, so unless you start using SQL queries you can't influence this.

These hints are special cases anyway; you wouldn't normally use them. What did you find when you ran the query through the database tuning advisor?
Abbas Gadhia
Greenhorn

Joined: Sep 11, 2008
Posts: 4
Yes. You are right. These hints ARE really special cases.
Sorry for being so late in replying to this thread.

Well, here are some of the optimizations that i did to get rid of these so called 'deadlocks', which eventually turned out to be cases of reduced concurrency on the table as you rightly pointed out.

1) Instead of the original 'find me this row where this=that' and THEN 'delete this row' , i now use 'delete this row where this=that'. This resulted in SIGNIFICANTLY reducing the number of deadlocks. (So removing unnecessary queries helped)
2) After probably 3 months or so, we were faced with similar deadlock issues in another scenario on the same table and no amount of query reductions could be done there. Simple selects or deletes or updates (on a specific where clause) were being 'blocked' just bcos ONE transaction was holding an X lock on ONE row and was not releasing it as the transaction was running long.
A simple soln to this problem was to apply an index on the columns that were used in the where clause, so that now SQL Server would no longer lock the ROW but would now lock JUST the key of the index. So other SELECTS, UPDATES and DELETES would now work simultaneously on OTHER rows despite the fact that another transaction is holding a X lock on ONE (or more) of the rows.
Abbas Gadhia
Greenhorn

Joined: Sep 11, 2008
Posts: 4
since the first query was "find", successive threads had to wait for their predecessors to complete as the predecessors had acquired "read" locks


Slight correction in what i said here [My post dated 12th Sep].
The so called 'read locks' are just held while the select is happening and is released after that. Although, to acquire this lock, the row that you are 'selecting', should have no lock, or another shared lock. If another transaction has got a update or a exclusive lock on that row, this transaction will wait until they are released.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Deadlock problems with Hibernate/Spring/MS-SQL