Deadlock on AcquireAsyncJobsDueCmd for MSSQL

[saurabh_biet]

Hi There,

I am using activiti in one of my project. I am using 5.22.0 version of activiti. I am using sprint boot in my application. My activiti engine is using MSSQL.

Whenever I do a performance test of my application I see the following error:

### SQL: SELECT SUB.* FROM (     select        RES.* , row_number() over (ORDER BY RES.ID_ asc) rnk FROM ( select distinct RES.*             from ACT_RU_JOB RES           LEFT OUTER JOIN ACT_RU_EXECUTION PI ON PI.ID_ = RES.PROCESS_INSTANCE_ID_     where RES.RETRIES_ > 0        and (          (RES.DUEDATE_ is not null and RES.DUEDATE_ <= ? and RES.LOCK_EXP_TIME_ is null)         or          (RES.LOCK_EXP_TIME_ is not null and RES.LOCK_EXP_TIME_ <= ?)       )       and TYPE_ = 'message'       and (           (RES.EXECUTION_ID_ is null)           or            (PI.SUSPENSION_STATE_ = 1)       )       )RES ) SUB WHERE SUB.rnk >= ? AND SUB.rnk < ?

### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 171) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

org.apache.ibatis.exceptions.PersistenceException:

### Error querying database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 171) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

### The error may exist in org/activiti/db/mapping/entity/Job.xml

### The error may involve org.activiti.engine.impl.persistence.entity.JobEntity.selectAsyncJobsDueToExecute

### The error occurred while handling results

### SQL: SELECT SUB.* FROM (     select        RES.* , row_number() over (ORDER BY RES.ID_ asc) rnk FROM ( select distinct RES.*             from ACT_RU_JOB RES           LEFT OUTER JOIN ACT_RU_EXECUTION PI ON PI.ID_ = RES.PROCESS_INSTANCE_ID_     where RES.RETRIES_ > 0        and (          (RES.DUEDATE_ is not null and RES.DUEDATE_ <= ? and RES.LOCK_EXP_TIME_ is null)         or          (RES.LOCK_EXP_TIME_ is not null and RES.LOCK_EXP_TIME_ <= ?)       )       and TYPE_ = 'message'       and (           (RES.EXECUTION_ID_ is null)           or            (PI.SUSPENSION_STATE_ = 1)       )       )RES ) SUB WHERE SUB.rnk >= ? AND SUB.rnk < ?

### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 171) 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:30)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:122)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:113)

at org.activiti.engine.impl.db.DbSqlSession.selectListWithRawParameter(DbSqlSession.java:440)

at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:431)

at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:426)

at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:409)

at org.activiti.engine.impl.persistence.entity.JobEntityManager.findAsyncJobsDueToExecute(JobEntityManager.java:164)

at org.activiti.engine.impl.cmd.AcquireAsyncJobsDueCmd.execute(AcquireAsyncJobsDueCmd.java:42)

at org.activiti.engine.impl.cmd.AcquireAsyncJobsDueCmd.execute(AcquireAsyncJobsDueCmd.java:30)

at org.activiti.engine.impl.interceptor.CommandInvoker.execute(CommandInvoker.java:24)

at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:57)

at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:47)

at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)

at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:45)

at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:31)

at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:40)

at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:35)

at org.activiti.engine.impl.asyncexecutor.AcquireAsyncJobsDueRunnable.run(AcquireAsyncJobsDueRunnable.java:52)

at java.lang.Thread.run(Thread.java:745)

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 171) 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:216)

at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer.nextRow(SQLServerResultSet.java:4853)

at com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(SQLServerResultSet.java:1781)

at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:1034)

at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValuesForSimpleResultMap(DefaultResultSetHandler.java:296)

at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:273)

at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSet(DefaultResultSetHandler.java:246)

at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:160)

at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63)

at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:78)

at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62)

at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:303)

at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:154)

at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:102)

at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:82)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:120)

... 18 common frames omitted

Can someone help here? What do you think I am doing wrong? Have you seen this error before?

[daisuke-yoshimo · Accepted solution]

Even when READ_COMMITTED_SNAPSHOT option(SQL Server's database option) is enabled, are deadlocks reproduced?

You can change READ_COMMITTED_SNAPSHOT option by the following sql.

ALTER DATABASE {data_base_name} SET READ_COMMITTED_SNAPSHOT ON;

* If you want to know detail, you should check the MS's site.
https://msdn.microsoft.com/en-us//library/tcbchxcb(v=vs.110).aspx

* Similar problem
https://github.com/Activiti/Activiti/issues/1083