AnsweredAssumed Answered

Getting org.h2.jdbc.JdbcSQLException: Deadlock Error

Question asked by sharad on Mar 11, 2014
Latest reply on Mar 17, 2014 by jbarrez
I am running https://github.com/jbarrez/activiti-benchmark code for analyzing Activiti 5.14 Performance. I am running this project with H2 Database. I am getting JdbcSQLException: Deadlock error for process-usertask-02 workflow when running with 2 threads in threadpool.

url : jdbc:h2:activiti;DB_CLOSE_DELAY=1000
driver : org.h2.Driver
Finished deploying test processes
Tue Mar 11 18:05:36 IST 2014 - benchmarking with one thread.
Tue Mar 11 18:05:36 IST 2014 : [SEQ]Starting 10 of process process-usertask-02
Tue Mar 11 18:05:38 IST 2014 : Counting finished processes [expected:10] … OK
Tue Mar 11 18:05:38 IST 2014 : Recreating DB schema
Rebooting process engine
Reboot Process Engine : Calling getProcessEngine
Using DEFAULT config
[Process engine configuration info]:
url : jdbc:h2:activiti;DB_CLOSE_DELAY=1000
driver : org.h2.Driver
Redeploying test processes
Tue Mar 11 18:05:49 IST 2014 : [RND]Starting 10 random processes
Tue Mar 11 18:05:50 IST 2014 : Counting finished processes [expected:10] … OK
Tue Mar 11 18:05:50 IST 2014 : Recreating DB schema
Rebooting process engine
Reboot Process Engine : Calling getProcessEngine
Using DEFAULT config
[Process engine configuration info]:
url : jdbc:h2:activiti;DB_CLOSE_DELAY=1000
driver : org.h2.Driver
Redeploying test processes
Tue Mar 11 18:06:01 IST 2014 - benchmarking with fixed threadpool of 2 threads.
Tue Mar 11 18:06:01 IST 2014 : [SEQ]Starting 10 of process process-usertask-02
Processing process execution durations …
Tue Mar 11 18:06:01 IST 2014 : Counting finished processes [expected:10] … OK
Tue Mar 11 18:06:01 IST 2014 : Recreating DB schema
Rebooting process engine
Reboot Process Engine : Calling getProcessEngine
Using DEFAULT config
[Process engine configuration info]:
url : jdbc:h2:activiti;DB_CLOSE_DELAY=1000
driver : org.h2.Driver
Redeploying test processes
Tue Mar 11 18:06:12 IST 2014: [RND]Starting 10 random processes
Exception in thread "pool-6-thread-2" org.apache.ibatis.exceptions.PersistenceException:
### Error updating database.  Cause: org.h2.jdbc.JdbcSQLException: Deadlock detected. The current transaction was rolled back. Details: "
Session #17 (user: SA) is waiting to lock PUBLIC.ACT_RU_EXECUTION while locking PUBLIC.ACT_HI_ACTINST (exclusive), PUBLIC.ACT_HI_TASKINST (exclusive), PUBLIC.ACT_HI_PROCINST (exclusive), PUBLIC.ACT_RU_TASK (exclusive).
Session #19 (user: SA) is waiting to lock PUBLIC.ACT_HI_PROCINST while locking PUBLIC.ACT_RU_EXECUTION (exclusive)."; SQL statement:
insert into ACT_HI_PROCINST (
        ID_,
        PROC_INST_ID_,
        BUSINESS_KEY_,
        PROC_DEF_ID_,
        START_TIME_,
        END_TIME_,
        DURATION_,
        START_USER_ID_,
        START_ACT_ID_,
        END_ACT_ID_,
        SUPER_PROCESS_INSTANCE_ID_,
        DELETE_REASON_
      ) values (
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?
      ) [40001-141]
### The error may involve org.activiti.engine.impl.persistence.entity.HistoricProcessInstanceEntity.insertHistoricProcessInstance-Inline
### The error occurred while setting parameters
### SQL: insert into ACT_HI_PROCINST (         ID_,         PROC_INST_ID_,         BUSINESS_KEY_,         PROC_DEF_ID_,         START_TIME_,         END_TIME_,         DURATION_,         START_USER_ID_,         START_ACT_ID_,         END_ACT_ID_,         SUPER_PROCESS_INSTANCE_ID_,         DELETE_REASON_       ) values (         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?       )
### Cause: org.h2.jdbc.JdbcSQLException: Deadlock detected. The current transaction was rolled back. Details: "
Session #17 (user: SA) is waiting to lock PUBLIC.ACT_RU_EXECUTION while locking PUBLIC.ACT_HI_ACTINST (exclusive), PUBLIC.ACT_HI_TASKINST (exclusive), PUBLIC.ACT_HI_PROCINST (exclusive), PUBLIC.ACT_RU_TASK (exclusive).
Session #19 (user: SA) is waiting to lock PUBLIC.ACT_HI_PROCINST while locking PUBLIC.ACT_RU_EXECUTION (exclusive)."; SQL statement:
insert into ACT_HI_PROCINST (
        ID_,
        PROC_INST_ID_,
        BUSINESS_KEY_,
        PROC_DEF_ID_,
        START_TIME_,
        END_TIME_,
        DURATION_,
        START_USER_ID_,
        START_ACT_ID_,
        END_ACT_ID_,
        SUPER_PROCESS_INSTANCE_ID_,
        DELETE_REASON_
      ) values (
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?
      ) [40001-141]
   at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:150)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:137)
   at org.activiti.engine.impl.db.DbSqlSession.flushInserts(DbSqlSession.java:540)
   at org.activiti.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:443)
   at org.activiti.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:170)
   at org.activiti.engine.impl.interceptor.CommandContext.close(CommandContext.java:117)
   at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:66)
   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.RuntimeServiceImpl.startProcessInstanceByKey(RuntimeServiceImpl.java:56)
   at be.jorambarrez.activiti.benchmark.execution.ExecuteProcessRunnable.run(ExecuteProcessRunnable.java:29)
   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:744)
Caused by: org.h2.jdbc.JdbcSQLException: Deadlock detected. The current transaction was rolled back. Details: "
Session #17 (user: SA) is waiting to lock PUBLIC.ACT_RU_EXECUTION while locking PUBLIC.ACT_HI_ACTINST (exclusive), PUBLIC.ACT_HI_TASKINST (exclusive), PUBLIC.ACT_HI_PROCINST (exclusive), PUBLIC.ACT_RU_TASK (exclusive).
Session #19 (user: SA) is waiting to lock PUBLIC.ACT_HI_PROCINST while locking PUBLIC.ACT_RU_EXECUTION (exclusive)."; SQL statement:
insert into ACT_HI_PROCINST (
        ID_,
        PROC_INST_ID_,
        BUSINESS_KEY_,
        PROC_DEF_ID_,
        START_TIME_,
        END_TIME_,
        DURATION_,
        START_USER_ID_,
        START_ACT_ID_,
        END_ACT_ID_,
        SUPER_PROCESS_INSTANCE_ID_,
        DELETE_REASON_
      ) values (
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?
      ) [40001-141]
   at org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
   at org.h2.message.DbException.get(DbException.java:167)
   at org.h2.message.DbException.get(DbException.java:144)
   at org.h2.table.RegularTable.doLock(RegularTable.java:469)
   at org.h2.table.RegularTable.lock(RegularTable.java:416)
   at org.h2.command.dml.Insert.insertRows(Insert.java:119)
   at org.h2.command.dml.Insert.update(Insert.java:82)
   at org.h2.command.CommandContainer.update(CommandContainer.java:70)
   at org.h2.command.Command.executeUpdate(Command.java:199)
   at org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:179)
   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:100)
   at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:148)
   … 14 more
Tue Mar 11 18:06:13 IST 2014 : Counting finished processes [expected:10] … Exception in thread "main" java.lang.RuntimeException: Error: expected to have 10 processes executions, but got 9 instead.
   at be.jorambarrez.activiti.benchmark.execution.BasicBenchmarkExecution.verifyCounts(BasicBenchmarkExecution.java:141)
   at be.jorambarrez.activiti.benchmark.execution.ThreadPooledBenchmarkExecution.randomExecution(ThreadPooledBenchmarkExecution.java:91)
   at be.jorambarrez.activiti.benchmark.Benchmark.executeBenchmarks(Benchmark.java:112)
   at be.jorambarrez.activiti.benchmark.Benchmark.main(Benchmark.java:69)


How do we fix above issue ?

Outcomes