Transaction problems

[backslash]

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

[meyerd]

Hmm. sounds strange. Really difficult to say anything from what you are writing. Do you have any additional information?

[backslash]

Hi,
thank you for response. I might have one more piece of information, but dont know if it will help. I have been using the same "entityManagerFactory" and "transactionManager" in the application for normal persistance of objects. After the database problem, entities stored this way were in the database and the data stored through activiti (process instances, …) was not there.

What about the "jpaHandleTransaction" and "jpaCloseEntityManager", could these two be the cause of problems in my setup?

[meyerd]

Hi backslash,

sounds like the transaction integration between activiti and your entity manager is not setup correctly. Are you using Spring? Do you have different datasources for activiti and the JPA entity manager?

[backslash]

Yes, I am using spring 3.1.0.RELEASE and hibernate 3.6.10.Final. The datasource is the same.

[frederikherema1]


    <property name="jpaHandleTransaction" value="true" />
      <property name="jpaCloseEntityManager" value="true" />

Read the user guide trough, for the use of these properties. If the transaction is externally managed (which in your case, use true), the handleTransaction should be false and close as well, otherwise, activiti JPA-integration will close connection/transaction prematurely…

[backslash]

Thank you for advice. Do you think this could case the data-loss? It is really strange, becuase entities stored directly by me were in DB and only data in activiti tables werent there.

[frederikherema1]

Yes, it could cause the data-loss, since transaction was not correctly shared between your entities and activiti

[neville_sequeir]

Hi backslash,

Did you achieve success in using JpaTransactionManager for Activiti?  I am having the same issue, except I use jTDS JDBC driver and I do not see any exception. But Activiti data is not being saved to database. I have created another topic with some additional information about my observations about transactions - both using  org.springframework.orm.jpa.JpaTransactionManager  and   org.springframework.jdbc.datasource.DataSourceTransactionManager. The new topic is at - http://forums.activiti.org/content/spring-jpatransactionmanager-not-saving-activiti-entities-db

It will be great if you can share some details.