Deadlock on MSSQL 2008 while loadtesting

[sazzadul]

Hei,

I am getting the following exception while running loadtest. Is there any twik in the configuration I can do to avoid this exception ?
I havn't created any db indexes other than what was already added by activity engine.

I am having this problem each time I run a loadtest. Is this specific to mssql ?
I am using jtds datasource and sqljdbc driver version 1.2

Please suggest me of a solution to this problem.

Exception in thread "pool-1-thread-3" org.apache.ibatis.exceptions.PersistenceException:
### Error updating database.  Cause: java.sql.SQLException: Transaction (Process ID 269) 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.VariableInstanceEntity.deleteByteArray-Inline
### The error occurred while setting parameters
### Cause: java.sql.SQLException: Transaction (Process ID 269) 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:8)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:120)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:131)
        at org.activiti.engine.impl.db.DbSqlSession$DeleteById.execute(DbSqlSession.java:146)
        at org.activiti.engine.impl.db.DbSqlSession.flushDeletes(DbSqlSession.java:444)
        at org.activiti.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:349)
        at org.activiti.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:148)
        at org.activiti.engine.impl.interceptor.CommandContext.close(CommandContext.java:104)
        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.jobexecutor.ExecuteJobsRunnable.run(ExecuteJobsRunnable.java:36)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
        at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Transaction (Process ID 269) 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:365)
        at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
        at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
        at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)
        at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:525)
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:487)
        at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.execute(JtdsPreparedStatement.java:475)
        at sun.reflect.GeneratedMethodAccessor14.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at java.lang.reflect.Method.invoke(Unknown Source)
        at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:45)
        at $Proxy26.execute(Unknown Source)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:22)
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:51)
        at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:29)
        at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:75)
        at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:43)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:118)
        … 15 more
12.jan.2012 15:14:45 org.activiti.engine.impl.interceptor.CommandContext close
SEVERE: Error while closing command context
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[meyerd]

Hello sazzadul,

Without having looked into you stacktraces in detail:
are you using org.activiti.engine.impl.persistence.StrongUuidGenerator (See ACT-789) ?

[sazzadul]

Hello sazzadul,

Without having looked into you stacktraces in detail:
are you using org.activiti.engine.impl.persistence.StrongUuidGenerator (See ACT-789) ?

No I am not, will this solve the problem ?

I have tried with this settings without any luck


<tx:advice id="txAdvice" transaction-manager="transactionManager">
    <tx:attributes>
       <tx:method name="get*" propagation="REQUIRED" read-only="true" />
       <tx:method name="set*" propagation="REQUIRES_NEW" />
       <tx:method name="*" />
    </tx:attributes>
</tx:advice>


[sazzadul]

Hello sazzadul,

Without having looked into you stacktraces in detail:
are you using org.activiti.engine.impl.persistence.StrongUuidGenerator (See ACT-789) ?

I have now tried StrongUuidGenerator as well and result is same also the deadlock still occurs.

Do you have other suggestions ?

[notizklotz]

You are not alone…

Please see http://forums.activiti.org/en/viewtopic.php?f=6&t=2029 which is also a topic in this forum about this problem. For using MS SQL's READ_COMMITTED_SNAPSHOT setting resolved most deadlocks, but not all. See the other topic for how to enable that and why that resolves deadlocks.

[trond_nordli]

We had a similar problem on MS SQL 2008 (SP3) but this problem seems to have disappeared after we upgraded the project from Activiti version 5.12.1 to Activiti version 5.13.

We have also tested this out successfully in Activi Benchmark project, where Activiti version 5.13 works but the older version does not work (dead locks problem) on MS SQL 2008.

Activiti Benchmark project:
http://www.jorambarrez.be/blog/2012/06/28/the-activiti-performance-showdown/

[jbarrez]

Yes. We had a customer seeing a similar issue on MSSQL and fixed it in 5.13.

[asite]

Hi jbarrez,
I am using 'org.activiti:activiti-spring:5.15.1' version and I have been facing the same problem of deadlock for mssql server from last one month and still I hadn't find any solution.

I had tried below solution
1. Created Missing index on activiti's table
2. Used BoneCP instead of DBCP

but still produce deadlock when performing taskService.complete() command in high concurrency.

Any help….

[jbarrez]

Any reason why using such an old version?
If I remember correctly, the problem with mssql back in the day was mising indices … maybe that's worth to look into?