Uploaded image for project: 'camunda BPM'
  1. camunda BPM
  2. CAM-14818

Camunda fails variable queries with OptimisticLockException or deadlocks

    XMLWordPrintable

Details

    • Bug Report
    • Status: Closed
    • L3 - Default
    • Resolution: Cannot Reproduce
    • 7.16.0, 7.17.0
    • None
    • engine
    • None

    Description

      Environment (Required on creation):

      linux, java-17, mariadb, camunda-7.17.0, spring-boot-2.7.2

      Description (Required on creation; please attach any relevant screenshots, stacktraces, log files, etc. to the ticket):

      Calling

       

      runtimeService.createVariableInstanceQuery()
            .processInstanceIdIn(getActiveProcessInstanceIds())
            .variableNameIn(variableNames)
            .list(); 

      in a transaction that does not update anything, camunda occasionally fails with two kinds of exceptions:

       

       

      2022-08-18 09:25:56.292 level=ERROR 747375 --- [http-nio-8080-exec-10] correlationId=4f289d0f-e584-454c-aeaa-bf395fe27309, org.camunda.bpm.engine.context           : ENGINE-16004 Exception while closing command context: An exception occurred in the persistence layer. Please check the server logs for a detailed message and the entire exception stack trace.
      org.camunda.bpm.engine.ProcessEngineException: An exception occurred in the persistence layer. Please check the server logs for a detailed message and the entire exception stack trace.
      	at org.camunda.bpm.engine.impl.util.ExceptionUtil.wrapPersistenceException(ExceptionUtil.java:263)
      	at org.camunda.bpm.engine.impl.db.EnginePersistenceLogger.flushDbOperationException(EnginePersistenceLogger.java:133)
      	at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.flushDbOperations(DbEntityManager.java:364)
      	at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.flushDbOperationManager(DbEntityManager.java:323)
      	at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.flush(DbEntityManager.java:295)
      	at org.camunda.bpm.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:272)
      	at org.camunda.bpm.engine.impl.interceptor.CommandContext.close(CommandContext.java:188)
      	at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:119)
      	at org.camunda.bpm.engine.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:72)
      	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
      	at org.camunda.bpm.engine.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:70)
      	at org.camunda.bpm.engine.impl.interceptor.ProcessApplicationContextInterceptor.execute(ProcessApplicationContextInterceptor.java:70)
      	at org.camunda.bpm.engine.impl.interceptor.CommandCounterInterceptor.execute(CommandCounterInterceptor.java:35)
      	at org.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)
      	at org.camunda.bpm.engine.impl.AbstractQuery.executeResult(AbstractQuery.java:160)
      	at org.camunda.bpm.engine.impl.AbstractQuery.list(AbstractQuery.java:142)
      
      ...
      
      Caused by: org.camunda.bpm.engine.ProcessEngineException: ENGINE-03004 Exception while executing Database Operation 'UPDATE VariableInstanceEntity[04ff3d83-da8e-11ec-8c15-001a4a3b025a]' with message '
      ### Error updating database.  Cause: java.sql.SQLException: (conn=6451307) Lock wait timeout exceeded; try restarting transaction
      ### The error may exist in org/camunda/bpm/engine/impl/mapping/entity/VariableInstance.xml
      ### The error may involve org.camunda.bpm.engine.impl.persistence.entity.VariableInstanceEntity.updateVariableInstance-Inline
      ### The error occurred while setting parameters
      ### SQL: update ACT_RU_VARIABLE     set       REV_ = ?,       EXECUTION_ID_ = ?,       VAR_SCOPE_ = ?,       TYPE_ = ?,      BYTEARRAY_ID_ = ?,      DOUBLE_ = ?,      LONG_ = ?,      TEXT_ = ?,      TEXT2_ = ?,       SEQUENCE_COUNTER_ = ?     where ID_ = ?       and REV_ = ?
      ### Cause: java.sql.SQLException: (conn=6451307) Lock wait timeout exceeded; try restarting transaction'. Flush summary: 
       [
        INSERT HistoricVariableUpdateEventEntity[e03cebc4-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e03f83d8-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e0421bec-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e04465e0-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e046d6e4-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e0496ef8-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e04bdffc-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e04e7810-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e050e914-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e0535a18-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e056193c-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e0586330-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e05ad434-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e05d9358-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e060045c-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e0627560-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e064e564-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e0677d78-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e069ee7c-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e06c5f80-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e06ed084-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e0714188-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e0738b7c-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e0762390-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e0789494-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e07b0598-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e07d9dac-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e0800eb0-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e0827fb4-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e084f0b8-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e0873aac-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e089d2c0-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e08c43c4-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e08edbd8-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e09125cc-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e09396d0-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e0962ee4-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e09878d8-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e09b10ec-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT HistoricVariableUpdateEventEntity[e09d81f0-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e03cebc2-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e03cebc3-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e03e2445-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e03f83d6-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e03f83d7-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e040bc59-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e0421bea-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e0421beb-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e0432d5d-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e04465de-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e04465df-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e0459e61-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e046d6e2-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e046d6e3-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e0480f65-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e0496ef6-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e0496ef7-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e04aa779-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e04bdffa-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e04bdffb-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e04d187d-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e04e780e-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e04e780f-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e04f8981-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e050e912-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e050e913-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e0522195-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e0535a16-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e0535a17-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e054b9a9-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e056193a-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e056193b-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e0572aad-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e058632e-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e058632f-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e0599bb1-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e05ad432-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e05ad433-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e05c33c5-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e05d9356-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e05d9357-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e05ecbd9-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e060045a-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e060045b-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e0613cdd-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e062755e-1ec6-11ed-8fc6-001a4a3b0259]
        INSERT ByteArrayEntity[e062755f-1ec6-11ed-8fc6-001a4a3b0259]

      or

      2022-08-17 12:14:46.347 level=ERROR 747375 --- [http-nio-8080-exec-3] correlationId=000953cb-fefe-4c9c-a546-63dd2ddf1fc7, o.z.problem.spring.common.AdviceTraits   : Internal Server Error
      org.camunda.bpm.engine.OptimisticLockingException: ENGINE-03005 Execution of 'UPDATE VariableInstanceEntity[04ff3d83-da8e-11ec-8c15-001a4a3b025a]' failed. Entity was updated by another transaction concurrently.
      	at org.camunda.bpm.engine.impl.db.EnginePersistenceLogger.concurrentUpdateDbEntityException(EnginePersistenceLogger.java:137)
      	at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.handleConcurrentModification(DbEntityManager.java:413)
      	at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.flushDbOperations(DbEntityManager.java:356)
      	at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.flushDbOperationManager(DbEntityManager.java:323)
      	at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.flush(DbEntityManager.java:295)
      	at org.camunda.bpm.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:272)
      	at org.camunda.bpm.engine.impl.interceptor.CommandContext.close(CommandContext.java:188)
      	at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:119)
      	at org.camunda.bpm.engine.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:72)
      	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
      	at org.camunda.bpm.engine.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:70)
      	at org.camunda.bpm.engine.impl.interceptor.ProcessApplicationContextInterceptor.execute(ProcessApplicationContextInterceptor.java:70)
      	at org.camunda.bpm.engine.impl.interceptor.CommandCounterInterceptor.execute(CommandCounterInterceptor.java:35)
      	at org.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)
      	at org.camunda.bpm.engine.impl.AbstractQuery.executeResult(AbstractQuery.java:160)
      	at org.camunda.bpm.engine.impl.AbstractQuery.list(AbstractQuery.java:142)2022-08-17 12:14:46.347 level=ERROR 747375 --- [http-nio-8080-exec-3] correlationId=000953cb-fefe-4c9c-a546-63dd2ddf1fc7, o.z.problem.spring.common.AdviceTraits   : Internal Server Error
      org.camunda.bpm.engine.OptimisticLockingException: ENGINE-03005 Execution of 'UPDATE VariableInstanceEntity[04ff3d83-da8e-11ec-8c15-001a4a3b025a]' failed. Entity was updated by another transaction concurrently.
      	at org.camunda.bpm.engine.impl.db.EnginePersistenceLogger.concurrentUpdateDbEntityException(EnginePersistenceLogger.java:137)
      	at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.handleConcurrentModification(DbEntityManager.java:413)
      	at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.flushDbOperations(DbEntityManager.java:356)
      	at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.flushDbOperationManager(DbEntityManager.java:323)
      	at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.flush(DbEntityManager.java:295)
      	at org.camunda.bpm.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:272)
      	at org.camunda.bpm.engine.impl.interceptor.CommandContext.close(CommandContext.java:188)
      	at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:119)
      	at org.camunda.bpm.engine.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:72)
      	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
      	at org.camunda.bpm.engine.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:70)
      	at org.camunda.bpm.engine.impl.interceptor.ProcessApplicationContextInterceptor.execute(ProcessApplicationContextInterceptor.java:70)
      	at org.camunda.bpm.engine.impl.interceptor.CommandCounterInterceptor.execute(CommandCounterInterceptor.java:35)
      	at org.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)
      	at org.camunda.bpm.engine.impl.AbstractQuery.executeResult(AbstractQuery.java:160)
      	at org.camunda.bpm.engine.impl.AbstractQuery.list(AbstractQuery.java:142) 

       

      The transation is started on http request (spring-mvc).

      Steps to reproduce (Required on creation):

      Not easy, actually. I guess it happens on larger load.

      Observed Behavior (Required on creation):

      Exception gets thrown.

      Expected behavior (Required on creation):

      Read operations should not result in deadlocks or OptimistiLockExceptions.

      Root Cause (Required on prioritization):

      Internally Camunda writes to its DB when only asked to read from it.

      Solution Ideas (Optional):

      Remove the code writing to DB on read-only queries.

      Hints (optional):

      Other complaints include:

      https://forum.camunda.io/t/taskserviceimpl-getvariables-results-into-db-insertion-of-history-table-data/4752

      https://forum.camunda.io/t/optimisticlockingexception-during-job-execution-caused-by-createvariableinstancequery/10111

      https://forum.camunda.io/t/how-to-prevent-get-variables-from-being-stored-in-the-database-when-using-getvariable/30063

      One of them suggests adding disableCustomObjectDeserialization() when constructing the query. But I need it for JSON serialized vars

      mgm-controller-panel

        This is the controller panel for Smart Panels app

        Attachments

          Activity

            People

              miklas.boskamp Miklas Boskamp
              andrius Andrius Kurtinaitis
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Salesforce