AnsweredAssumed Answered

DB deadlock when running processes concurrently

Question asked by lichtin on Oct 11, 2011
Latest reply on Oct 13, 2011 by lichtin
Hi
Following experience when running a few processes in parallel:
11.10.2011 10:12:11 org.activiti.engine.impl.interceptor.CommandContext close
SCHWERWIEGEND: Error while closing command context
org.apache.ibatis.exceptions.PersistenceException:
### Error updating database.  Cause: org.h2.jdbc.JdbcSQLException: Eine Verklemmung (Deadlock) ist aufgetreten. Die aktuelle Transaktion wurde rlückgängig gemacht. Details: "
Session #1676 (user: SA) is waiting to lock PUBLIC.ACT_RU_EXECUTION while locking PUBLIC.ACT_RU_VARIABLE (exclusive), PUBLIC.ACT_GE_BYTEARRAY (exclusive).
Session #1772 (user: SA) is waiting to lock PUBLIC.ACT_RU_VARIABLE while locking PUBLIC.ACT_RU_EXECUTION (exclusive)."
Deadlock detected. The current transaction was rolled back. Details: "
Session #1676 (user: SA) is waiting to lock PUBLIC.ACT_RU_EXECUTION while locking PUBLIC.ACT_RU_VARIABLE (exclusive), PUBLIC.ACT_GE_BYTEARRAY (exclusive).
Session #1772 (user: SA) is waiting to lock PUBLIC.ACT_RU_VARIABLE while locking PUBLIC.ACT_RU_EXECUTION (exclusive)."; SQL statement:
insert into ACT_RU_VARIABLE (ID_, REV_, TYPE_, NAME_, PROC_INST_ID_, EXECUTION_ID_, TASK_ID_, BYTEARRAY_ID_, DOUBLE_, LONG_ , TEXT_, TEXT2_)
    values (
       ?,
       1,
       ?,
       ?,
      ?,
       ?,
      ?,
       ?,
       ?,
       ?,
       ?,
       ?
    ) [40001-132]
### The error may involve org.activiti.engine.impl.persistence.entity.VariableInstanceEntity.insertVariableInstance-Inline
### The error occurred while setting parameters
### Cause: org.h2.jdbc.JdbcSQLException: Eine Verklemmung (Deadlock) ist aufgetreten. Die aktuelle Transaktion wurde rlückgängig gemacht. Details: "
Session #1676 (user: SA) is waiting to lock PUBLIC.ACT_RU_EXECUTION while locking PUBLIC.ACT_RU_VARIABLE (exclusive), PUBLIC.ACT_GE_BYTEARRAY (exclusive).
Session #1772 (user: SA) is waiting to lock PUBLIC.ACT_RU_VARIABLE while locking PUBLIC.ACT_RU_EXECUTION (exclusive)."
Deadlock detected. The current transaction was rolled back. Details: "
Session #1676 (user: SA) is waiting to lock PUBLIC.ACT_RU_EXECUTION while locking PUBLIC.ACT_RU_VARIABLE (exclusive), PUBLIC.ACT_GE_BYTEARRAY (exclusive).
Session #1772 (user: SA) is waiting to lock PUBLIC.ACT_RU_VARIABLE while locking PUBLIC.ACT_RU_EXECUTION (exclusive)."; SQL statement:
insert into ACT_RU_VARIABLE (ID_, REV_, TYPE_, NAME_, PROC_INST_ID_, EXECUTION_ID_, TASK_ID_, BYTEARRAY_ID_, DOUBLE_, LONG_ , TEXT_, TEXT2_)
    values (
       ?,
       1,
       ?,
       ?,
      ?,
       ?,
      ?,
       ?,
       ?,
       ?,
       ?,
       ?
    ) [40001-132]
   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.insert(DefaultSqlSession.java:107)
   at org.activiti.engine.impl.db.DbSqlSession.flushInserts(DbSqlSession.java:420)
   at org.activiti.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:347)
   at org.activiti.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:147)
   at org.activiti.engine.impl.interceptor.CommandContext.close(CommandContext.java:103)
   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.RuntimeServiceImpl.startProcessInstanceByKey(RuntimeServiceImpl.java:53)
   at org.activiti.camel.ActivitiProducer.startProcess(ActivitiProducer.java:95)
   at org.activiti.camel.ActivitiProducer.process(ActivitiProducer.java:48)
   at org.apache.camel.impl.converter.AsyncProcessorTypeConverter$ProcessorToAsyncProcessorBridge.process(AsyncProcessorTypeConverter.java:50)
   at org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:80)
   at org.apache.camel.processor.SendProcessor$2.doInAsyncProducer(SendProcessor.java:104)
   at org.apache.camel.impl.ProducerCache.doInAsyncProducer(ProducerCache.java:272)
   at org.apache.camel.processor.SendProcessor.process(SendProcessor.java:98)
   at org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:80)
   at org.apache.camel.processor.DelegateAsyncProcessor.processNext(DelegateAsyncProcessor.java:98)
   at org.apache.camel.processor.DelegateAsyncProcessor.process(DelegateAsyncProcessor.java:89)
   at org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:73)
   at org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:80)
   at org.apache.camel.processor.DelegateAsyncProcessor.processNext(DelegateAsyncProcessor.java:98)
   at org.apache.camel.processor.DelegateAsyncProcessor.process(DelegateAsyncProcessor.java:89)
   at org.apache.camel.processor.interceptor.TraceInterceptor.process(TraceInterceptor.java:99)
   at org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:80)
   at org.apache.camel.processor.DelegateAsyncProcessor.processNext(DelegateAsyncProcessor.java:98)
   at org.apache.camel.processor.DelegateAsyncProcessor.process(DelegateAsyncProcessor.java:89)
   at org.apache.camel.fabric.FabricTraceProcessor.process(FabricTraceProcessor.java:59)
   at org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:80)
   at org.apache.camel.processor.RedeliveryErrorHandler.processErrorHandler(RedeliveryErrorHandler.java:314)
   at org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:209)
   at org.apache.camel.processor.DefaultChannel.process(DefaultChannel.java:273)
   at org.apache.camel.processor.UnitOfWorkProcessor.process(UnitOfWorkProcessor.java:132)
   at org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:80)
   at org.apache.camel.processor.DelegateAsyncProcessor.processNext(DelegateAsyncProcessor.java:98)
   at org.apache.camel.processor.DelegateAsyncProcessor.process(DelegateAsyncProcessor.java:89)
   at org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:73)
   at org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:80)
   at org.apache.camel.component.direct.DirectProducer.process(DirectProducer.java:61)
   at org.apache.camel.processor.UnitOfWorkProcessor.process(UnitOfWorkProcessor.java:102)
   at org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:106)
   at org.apache.camel.processor.DelegateAsyncProcessor.process(DelegateAsyncProcessor.java:85)
   at org.apache.camel.processor.UnitOfWorkProducer.process(UnitOfWorkProducer.java:63)
   at org.apache.camel.impl.ProducerCache$1.doInProducer(ProducerCache.java:333)
   at org.apache.camel.impl.ProducerCache$1.doInProducer(ProducerCache.java:303)
   at org.apache.camel.impl.ProducerCache.doInProducer(ProducerCache.java:208)
   at org.apache.camel.impl.ProducerCache.sendExchange(ProducerCache.java:303)
   at org.apache.camel.impl.ProducerCache.send(ProducerCache.java:154)
   at org.apache.camel.impl.DefaultProducerTemplate.send(DefaultProducerTemplate.java:110)
   at org.apache.camel.impl.DefaultProducerTemplate.sendBodyAndProperty(DefaultProducerTemplate.java:185)
   at org.apache.camel.impl.DefaultProducerTemplate.sendBodyAndProperty(DefaultProducerTemplate.java:180)
   at ch.bbp.sandbox.camelintegration.common.producer.Sender.sendAckedMsg(Sender.java:58)
   at ch.bbp.sandbox.camelintegration.common.producer.Sender.call(Sender.java:41)
   at ch.bbp.sandbox.camelintegration.common.producer.Sender.call(Sender.java:14)
   at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
   at java.util.concurrent.FutureTask.run(FutureTask.java:138)
   at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
   at java.lang.Thread.run(Thread.java:662)
Caused by: org.h2.jdbc.JdbcSQLException: Eine Verklemmung (Deadlock) ist aufgetreten. Die aktuelle Transaktion wurde rlückgängig gemacht. Details: "
Session #1676 (user: SA) is waiting to lock PUBLIC.ACT_RU_EXECUTION while locking PUBLIC.ACT_RU_VARIABLE (exclusive), PUBLIC.ACT_GE_BYTEARRAY (exclusive).
Session #1772 (user: SA) is waiting to lock PUBLIC.ACT_RU_VARIABLE while locking PUBLIC.ACT_RU_EXECUTION (exclusive)."
Deadlock detected. The current transaction was rolled back. Details: "
Session #1676 (user: SA) is waiting to lock PUBLIC.ACT_RU_EXECUTION while locking PUBLIC.ACT_RU_VARIABLE (exclusive), PUBLIC.ACT_GE_BYTEARRAY (exclusive).
Session #1772 (user: SA) is waiting to lock PUBLIC.ACT_RU_VARIABLE while locking PUBLIC.ACT_RU_EXECUTION (exclusive)."; SQL statement:
insert into ACT_RU_VARIABLE (ID_, REV_, TYPE_, NAME_, PROC_INST_ID_, EXECUTION_ID_, TASK_ID_, BYTEARRAY_ID_, DOUBLE_, LONG_ , TEXT_, TEXT2_)
    values (
       ?,
       1,
       ?,
       ?,
      ?,
       ?,
      ?,
       ?,
       ?,
       ?,
       ?,
       ?
    ) [40001-132]
   at org.h2.message.DbException.getJdbcSQLException(DbException.java:316)
   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:435)
   at org.h2.table.RegularTable.lock(RegularTable.java:382)
   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:198)
   at org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:179)
   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)
   … 61 more

Outcomes