spring - Spring事务中发生死锁时调度程序停止

Question

我开发了一个监控应用程序，它使用 Spring-3.0 Hibernate-3.6.5 和 SpringHibernateTemplate 作为 DAO。数据库 SQL Server 2008。此应用程序每天都有长时间运行的事务。

@Repository("areaDaoHibernate")
public class AreaDAO implements IArea {
       protected HibernateTemplate template = null;

       @Autowired @Required
       public void setSessionFactory(SessionFactory sessionFactory) {
              template = new HibernateTemplate(sessionFactory);
       }

       // basic dao method
}

此应用程序通过 springbeans 进行了一些调度(实现 Runable)。

@Component("dailyTask")
@Scope("prototype")
public class DailyTask implements Runnable {

@Override
       public void run() {
              // running task
       }
}

此应用使用 Apache DBCP 进行池化。

   <bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
              <property name="dataSource">
                     <ref bean="CustodyDataSource" />
              </property>
…
       </bean>

<bean id = "CustodyDataSource"
      class             = "org.apache.commons.dbcp.BasicDataSource"
      p:driverClassName = "${jdbc1.driverClassName}"
      p:url             = "${jdbc1.url}"
      p:username        = "${jdbc1.username}"
      p:password        = "${jdbc1.password}"
      p:maxIdle         = "${jdbc1.maxIdle}"
      p:maxActive       = "${jdbc1.maxActive}" 
      p:validationQuery = "${jdbc1.validationQuery}" 
      p:testWhileIdle   = "${jdbc1.testWhileIdle}" />
<bean id = "transactionManager"
      class = "org.springframework.orm.hibernate3.HibernateTransactionManager"
      p:sessionFactory-ref = "sessionFactory" />

当这个应用程序死锁时，我遇到了问题，调度停止运行。
错误显示:

2012-12-27 12:57:20,861  WARN (JDBCExceptionReporter:233) - SQL Error: 1205, SQLState: 40001
2012-12-27 12:57:20,862 ERROR (JDBCExceptionReporter:234) - Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
2012-12-27 12:57:20,866 ERROR (DailyTask:238) - org.springframework.dao.CannotAcquireLockException: could not update: [com.btpn.custody.entity.MasterData#00176ed4-b7a2-4da5-a266-ffaab3654050]; SQL [update T_CIF set __UPDATED_DATE=?, STATUS_MD=? where __SID=?]; nested exception is org.hibernate.exception.LockAcquisitionException: could not update: [com.btpn.custody.entity.MasterData#00176ed4-b7a2-4da5-a266-ffaab3654050]
            at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:633)
            at org.springframework.orm.hibernate3.HibernateTransactionManager.convertHibernateAccessException(HibernateTransactionManager.java:793)
            at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:664)
            at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:754)
            at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:723)
            at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:393)
            at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:120)
            at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
            at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
            at $Proxy33.relateWithBranch(Unknown Source)
            at com.btpn.custody.bean.DailyTask.relateMasterDataWithBranch(DailyTask.java:1166)
            at com.btpn.custody.bean.DailyTask.manualInitial(DailyTask.java:213)
            at com.btpn.custody.bean.DailyTask.run(DailyTask.java:197)
            at java.lang.Thread.run(Unknown Source)
Caused by: org.hibernate.exception.LockAcquisitionException: could not update: [com.btpn.custody.entity.MasterData#00176ed4-b7a2-4da5-a266-ffaab3654050]
            at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:107)
            at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
            at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2612)
            at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:2494)
            at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2821)
            at org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:113)
            at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:273)
            at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:265)
            at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:185)
            at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
            at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)
            at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1216)
            at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:383)
            at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:133)
            at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:656)
            ... 11 more
Caused by: java.sql.SQLException: Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
            at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
            at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
            at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
            at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
            at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
            at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
            at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:504)
            at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
            at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:46)
            at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2590)
            ... 23 more

所以我的问题:

1. 我应该更改连接池以避免死锁问题吗？
2. 为什么我的调度 bean 在发生死锁后停止运行？

Answer 1

死锁发生在 SQL Server 中。这既不是 Spring、Hibernate、连接池也不是 SQL Server 的错。问题是您的应用程序正在锁定您的应用程序或其他应用程序也试图锁定的行(以 deadlock 方式)。

最简单的解决方法可能是为您的作业设置重试策略(如果失败则重试整个事务)。您还可以通过查看 SQL Server 日志来尝试找出死锁中涉及的应用程序/用户。