Deadlock ActivityInstanceEntityImpl.updateActivityInstance-Inline

Flowable Engine
1

We use flowable 6.5.0 and MariaDB 10.3 und see following deadlock while running different process-instances in parallel:

### Error updating database.  Cause: java.sql.SQLTransactionRollbackException: (conn=45) Deadlock found when trying to get lock; try restarting transaction
### The error may exist in org/flowable/db/mapping/entity/ActivityInstance.xml
### The error may involve org.flowable.engine.impl.persistence.entity.ActivityInstanceEntityImpl.updateActivityInstance-Inline
### The error occurred while setting parameters
### SQL: update ACT_RU_ACTINST      SET REV_ = ?,                      PROC_DEF_ID_ = ?,                                                          END_TIME_ = ?,    
                           DURATION_ = ?      where ID_ = ?     and REV_ = ?

### Cause: java.sql.SQLTransactionRollbackException: (conn=45) Deadlock found when trying to get lock; try restarting transaction
        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30) ~[!/:?]
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:199) ~[!/:?]
        at org.flowable.common.engine.impl.db.DbSqlSession.flushUpdates(DbSqlSession.java:570) ~[!/:?]
        at org.flowable.common.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:359) ~[!/:?]
        at org.flowable.common.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:192) ~[!/:?]
        at org.flowable.common.engine.impl.interceptor.CommandContext.close(CommandContext.java:61) ~[!/:?]

        at org.flowable.common.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:81) ~[!/:?]

        at org.flowable.common.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30) ~[!/:?]

Root Cause:

Caused by: java.sql.SQLException: Deadlock found when trying to get lock; try restarting transaction
        at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1594) ~[!/:?]
        at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1453) ~[!/:?]
        at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1415) ~[!/:?]
        at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:289) ~[!/:?]
        at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:221) ~[!/:?]
        at org.mariadb.jdbc.ClientSidePreparedStatement.execute(ClientSidePreparedStatement.java:157) ~[!/:?]
        at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:198) ~[!/:2.1.1]
        at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:198) ~[!/:2.1.1]
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47) ~[!/:?]
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) ~[!/:?]
        at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) ~[!/:?]
        at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) ~[!/:?]
        at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) ~[!/:?]
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197) ~[!/:?]

Any ideas?

2

Not immediately no … the error seems to indicate two transactions are both trying to update the same activity instance (or there’s another set of rows involved that aren’t showed in the error message). Is there anything particular about these instances? Are multiple threads completing the same steps?

3

No nothing special, we have just one engine running. The deadlock seems to occur, when other process-instances are running, so there should be multiple threads updating the same table but not the same row.

Or is there some special locking mechanism implemented which locks the whole table?

4

@joram Looking at the deadlocks happening in our production system, it turns out, that deadlocks appear always when process-instances are completing (i.e. the data from the runtime tables is going to be deleted).

So there is always one transaction deleting from ACT_RU_* and another transaction manipulating (insert or update) ACT_RU_*, e.g.

LATEST DETECTED DEADLOCK

2020-09-18 13:59:15 0x7f5cbd1b8700
*** (1) TRANSACTION:
TRANSACTION 167187, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 7
MySQL thread id 106, OS thread handle 140036287338240, query id 35720567 localhost 127.0.0.1 karaf Update
insert into ACT_RU_VARIABLE (ID_, REV_, TYPE_, NAME_, PROC_INST_ID_, EXECUTION_ID_, TASK_ID_, SCOPE_ID_, SUB_SCOPE_ID_, SCOPE_TYPE_,
            BYTEARRAY_ID_, DOUBLE_, LONG_ , TEXT_, TEXT2_)
        values (
            '53494',
            1,
            'string',
            'callbackStatus',
            '53471',
            '53471',
            NULL,
            NULL,
            NULL,
            NULL,
            NULL,
            NULL,
            NULL,
            'COMPLETED_SUCCESS',
            NULL
        )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 53 page no 3 n bits 160 index PRIMARY of table `karaf`.`ACT_RU_VARIABLE` trx id 167187 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 167158, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
17 lock struct(s), heap size 1136, 107 row lock(s), undo log entries 43
MySQL thread id 99, OS thread handle 140036286416640, query id 35720568 localhost 127.0.0.1 karaf Updating
delete from ACT_RU_ACTINST where PROC_INST_ID_ = '53436'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 53 page no 3 n bits 160 index PRIMARY of table `karaf`.`ACT_RU_VARIABLE` trx id 167158 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Maybe this points you to the right direction?

5

After setting the mysql transaction isolation level from REPEATABLE_READ to READ_COMMITTED, the deadlock is gone.

This brings me to the conclusion, that some READ statement causes the trouble. I see in our code, that we are performing a runtime query for an execution (runtimeService.createQuery…) before triggering the execution (runtimeService.trigger(executionId)).

We have no TransactionManager configured.

Question: Does the select statement (runtimeService.createQuery…) and update statement (runtimeService.trigger(executionId)) run in the same transaction? And if yes, how can I achieve, that in runs in a seperate transaction?

1 Like