AnsweredAssumed Answered

Transaction problems

Question asked by backslash on Nov 12, 2012
Latest reply on Sep 12, 2013 by neville.sequeira
Hello,
I am developing a platform based on activiti engine (5.10). The database is MSSQL Servier 2008 and I am using Spring and JPA (Hibernate provider).
My application was running fine for few days. But today something happened with the connection to database and I suspect there was a big rollback, because data which was definitely saved in the application was gone. For example there was a process instance going (somewhere in the middle of the whole process) and suddenly it was on the first task of the process.

This is relevant part of the root-context.xml:


<!– JDBC data source and transaction management –>
   <bean id="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource"
      destroy-method="close" p:driverClassName="${jdbc.driverClassName}"
      p:url="${jdbc.databaseurl}" p:username="${jdbc.username}" p:password="${jdbc.password}"
      p:maxActive="100" p:minIdle="5" p:initialSize="10" p:validationQuery="SELECT 1" p:testOnBorrow="true"
      p:testWhileIdle="false" p:removeAbandoned="true" p:removeAbandonedTimeout="60" p:logAbandoned="true"/>
      
   <bean id="entityManagerFactory"
      class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" depends-on="liquibase">
      <property name="persistenceUnitName" value="ano24" />
      <property name="dataSource" ref="dataSource" />
      <property name="jpaProperties">
         <props>
            <prop key="hibernate.dialect">${hibernate.dialect}</prop>
            <prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop>
            <prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
            <prop key="hibernate.ejb.event.post-insert">org.hibernate.ejb.event.EJB3PostInsertEventListener,org.hibernate.envers.event.AuditEventListener
            </prop>
            <prop key="hibernate.ejb.event.post-update">org.hibernate.ejb.event.EJB3PostUpdateEventListener,org.hibernate.envers.event.AuditEventListener
            </prop>
            <prop key="hibernate.ejb.event.post-delete">org.hibernate.ejb.event.EJB3PostDeleteEventListener,org.hibernate.envers.event.AuditEventListener
            </prop>
            <prop key="hibernate.ejb.event.pre-collection-update">org.hibernate.envers.event.AuditEventListener</prop>
            <prop key="hibernate.ejb.event.pre-collection-remove">org.hibernate.envers.event.AuditEventListener</prop>
            <prop key="hibernate.ejb.event.post-collection-recreate">org.hibernate.envers.event.AuditEventListener</prop>
         </props>
      </property>
   </bean>

   <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
      <property name="entityManagerFactory" ref="entityManagerFactory" />
      <property name="dataSource" ref="dataSource" />
   </bean>
   <tx:annotation-driven transaction-manager="transactionManager" />

<!– Activiti BPMN2 engine –>
   <bean id="processEngineConfiguration" class="org.activiti.spring.SpringProcessEngineConfiguration">
      <property name="history" value="full" />
      <property name="dataSource" ref="dataSource" />
      <property name="transactionManager" ref="transactionManager" />
      <property name="databaseSchemaUpdate" value="true" />
      <property name="jpaEntityManagerFactory" ref="entityManagerFactory" />
      <property name="jpaHandleTransaction" value="true" />
      <property name="jpaCloseEntityManager" value="true" />
      <property name="jobExecutorActivate" value="false" />
      
   </bean>
   <bean id="processEngine" class="org.activiti.spring.ProcessEngineFactoryBean">
      <property name="processEngineConfiguration" ref="processEngineConfiguration" />
   </bean>
   <bean id="repositoryService" factory-bean="processEngine"
      factory-method="getRepositoryService" />
   <bean id="formService" factory-bean="processEngine"
      factory-method="getFormService" />
   <bean id="runtimeService" factory-bean="processEngine"
      factory-method="getRuntimeService" />
   <bean id="taskService" factory-bean="processEngine"
      factory-method="getTaskService" />
   <bean id="historyService" factory-bean="processEngine"
      factory-method="getHistoryService" />
   <bean id="managementService" factory-bean="processEngine"
      factory-method="getManagementService" />
   <bean id="identityService" factory-bean="processEngine"
      factory-method="getIdentityService" />

And this is the exception which was rised after the database problem:


12.11.2012 11:46:07 : ERROR : Application exception overridden by rollback exception
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: socket closed
### The error may involve org.activiti.engine.impl.persistence.entity.HistoricTaskInstanceEntity.selectHistoricTaskInstancesByQueryCriteria-Inline
### The error occurred while setting parameters
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: socket closed
   at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:81)
   at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:215)
   at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:197)
   at org.activiti.engine.impl.persistence.entity.HistoricTaskInstanceManager.findHistoricTaskInstancesByQueryCriteria(HistoricTaskInstanceManager.java:55)
   at org.activiti.engine.impl.HistoricTaskInstanceQueryImpl.executeList(HistoricTaskInstanceQueryImpl.java:82)
   at org.activiti.engine.impl.AbstractQuery.execute(AbstractQuery.java:138)
   at org.activiti.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24)
   at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:42)
   at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)
   at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
   at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40)
   at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)
   at org.activiti.engine.impl.AbstractQuery.list(AbstractQuery.java:112)
   at com.inqool.bpmn.service.impl.ProcessTasksServiceImpl.refreshProcessTasks(ProcessTasksServiceImpl.java:41)
   at com.inqool.bpmn.controller.ProcessController.selectProcess(ProcessController.java:61)
   at sun.reflect.GeneratedMethodAccessor686.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
   at java.lang.reflect.Method.invoke(Unknown Source)
   at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:212)
   at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
   at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96)
   at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617)
   at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578)
   at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
   at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:900)
   at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:827)
   at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
   at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
   at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:311)
   at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:116)
   at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
   at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
   at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
   at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
   at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:101)
   at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
   at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113)
   at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
   at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
   at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
   at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
   at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
   at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:182)
   at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
   at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
   at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
   at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
   at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
   at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:173)
   at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
   at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
   at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
   at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
   at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
   at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:929)
   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
   at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
   at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1002)
   at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:585)
   at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
   at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
   at java.lang.Thread.run(Unknown Source)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: socket closed
   at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1667)
   at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1654)
   at com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:1789)
   at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:4838)
   at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:6150)
   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:402)
   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
   at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
   at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
   at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
   at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:332)
   at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:39)
   at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:55)
   at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:41)
   at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:243)
   at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:117)
   at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:72)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:79)
   … 74 more


I am trying to figure out what exactly happened, but cant simulate this situation again. For me it looks like a great portion of the work done by activiti engine for past few days was in one very long transaction, but this sounds crazy. Does anybody have an idea what might happened? Btw. what should be the value of "jpaHandleTransaction" and "jpaCloseEntityManager" in such configuration (spring, JPA, hibernate,…)?

Thanks in advance
Matus

Outcomes