Error updating database, Deadlock. Cause: org.postgresql.util.PSQLException: ERROR: insert or update on table "act_ru_execution" violates foreign key constraint "act_fk_exe_parent"

I have a problem. There is an edge case on our environment when camunda engine cannot add
the executions to table act_ru_execution. I cannot easily reproduce it because It happens only when there is high traffic on environment.

[EDIT]
Our set up:
Camunda 7.17.0
SpringBoot 2.7.6
PostgreSQL 13.10

Error happens when I try to complete “UserTask1”. After error occuring, process instance stay in UserTask1 and I cannot keep going processing.

Do you see something wrong in my my diagram? Maybe something can be done better so that I can avoid this error. I’m especially suspicious of first parrarel gateway in my diagram.

My process diagram looks like this:

And logs:

java.sql.BatchUpdateException: Batch entry 0 delete from ACT_RU_EXECUTION where ID_ = 'bd7a7b8c-006d-11ee-b1a5-0a580a800424' and REV_ = 1 was aborted: ERROR: deadlock detected

2023-05-31 12:00:41.486 [,] ERROR [camunda,24fe50e47c0997cd,24fe50e47c0997cd] 1 --- [io-8080-exec-10] 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.ExternalTaskServiceImpl.complete(ExternalTaskServiceImpl.java:64)
	at org.camunda.bpm.engine.rest.sub.externaltask.impl.ExternalTaskResourceImpl.complete(ExternalTaskResourceImpl.java:119)
	at jdk.internal.reflect.GeneratedMethodAccessor649.invoke(Unknown Source)
Caused by: org.camunda.bpm.engine.ProcessEngineException: ENGINE-03004 Exception while executing Database Operation 'INSERT ExecutionEntity[c3e63383-ffaa-11ed-9fe9-0a580a80041f]' with message 
Error flushing statements.  Cause: org.apache.ibatis.executor.BatchExecutorException: org.camunda.bpm.engine.impl.persistence.entity.ExecutionEntity.insertExecution (batch index #7) failed. 6 prior sub executor(s) completed successfully, but will be rolled back. Cause: java.sql.BatchUpdateException: Batch entry 0 insert into ACT_RU_EXECUTION
    (
      ID_,
      ROOT_PROC_INST_ID_,
      PROC_INST_ID_,
      BUSINESS_KEY_,
      PROC_DEF_ID_,
      ACT_ID_,
      ACT_INST_ID_,
      IS_ACTIVE_,
      IS_CONCURRENT_,
      IS_SCOPE_,
      IS_EVENT_SCOPE_,
      PARENT_ID_,
      SUPER_EXEC_,
      SUPER_CASE_EXEC_,
      CASE_INST_ID_,
      SUSPENSION_STATE_,
      CACHED_ENT_STATE_,
      SEQUENCE_COUNTER_,
      TENANT_ID_,
      REV_
    )
    values
    (
      'c3e63383-ffaa-11ed-9fe9-0a580a80041f',
      NULL,
      '5b96a833-ffa5-11ed-9fe9-0a580a80041f',
      NULL,
      'OZKH:23:80c6fc5e-fe26-11ed-8ff4-0a580a820653',
      'Activity_1wbwrgs',
      'Activity_1wbwrgs:c3e63384-ffaa-11ed-9fe9-0a580a80041f',
      'TRUE',
      'FALSE',
      'TRUE',
      'FALSE',
      'c2ee3f12-ffaa-11ed-9fe9-0a580a80041f',
      NULL,
      NULL,
      NULL,
      1,
      128,
      65,
      NULL,
      1
    ) was aborted: 
ERROR: insert or update on table "act_ru_execution" violates foreign key constraint "act_fk_exe_parent"


 Flush summary: 
 [
  INSERT HistoricVariableInstanceEntity[c3e41093-ffaa-11ed-9fe9-0a580a80041f]
  INSERT HistoricVariableInstanceEntity[c3e45eb9-ffaa-11ed-9fe9-0a580a80041f]
  INSERT HistoricVariableInstanceEntity[c3e45ebb-ffaa-11ed-9fe9-0a580a80041f]
  INSERT HistoricVariableInstanceEntity[c3e63380-ffaa-11ed-9fe9-0a580a80041f]
  INSERT HistoricVariableUpdateEventEntity[c3e41094-ffaa-11ed-9fe9-0a580a80041f]
  INSERT HistoricVariableUpdateEventEntity[c3e41096-ffaa-11ed-9fe9-0a580a80041f]
  INSERT HistoricVariableUpdateEventEntity[c3e45eba-ffaa-11ed-9fe9-0a580a80041f]
  INSERT HistoricVariableUpdateEventEntity[c3e45ebc-ffaa-11ed-9fe9-0a580a80041f]
  INSERT HistoricVariableUpdateEventEntity[c3e63381-ffaa-11ed-9fe9-0a580a80041f]
  INSERT HistoricExternalTaskLogEntity[c3e19f8c-ffaa-11ed-9fe9-0a580a80041f]
  INSERT HistoricExternalTaskLogEntity[c3e65a96-ffaa-11ed-9fe9-0a580a80041f]
  INSERT HistoricDecisionInstanceEntity[c3e6337d-ffaa-11ed-9fe9-0a580a80041f]
  INSERT HistoricDecisionInputInstanceEntity[c3e6337e-ffaa-11ed-9fe9-0a580a80041f]
  INSERT HistoricDecisionInputInstanceEntity[c3e6337f-ffaa-11ed-9fe9-0a580a80041f]
  INSERT HistoricActivityInstanceEventEntity[Activity_07ygu3s:c3e41095-ffaa-11ed-9fe9-0a580a80041f]
  INSERT HistoricActivityInstanceEventEntity[Activity_0p6sf0k:c3e45eb8-ffaa-11ed-9fe9-0a580a80041f]
  INSERT HistoricActivityInstanceEventEntity[Activity_0tua7hy:c3e214bf-ffaa-11ed-9fe9-0a580a80041f]
  INSERT HistoricActivityInstanceEventEntity[Activity_1wbwrgs:c3e63384-ffaa-11ed-9fe9-0a580a80041f]
  INSERT HistoricActivityInstanceEventEntity[Gateway_0vspyln:c3e63382-ffaa-11ed-9fe9-0a580a80041f]
  INSERT ExecutionEntity[c3e63383-ffaa-11ed-9fe9-0a580a80041f]
  INSERT VariableInstanceEntity[c3e41093-ffaa-11ed-9fe9-0a580a80041f]
  INSERT VariableInstanceEntity[c3e63380-ffaa-11ed-9fe9-0a580a80041f]
  INSERT ExternalTaskEntity[c3e65a95-ffaa-11ed-9fe9-0a580a80041f]
  DELETE ExternalTaskEntity[c2f718c7-ffaa-11ed-9fe9-0a580a80041f]
  UPDATE VariableInstanceEntity[6106b742-ffa5-11ed-9fe9-0a580a80041f]
  DELETE VariableInstanceEntity[c2f718c5-ffaa-11ed-9fe9-0a580a80041f]
  UPDATE ExecutionEntity[c2ee3f12-ffaa-11ed-9fe9-0a580a80041f]
  DELETE ExecutionEntity[c2f718c3-ffaa-11ed-9fe9-0a580a80041f]
  UPDATE HistoricActivityInstanceEventEntity[Activity_0auvz6p:c2f718c4-ffaa-11ed-9fe9-0a580a80041f]

I would start by refactoring the two Boundary Non-Interruption EMail into Event Based Non-Interrupting, but that might have unintended side effects (ie. the email can cause the Data Export while other steps are running…)

You might also consider splitting the Data Extract portion out to a distinct process, that you can then use a message start to kick off in parallel.

I removed non-interrupting message boundary event from “UserTask1” and error disappeared.
Can I somehow replace this event without changing process logic?

I don’t understand. Into what did you suggest to refactor? What is “Event Based non-Interrupting”?

I phrased it poorly…

I was suggesting that you could refactor using Event Subprocess | docs.camunda.org
and move all the “Extract Data” logic into that subprocess.

Did you mean something like this?

Close, you’ll need to put an “Event Subprocess” around the section at the bottom.

Also, per BPMN specification, you CANNOT send a message from the process to itself.
But the event subprocess can catch the message that UserTask1 is catching (so remove if from the Boundary) and simply react directly.
The alternative is to make the section at the bottom its own process, which listens for the message, or can be called by the main process.

I made something like this, but error still occur.

That shouldn’t cause a deadlock, unless you’re trying update the process data in the DB directly in one of the script tasks.