topic Re: activiti & thread safety in Alfresco Archive

activiti & thread safety

christoph_retti — Wed, 24 Oct 2012 21:30:38 GMT

Hi guys, we are using activiti embedded in our product and have it configured using Spring like <bean id="activiti.dataSource.dbcp" class="org.apache.commons.dbcp.BasicDataSource"> <property name="driverClassName" ref="activiti.jdbc.driver" /> <property name="url" ref="activiti

Re: activiti & thread safety

jbarrez — Thu, 25 Oct 2012 08:16:59 GMT

No, Activiti is thread safe. The way I read your exception, is that there is something wrong at the database level. What isolation level is your db running on? Does the db uses table locks instead of row locks? Because I've seen that exception before, but only with H2 database, which uses table locks.

Re: activiti & thread safety

christoph_retti — Thu, 25 Oct 2012 15:06:30 GMT

Hi, I am using SQL Server 2008 with default settings and this are
isolation level = read committedwhich is passed back by executing the t-sql command
DBCC USEROPTIONS;Accoring to
http://msdn.microsoft.com/de-at/library/ms189122(v=sql.105).aspx
SQL Server 2008 does not use table locks - only when running batch updates.

Our own application is not affected in any way by locks, so I was wondering if you use special statements through the ibatis command you run?

chris

Re: activiti & thread safety

jbarrez — Fri, 26 Oct 2012 06:25:06 GMT

Those settings look fine. I'm also assuming a product like MSSQLserver doesn't use table locks.

But the exception bothers me. If you have read committed, and you have two transactions going to the same row, Activiti will use optimistic locking to throw an exception for one of them. But then you'd see an optimistic locking exception and not a deadlock.

Did you already read http://www.codinghorror.com/blog/2008/08/deadlocked.html? In particular, this statements:

I'm no DBA, but it seems to me the root of our problem is that the default SQL Server locking strategy is incredibly pessimistic out of the box:

The database philosophically expects there will be many data conflicts; with multiple sessions all trying to change the same data at the same time and corruption will result. To avoid this, Locks are put in place to guard data integrity … there are a few instances though, when this pessimistic heavy lock design is more of a negative than a positive benefit, such as applications that have very heavy read activity with light writes.
Wow, very heavy read activity with light writes. What does that remind me of? Hmm. Oh yes, that damn website we're building. Fortunately, there is a mode in SQL Server 2005 designed for exactly this scenario: read committed snapshot:

Snapshots rely on an entirely new data change tracking method … more than just a slight logical change, it requires the server to handle the data physically differently. Once this new data change tracking method is enabled, it creates a copy, or snapshot of every data change. By reading these snapshots rather than live data at times of contention, Shared Locks are no longer needed on reads, and overall database performance may increase.

But one thing is for sure: don't synchronize the calls. That would be very bad for performance.

Re: activiti & thread safety

christoph_retti — Fri, 26 Oct 2012 15:11:43 GMT

Hmmm … I have read a lot of articles now on this issue and also found that one should try out

<bean id="processEngineConfiguration" … >
…
<property name="idGenerator">
<bean class="org.activiti.engine.impl.persistence.StrongUuidGenerator" /> 
</property>
</bean>

What do you think, should I change the idGenerator os is this something already in place with activiti 5.10?

Anyway, I will give it a try with READ COMMITED SNAPSHOT tomorrow, I will get you informed of the outcome.

Many thanks for the help!!

chris

Re: activiti & thread safety

jbarrez — Mon, 29 Oct 2012 13:15:22 GMT

Hmmm, you might be right. Didn't think in that direction.
The problem that the uuid generator solves, is that under high load, no free connections can be found for fetching new id blocks that the engine uses to assign ids to entities. it's a long shot, but maybe the exception you see is indeed related to that….

Keep me posted!

Re: activiti & thread safety

christoph_retti — Fri, 02 Nov 2012 18:36:33 GMT

Hi, I gave it another try now and so I again removed the synchronization to be able to reproduce the error again which is

INFO : Starting queue process.cspstep 
Nov 02, 2012 6:59:04 PM org.activiti.engine.impl.bpmn.deployer.BpmnDeployer deploy
INFO: Processing resource UC2InvoiceProcess.Uc2InvoiceProcess.png
Nov 02, 2012 6:59:04 PM org.activiti.engine.impl.bpmn.deployer.BpmnDeployer deploy
INFO: Processing resource UC2InvoiceProcess.Uc2InvoiceProcess.png
Nov 02, 2012 6:59:04 PM org.activiti.engine.impl.bpmn.deployer.BpmnDeployer deploy
INFO: Processing resource UC2InvoiceProcess.bpmn20.xml
Nov 02, 2012 6:59:04 PM org.activiti.engine.impl.bpmn.deployer.BpmnDeployer deploy
INFO: Processing resource UC2InvoiceProcess.bpmn20.xml
Nov 02, 2012 6:59:04 PM org.activiti.engine.impl.bpmn.parser.BpmnParse parseDefinitionsAttributes
INFO: XMLSchema currently not supported as typeLanguage
Nov 02, 2012 6:59:04 PM org.activiti.engine.impl.bpmn.parser.BpmnParse parseDefinitionsAttributes
INFO: XPath currently not supported as expressionLanguage
Nov 02, 2012 6:59:04 PM org.activiti.engine.impl.bpmn.parser.BpmnParse parseProcessDefinitions
INFO: Process with id='Uc2InvoiceProcess' hasn't the attribute isExecutable set. Please maintain it, so you are compatible to future activiti versions.
Nov 02, 2012 6:59:04 PM org.activiti.engine.impl.bpmn.parser.BpmnParse parseDefinitionsAttributes
INFO: XMLSchema currently not supported as typeLanguage
Nov 02, 2012 6:59:04 PM org.activiti.engine.impl.bpmn.parser.BpmnParse parseDefinitionsAttributes
INFO: XPath currently not supported as expressionLanguage
Nov 02, 2012 6:59:04 PM org.activiti.engine.impl.bpmn.parser.BpmnParse parseProcessDefinitions
INFO: Process with id='Uc2InvoiceProcess' hasn't the attribute isExecutable set. Please maintain it, so you are compatible to future activiti versions.
INFO : [CSPSTEP] released (item=177 step=CspstepInvoiceApproval assignmentDate=02.11.12 18:39, step=CspstepInvoiceApproval) 
Nov 02, 2012 6:59:09 PM org.activiti.engine.impl.interceptor.CommandContext close
SEVERE: Error while closing command context
org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
### The error may involve org.activiti.engine.impl.persistence.entity.TaskEntity.deleteTask-Inline
### The error occurred while setting parameters
### SQL: delete from ACT_RU_TASK where ID_ = ?
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
 at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
 at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:147)
 at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:158)
 at org.activiti.engine.impl.db.DbSqlSession$DeleteById.execute(DbSqlSession.java:146)
 at org.activiti.engine.impl.db.DbSqlSession.flushDeletes(DbSqlSession.java:483)
 at org.activiti.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:371)
 at org.activiti.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:157)
 at org.activiti.engine.impl.interceptor.CommandContext.close(CommandContext.java:109)
 at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:49)
 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.TaskServiceImpl.complete(TaskServiceImpl.java:144)
 at biz.i2z.connector.activiti.ActivitiConnector.complete(ActivitiConnector.java:976)
 at biz.i2z.container.workflowaccessor.impl.activiti.ActivitiAccessor.updateTaskInActiviti(ActivitiAccessor.java:2016)
 at biz.i2z.container.workflowaccessor.impl.activiti.ActivitiAccessor.updateTaskData(ActivitiAccessor.java:1971)
 at biz.i2z.container.workflowaccessor.impl.activiti.ActivitiAccessor.updateTaskData(ActivitiAccessor.java:1927)
 at biz.i2z.container.workflowaccessor.impl.activiti.ActivitiAccessor.workOnProcess(ActivitiAccessor.java:1891)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:601)
 at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
 at biz.i2z.container.workflowaccessor.WorkflowAccessorEnrichmentProxy.invoke(WorkflowAccessorEnrichmentProxy.java:132)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
 at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
 at $Proxy75.workOnProcess(Unknown Source)
 at biz.i2z.modules.workflowaccessor.ServiceObject.workOnProcess(ServiceObject.java:468)
 at biz.i2z.modules.workflowaccessor.ServiceObject.workOnProcess(ServiceObject.java:450)
 at biz.i2z.modules.workflowaccessor.ServiceObject.workOnProcess(ServiceObject.java:431)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:601)
 at biz.i2z.system.ApplicationProcess.processRequest(ApplicationProcess.java:380)
 at biz.i2z.system.ApplicationProcess.ipcStart(ApplicationProcess.java:607)
 at biz.i2z.system.I2zProcess.ipcProcessing(I2zProcess.java:263)
 at biz.i2z.system.ApplicationProcess.run(ApplicationProcess.java:656)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 56) 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(SQLServerException.java:197)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)
 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:390)
 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:340)
 at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)
 at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)
 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:322)
 at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
 at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
 at sun.reflect.GeneratedMethodAccessor259.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:601)
 at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:58)
 at $Proxy73.execute(Unknown Source)
 at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:41)
 at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:66)
 at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:45)
 at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:108)
 at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)
 at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:145)
 … 39 more

WARN : updateTaskData(taskIds=[(contid=1211021714529005cb96d64f4924955b¦pid=119¦taskid=191¦execid=119¦businessid=)], username='svc_cspstep', password=' ÿ**************', attributes={}, taskStatus=completed): updating the process instances caused failures: [(contid=1211021714529005cb96d64f4924955b¦pid=119¦taskid=191¦execid=119¦businessid=)] 
### Error updating database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
### The error may involve org.activiti.engine.impl.persistence.entity.TaskEntity.deleteTask-Inline
### The error occurred while setting parameters
### SQL: delete from ACT_RU_TASK where ID_ = ?
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
 
INFO : [CSPSTEP] released (item=191 step=CspstepInvoiceApproval assignmentDate=02.11.12 18:39, step=CspstepInvoiceApproval)

afterwards I shut down the system and changed the database isolation level like

ALTER DATABASE activiti SET READ_COMMITTED_SNAPSHOT ON;

and re-run the test. The interesting thing is now that I get two deadlocks

### Error updating database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
### The error may involve org.activiti.engine.impl.persistence.entity.TaskEntity.deleteTask-Inline
### The error occurred while setting parameters
### SQL: delete from ACT_RU_TASK where ID_ = ?
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
 at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
 at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:147)
 at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:158)
 at org.activiti.engine.impl.db.DbSqlSession$DeleteById.execute(DbSqlSession.java:146)
 at org.activiti.engine.impl.db.DbSqlSession.flushDeletes(DbSqlSession.java:483)
 at org.activiti.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:371)
 at org.activiti.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:157)
 at org.activiti.engine.impl.interceptor.CommandContext.close(CommandContext.java:109)
 at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:49)
 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.TaskServiceImpl.complete(TaskServiceImpl.java:144)
 at biz.i2z.connector.activiti.ActivitiConnector.complete(ActivitiConnector.java:976)
 at biz.i2z.container.workflowaccessor.impl.activiti.ActivitiAccessor.updateTaskInActiviti(ActivitiAccessor.java:2016)
 at biz.i2z.container.workflowaccessor.impl.activiti.ActivitiAccessor.updateTaskData(ActivitiAccessor.java:1971)
 at biz.i2z.container.workflowaccessor.impl.activiti.ActivitiAccessor.updateTaskData(ActivitiAccessor.java:1927)
 at biz.i2z.container.workflowaccessor.impl.activiti.ActivitiAccessor.workOnProcess(ActivitiAccessor.java:1891)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:601)
 at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
 at biz.i2z.container.workflowaccessor.WorkflowAccessorEnrichmentProxy.invoke(WorkflowAccessorEnrichmentProxy.java:132)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
 at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
 at $Proxy75.workOnProcess(Unknown Source)
 at biz.i2z.modules.workflowaccessor.ServiceObject.workOnProcess(ServiceObject.java:468)
 at biz.i2z.modules.workflowaccessor.ServiceObject.workOnProcess(ServiceObject.java:450)
 at biz.i2z.modules.workflowaccessor.ServiceObject.workOnProcess(ServiceObject.java:431)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:601)
 at biz.i2z.system.ApplicationProcess.processRequest(ApplicationProcess.java:380)
 at biz.i2z.system.ApplicationProcess.ipcStart(ApplicationProcess.java:607)
 at biz.i2z.system.I2zProcess.ipcProcessing(I2zProcess.java:263)
 at biz.i2z.system.ApplicationProcess.run(ApplicationProcess.java:656)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 65) 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(SQLServerException.java:197)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)
 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:390)
 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:340)
 at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)
 at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)
 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:322)
 at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
 at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
 at sun.reflect.GeneratedMethodAccessor450.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:601)
 at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:58)
 at $Proxy73.execute(Unknown Source)
 at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:41)
 at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:66)
 at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:45)
 at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:108)
 at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)
 at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:145)
 … 39 more

WARN : updateTaskData(taskIds=[(contid=12110219090315951539f5ad06744e99¦pid=510¦taskid=563¦execid=510¦businessid=)], username='svc_cspstep', password=' ÿ**************', attributes={}, taskStatus=completed): updating the process instances caused failures: [(contid=12110219090315951539f5ad06744e99¦pid=510¦taskid=563¦execid=510¦businessid=)] 
### Error updating database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
### The error may involve org.activiti.engine.impl.persistence.entity.TaskEntity.deleteTask-Inline
### The error occurred while setting parameters
### SQL: delete from ACT_RU_TASK where ID_ = ?
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
 
INFO : [CSPSTEP] released (item=563 step=CspstepInvoiceApproval assignmentDate=02.11.12 19:15, step=CspstepInvoiceApproval) 
Nov 02, 2012 7:16:31 PM org.activiti.engine.impl.interceptor.CommandContext close
SEVERE: Error while closing command context
org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
### The error may involve org.activiti.engine.impl.persistence.entity.TaskEntity.deleteTask-Inline
### The error occurred while setting parameters
### SQL: delete from ACT_RU_TASK where ID_ = ?
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
 at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
 at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:147)
 at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:158)
 at org.activiti.engine.impl.db.DbSqlSession$DeleteById.execute(DbSqlSession.java:146)
 at org.activiti.engine.impl.db.DbSqlSession.flushDeletes(DbSqlSession.java:483)
 at org.activiti.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:371)
 at org.activiti.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:157)
 at org.activiti.engine.impl.interceptor.CommandContext.close(CommandContext.java:109)
 at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:49)
 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.TaskServiceImpl.complete(TaskServiceImpl.java:144)
 at biz.i2z.connector.activiti.ActivitiConnector.complete(ActivitiConnector.java:976)
 at biz.i2z.container.workflowaccessor.impl.activiti.ActivitiAccessor.updateTaskInActiviti(ActivitiAccessor.java:2016)
 at biz.i2z.container.workflowaccessor.impl.activiti.ActivitiAccessor.updateTaskData(ActivitiAccessor.java:1971)
 at biz.i2z.container.workflowaccessor.impl.activiti.ActivitiAccessor.updateTaskData(ActivitiAccessor.java:1927)
 at biz.i2z.container.workflowaccessor.impl.activiti.ActivitiAccessor.workOnProcess(ActivitiAccessor.java:1891)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:601)
 at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
 at biz.i2z.container.workflowaccessor.WorkflowAccessorEnrichmentProxy.invoke(WorkflowAccessorEnrichmentProxy.java:132)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
 at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
 at $Proxy75.workOnProcess(Unknown Source)
 at biz.i2z.modules.workflowaccessor.ServiceObject.workOnProcess(ServiceObject.java:468)
 at biz.i2z.modules.workflowaccessor.ServiceObject.workOnProcess(ServiceObject.java:450)
 at biz.i2z.modules.workflowaccessor.ServiceObject.workOnProcess(ServiceObject.java:431)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:601)
 at biz.i2z.system.ApplicationProcess.processRequest(ApplicationProcess.java:380)
 at biz.i2z.system.ApplicationProcess.ipcStart(ApplicationProcess.java:607)
 at biz.i2z.system.I2zProcess.ipcProcessing(I2zProcess.java:263)
 at biz.i2z.system.ApplicationProcess.run(ApplicationProcess.java:656)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 55) 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(SQLServerException.java:197)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)
 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:390)
 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:340)
 at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)
 at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)
 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:322)
 at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
 at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
 at sun.reflect.GeneratedMethodAccessor450.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:601)
 at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:58)
 at $Proxy73.execute(Unknown Source)
 at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:41)
 at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:66)
 at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:45)
 at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:108)
 at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)
 at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:145)
 … 39 more

WARN : updateTaskData(taskIds=[(contid=1211021908545654696a23144864904a¦pid=519¦taskid=570¦execid=519¦businessid=)], username='svc_cspstep', password=' ÿ**************', attributes={}, taskStatus=completed): updating the process instances caused failures: [(contid=1211021908545654696a23144864904a¦pid=519¦taskid=570¦execid=519¦businessid=)] 
### Error updating database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
### The error may involve org.activiti.engine.impl.persistence.entity.TaskEntity.deleteTask-Inline
### The error occurred while setting parameters
### SQL: delete from ACT_RU_TASK where ID_ = ?
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
 
INFO : [CSPSTEP] released (item=570 step=CspstepInvoiceApproval assignmentDate=02.11.12 19:15, step=CspstepInvoiceApproval) 
INFO : [CSPSTEP] released (item=556 step=CspstepInvoiceApproval assignmentDate=02.11.12 19:15, step=CspstepInvoiceApproval) 
INFO : starting queue process.cspstep

so I think lowering the database isolation just made it worse.

The szenario I am testing is the following:

in my process invoices are filled out and then we route these invoices (once completed) to a user task assigned to the queue cspstep.

There is a thread that queries all the tasks assigned to the group cspstep on a regular basis and then spawns a configurable number of threads)
to process the tasks found in parallel.

This processing is fine but when the results of the processing (in my case 3 invoices in parallel) is to be written back (in my case some task variable updates and
the completion of the user task) these deadlocks occur.

(The reason we have choosen to implement this automation based on a user task is that we can easily query the tasks to be processed and delegate the tasks
in case of failure to some admin group that can handle the issue and then delegate it back to be processed again).

Is there something wrong with this approach and activiti is not able to process user tasks in parallel?

regards, chris

Re: activiti & thread safety

christoph_retti — Fri, 02 Nov 2012 18:58:50 GMT

What I also tried now was to embedd my data source into a transaction aware datasource like

<bean id="activiti.dataSource.dbcp.tx" class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">
   <property name="targetDataSource" ref="activiti.dataSource.dbcp" />
</bean>

<bean id="activiti.dataSource.dbcp" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" ref="activiti.jdbc.driver" />
    <property name="url" ref="activiti.jdbc.url" />
    <property name="username" ref="activiti.jdbc.username" />
    <property name="password" ref="activiti.jdbc.password" />
    <property name="maxIdle" value="2" />
    <property name="maxActive" value="10" />
    <property name="maxWait" value="10000" />
    <property name="validationQuery" value="select 1" />
    <property name="testOnBorrow" value="false" />
    <property name="testWhileIdle" value="true" />
    <property name="timeBetweenEvictionRunsMillis" value="1200000" />
    <property name="minEvictableIdleTimeMillis" value="1800000" />
    <property name="numTestsPerEvictionRun" value="5" />
    <property name="defaultAutoCommit" value="false" />
</bean>

wich did not help either

Re: activiti & thread safety

christoph_retti — Sat, 03 Nov 2012 13:18:38 GMT

I did a few more testing and when I changed the configuration to

  <bean id="activiti.dataSource.spring" class="org.springframework.jdbc.datasource.SimpleDriverDataSource">
    <property name="driverClass" ref="activiti.jdbc.driver" />
    <property name="url" ref="activiti.jdbc.url" />
    <property name="username" ref="activiti.jdbc.username" />
    <property name="password" ref="activiti.jdbc.password" />
  </bean>

  <bean id="activiti.dataSource.spring.tx" class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">
    <property name="targetDataSource" ref="activiti.dataSource.spring" />
  </bean>

  <bean id="activiti.processEngineConfiguration" class="org.activiti.spring.SpringProcessEngineConfiguration">
    <property name="dataSource" ref="activiti.dataSource.spring.tx" />
    <property name="transactionManager" ref="activiti.transactionManager" />
    <property name="databaseSchemaUpdate" value="true" />
    …
  </bean>

using the org.springframework.jdbc.datasource.SimpleDriverDataSource instead of the pooled dbcp version org.apache.commons.dbcp.BasicDataSource
then the deadlocks are gone.

The question for me now is if this simpler configuration forces a synchronization on the (single?) JDBC connection or is there a problem when using
database connection pools?

Does anybody have experience/examples using activiti with a database connection pool?

regards, chris

Re: activiti & thread safety

jbarrez — Mon, 05 Nov 2012 08:16:00 GMT

wow, thanks for following up so good. Very interesting.

I have nothing conclusive on it, but I had the same experience with DBCP. If I ran my tests with very high concurrency, the system would just lock up. But that was on MySQL, and I didn't get deadlocks. Or maybe I just didnt wait long enough ….

I switched then to BoneCP: http://www.jorambarrez.be/blog/2012/04/30/dbcp_vs_c3p0_bonecp/
And all my problems were gone (and performance was better)

Re: activiti & thread safety

edgarjoao — Wed, 20 Apr 2016 17:42:58 GMT

Hi,
I had same issue with SQL Server, and I fixed using below configuration:

Cause
SQL Server is escalating row locks (in this case deleting a draft from the CONTENT table) to table locks and stopping other transactions from accessing the table.

Diagnosis
Check whether you are using the recommended transaction isolation level of "Read Committed with Row Versioning" by running this SELECT query. If this query which should result in '1', you are already using it. If it returns '0', proceed to the Workaround section:

SELECT sd.is_read_committed_snapshot_on
FROM sys.databases AS sd
WHERE sd.[name] = '<database_name>';

Workaround
Configure your database to use the isolation level, Read Committed with Row Versioning. You can do this by executing the following query:

ALTER DATABASE <database_name>
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;

Regards,
Edgar

Re: activiti & thread safety

jbarrez — Tue, 26 Apr 2016 10:39:35 GMT

Edgar, many thanks for posting this. I'm sure many people will find it helpful!