Hi *,

we are facing the same problem. Has someone doublechecked the index workaround?

cheers,

Matthias Schmidt / Filiadata / Netpioneer / Karlsruhe

Feb 19, 2014 3:15:10 PM org.camunda.bpm.engine.impl.interceptor.CommandContext close
SEVERE: Error while closing command context
org.apache.ibatis.exceptions.PersistenceException:

1. 1. 1. Error updating database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
      2. The error may involve org.camunda.bpm.engine.impl.persistence.entity.VariableInstanceEntity.deleteByteArrayNoRevisionCheck-Inline
      3. The error occurred while setting parameters
      4. SQL: delete from ACT_GE_BYTEARRAY where ID_ = ?
      5. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 68) 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.camunda.bpm.engine.impl.db.DbSqlSession$BulkDeleteOperation.execute(DbSqlSession.java:201)
         at org.camunda.bpm.engine.impl.db.DbSqlSession.flushDeletes(DbSqlSession.java:735)
         at org.camunda.bpm.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:502)
         at org.camunda.bpm.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:211)
         at org.camunda.bpm.engine.impl.interceptor.CommandContext.close(CommandContext.java:154)
         at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:49)
         at org.camunda.bpm.engine.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)
         at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:131)
         at org.camunda.bpm.engine.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40)
         at org.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:32)
         at org.camunda.bpm.engine.impl.RuntimeServiceImpl.startProcessInstanceByKey(RuntimeServiceImpl.java:66)
         at org.camunda.bpm.engine.RuntimeService$startProcessInstanceByKey.call(Unknown Source)

...
...
...

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1009)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:722)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 68) 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.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
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.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)
... 125 more

The index solution mentioned in the description works with various testcases on mssql where they deadlocked before.

The corresponding drop statements are:

drop index ACT_RU_EXECUTION.ACT_IDX_EXECUTION_PROC;
drop index ACT_RU_EXECUTION.ACT_IDX_EXECUTION_PARENT;
drop index ACT_RU_EXECUTION.ACT_IDX_EXECUTION_SUPER;

drop index ACT_RU_EVENT_SUBSCR.ACT_IDX_EVENT_SUBSCR_EXEC;

drop index ACT_GE_BYTEARRAY.ACT_IDX_BA_DEPLOYMENT;

drop index ACT_RU_IDENTITYLINK.ACT_IDX_IDENT_LNK_TASK;

drop index ACT_RU_INCIDENT.ACT_IDX_INCIDENT_EXEC;
drop index ACT_RU_INCIDENT.ACT_IDX_INCIDENT_PROCINST;
drop index ACT_RU_INCIDENT.ACT_IDX_INCIDENT_PROC_DEF_ID;
drop index ACT_RU_INCIDENT.ACT_IDX_INCIDENT_CAUSE;
drop index ACT_RU_INCIDENT.ACT_IDX_INCIDENT_ROOT_CAUSE;

drop index ACT_RU_JOB.ACT_IDX_JOB_EXCEPTION_STACK;

drop index ACT_RU_VARIABLE.ACT_IDX_VARIABLE_BA;
drop index ACT_RU_VARIABLE.ACT_IDX_VARIABLE_EXEC;
drop index ACT_RU_VARIABLE.ACT_IDX_VARIABLE_PROCINST;

drop index ACT_RU_TASK.ACT_IDX_TASK_EXEC;
drop index ACT_RU_TASK.ACT_IDX_TASK_PROCINST;
drop index ACT_RU_TASK.ACT_IDX_TASK_PROC_DEF_ID;

Hi - have you guys fixed this issue with changes to the code in 7.1.0-Final, or have you just added these additional indexes? Reason why I'm asking: we have all those indexes in place with version 7.0.0 and still facing this issue ...

Here's the top of the exception, It's different from the ones above. So if it's an complete new issue, pls. keep me in the loop:

Apr 07, 2014 1:22:40 PM org.camunda.bpm.engine.impl.interceptor.CommandContext close
SEVERE: Error while closing command context
org.apache.ibatis.exceptions.PersistenceException:

1. 1. 1. Error querying database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
      2. The error may exist in org/camunda/bpm/engine/impl/mapping/entity/HistoricVariableInstance.xml
      3. The error may involve org.camunda.bpm.engine.impl.persistence.entity.HistoricVariableInstanceEntity.selectHistoricVariableInstanceByQueryCriteria-Inline
      4. The error occurred while setting parameters
      5. SQL: SELECT SUB.* FROM ( select RES.* , row_number() over (ORDER BY RES.ID_ asc) rnk FROM ( select distinct RES.* from ACT_HI_VARINST RES WHERE RES.PROC_INST_ID_ = ? and RES.NAME_ = ? )RES ) SUB WHERE SUB.rnk >= ? AND SUB.rnk < ?
      6. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 53) 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.selectList(DefaultSqlSession.java:104)
         at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:95)
         at org.camunda.bpm.engine.impl.db.DbSqlSession.selectListWithRawParameter(DbSqlSession.java:329)
         at org.camunda.bpm.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:320)
         at org.camunda.bpm.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:310)
         at org.camunda.bpm.engine.impl.persistence.entity.HistoricVariableInstanceManager.findHistoricVariableInstancesByQueryCriteria(HistoricVariableInstanceManager.java:64)
         at org.camunda.bpm.engine.impl.HistoricVariableInstanceQueryImpl.executeList(HistoricVariableInstanceQueryImpl.java:123)
         at org.camunda.bpm.engine.impl.AbstractQuery.executeSingleResult(AbstractQuery.java:156)
         at org.camunda.bpm.engine.impl.AbstractQuery.execute(AbstractQuery.java:139)
         at org.camunda.bpm.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24)
         at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:42)
         at org.camunda.bpm.engine.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)
         at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:131)
         at org.camunda.bpm.engine.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40)
         at org.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:32)
         at org.camunda.bpm.engine.impl.AbstractQuery.singleResult(AbstractQuery.java:102)
         at org.camunda.bpm.engine.query.Query$singleResult$0.call(Unknown Source)

cheers - Matthias

Hi Matthias,

Did you really used the latest indexes inside the ticket?

create index ACT_IDX_EXECUTION_PROC on ACT_RU_EXECUTION(PROC_DEF_ID_);
create index ACT_IDX_EXECUTION_PARENT on ACT_RU_EXECUTION(PARENT_ID_);
create index ACT_IDX_EXECUTION_SUPER on ACT_RU_EXECUTION(SUPER_EXEC_);
create index ACT_IDX_EVENT_SUBSCR_EXEC on ACT_RU_EVENT_SUBSCR(EXECUTION_ID_);
create index ACT_IDX_BA_DEPLOYMENT on ACT_GE_BYTEARRAY(DEPLOYMENT_ID_);
create index ACT_IDX_IDENT_LNK_TASK on ACT_RU_IDENTITYLINK(TASK_ID_);
create index ACT_IDX_INCIDENT_EXEC on ACT_RU_INCIDENT(EXECUTION_ID_);
create index ACT_IDX_INCIDENT_PROCINST on ACT_RU_INCIDENT(PROC_INST_ID_);
create index ACT_IDX_INCIDENT_PROC_DEF_ID on ACT_RU_INCIDENT(PROC_DEF_ID_);
create index ACT_IDX_INCIDENT_CAUSE on ACT_RU_INCIDENT(CAUSE_INCIDENT_ID_);
create index ACT_IDX_INCIDENT_ROOT_CAUSE on ACT_RU_INCIDENT(ROOT_CAUSE_INCIDENT_ID_);
create index ACT_IDX_JOB_EXCEPTION_STACK on ACT_RU_JOB(EXCEPTION_STACK_ID_);
create index ACT_IDX_VARIABLE_BA on ACT_RU_VARIABLE(BYTEARRAY_ID_);
create index ACT_IDX_VARIABLE_EXEC on ACT_RU_VARIABLE(EXECUTION_ID_);
create index ACT_IDX_VARIABLE_PROCINST on ACT_RU_VARIABLE(PROC_INST_ID_);
create index ACT_IDX_TASK_EXEC on ACT_RU_TASK(EXECUTION_ID_);
create index ACT_IDX_TASK_PROCINST on ACT_RU_TASK(PROC_INST_ID_);
create index ACT_IDX_TASK_PROC_DEF_ID on ACT_RU_TASK(PROC_DEF_ID_);

If you did, could you please provide a failing testcase, because it should not possible to deadlock on a select only query, especially in history. Would be nice to get a complete stacktrace and the situation where / when it happens.

Cheers,
Christian

Hi Christian,

thanks for this super-fast reply. Meanwhile I managed to prevent these deadlocks by switching to the read isolation level:

ALTER DATABASE CURRENT
SET READ_COMMITTED_SNAPSHOT ON

This is a pretty costly mode, so i would love to have this issue fixed in general.

Yes, we have all these indexes in place, I've doublechecked. Carving out a testcase ( which you need, obviously ) is a non-trivial endeavour. We have a rather large application. I'll try to come up with something.

cheers,

Matthias

Hi Matthias,

Maybe you can enable the deadlock log for mssql and attach it together with a full stacktrace so we can have a look into it. You can find the deadlock log inside the mssql log dir. I think it is called error.log. Following commands can be executed on the database via a query:

Enable deadlock log:
– SQL 2000 version
DBCC TRACEON (1204, -1)
– SQL 2005 version
DBCC TRACEON (1222, -1)
Turn off deadlock tracing:

– SQL 2000 version
DBCC TRACEOFF (1204, -1)
– SQL 2005 version
DBCC TRACEOFF (1222, -1)

Cheers,
Christian

Hello,

was the fix tested on DB2 too?
I still run into deadlocks at my DB2 system using Camunda Version 7.2.0. When using the indexes provided in Activiti (https://jira.codehaus.org/browse/ACT-1649) the deadlocks are gone.

Greets
Chris

Hi Christoph, we also provide indexes on the foreign key constraints like activit does.

See https://github.com/camunda/camunda-bpm-platform/blob/7.2.0/engine/src/main/resources/org/camunda/bpm/engine/db/create/activiti.db2.create.engine.sql#L336 ff

One index was missing in 7.2.0 and will be provided with the 7.3.0 release. It is already available on master at https://github.com/camunda/camunda-bpm-platform/blob/master/distro/sql-script/upgrade/db2_engine_7.2_patch_7.2.4_to_7.2.5.sql

Cheers,
Christian

Thanks a lot!