I am using Camunda 7.20 and am experiencing regular deadlocks on job acquisition. This might be a duplicate of this topic: Deadlock on job acquistion - Camunda 7 Topics / Discussion & Questions - Camunda Forum which unfortunately has no replies.
This is the error Message:
<#877dfd5b> com.microsoft.sqlserver.jdbc.SQLServerException: An error occurred during the current command (Done status 0). Transaction (Process ID 329) 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:259)
at c.m.s.j.SQLServerResultSet$FetchBuffer$FetchBufferTokenHandler.onDone(SQLServerResultSet.java:5456)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:103)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:42)
at c.m.sqlserver.jdbc.SQLServerResultSet$FetchBuffer.nextRow(SQLServerResultSet.java:5559)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(SQLServerResultSet.java:1821)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:1079)
at com.zaxxer.hikari.pool.HikariProxyResultSet.next(HikariProxyResultSet.java)
at o.a.i.executor.resultset.DefaultResultSetHandler.handleRowValuesForSimpleResultMap(DefaultResultSetHandler.java:352)
at o.a.i.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:328)
at o.a.i.executor.resultset.DefaultResultSetHandler.handleResultSet(DefaultResultSetHandler.java:301)
at o.a.i.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:194)
at o.a.i.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:65)
at o.a.i.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
at org.apache.ibatis.executor.BatchExecutor.doQuery(BatchExecutor.java:92)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:89)
at o.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
... 24 common frames omitted
Wrapped by: <#d3a058f3> org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: An error occurred during the current command (Done status 0). Transaction (Process ID 329) 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/camunda/bpm/engine/impl/mapping/entity/Job.xml
### The error may involve org.camunda.bpm.engine.impl.persistence.entity.JobEntity.selectNextJobsToExecute
### The error occurred while handling results
### SQL: SELECT SUB.* FROM ( select RES.ID_, RES.REV_, RES.DUEDATE_, RES.PROCESS_INSTANCE_ID_, RES.EXCLUSIVE_ , row_number() over (ORDER BY RES.ID_ asc) rnk FROM ( select distinct RES.ID_, RES.REV_, RES.TYPE_, RES.LOCK_EXP_TIME_, RES.LOCK_OWNER_, RES.EXCLUSIVE_, RES.PROCESS_INSTANCE_ID_, RES.DUEDATE_, RES.PRIORITY_ from digital_application_process.ACT_RU_JOB RES where (RES.RETRIES_ > 0) and ( RES.DUEDATE_ is null or RES.DUEDATE_ <= ? ) and (RES.LOCK_OWNER_ is null or RES.LOCK_EXP_TIME_ < ?) and RES.SUSPENSION_STATE_ = 1 and ( ( RES.EXCLUSIVE_ = 1 and not exists( select J2.ID_ from digital_application_process.ACT_RU_JOB J2 where J2.PROCESS_INSTANCE_ID_ = RES.PROCESS_INSTANCE_ID_ -- from the same proc. inst. and (J2.EXCLUSIVE_ = 1) -- also exclusive and (J2.LOCK_OWNER_ is not null and J2.LOCK_EXP_TIME_ >= ?) -- in progress ) ) or RES.EXCLUSIVE_ = 0 ) )RES ) SUB WHERE SUB.rnk >= ? AND SUB.rnk < ? ORDER BY SUB.rnk
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: An error occurred during the curre...
Some more details on my setup:
Camunda 7.20.3 embedded in spring boot application
Another Camunda 7.20.3 deployed which is just used to provide the webapps
External tasks
Sql Server (mssql) Database with isolation level READ COMMITTED
Do you have any hints on how we can solve or even just troubleshoot the issue further?