Job acquisition is not happening in flowable

Flowable Engine
1

Hi Team,

We have an issue with jobs getting stuck in our engine. Below are the details.

DB : mysql
Flowable version: 6.7.2
parameter global-acquire-lock-enabled=true
Parallel gateway and exclusive gateways have async set and joining parallel gateway has exclusive set.

error details:

org.apache.ibatis.exceptions.PersistenceException:

Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

The error may exist in org/flowable/job/service/db/mapping/entity/Job.xml

The error may involve org.flowable.job.service.impl.persistence.entity.JobEntityImpl.insertJob-Inline

The error occurred while setting parameters

SQL: insert into ACT_RU_JOB ( ID_, REV_, CATEGORY_, TYPE_, LOCK_OWNER_, LOCK_EXP_TIME_, EXCLUSIVE_, EXECUTION_ID_, PROCESS_INSTANCE_ID_, PROC_DEF_ID_, ELEMENT_ID_, ELEMENT_NAME_, SCOPE_ID_, SUB_SCOPE_ID_, SCOPE_TYPE_, SCOPE_DEFINITION_ID_, CORRELATION_ID_, RETRIES_, EXCEPTION_STACK_ID_, EXCEPTION_MSG_, DUEDATE_, REPEAT_, HANDLER_TYPE_, HANDLER_CFG_, CUSTOM_VALUES_ID_, CREATE_TIME_, TENANT_ID_) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )

Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction.

2

Further to above details, i have added error details.

Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

SQL: insert into ACT_RU_JOB ( ID_, REV_, CATEGORY_, TYPE_, LOCK_OWNER_, LOCK_EXP_TIME_, EXCLUSIVE_, EXECUTION_ID_, PROCESS_INSTANCE_ID_, PROC_DEF_ID_, ELEMENT_ID_, ELEMENT_NAME_, SCOPE_ID_, SUB_SCOPE_ID_, SCOPE_TYPE_, SCOPE_DEFINITION_ID_, CORRELATION_ID_, RETRIES_, EXCEPTION_STACK_ID_, EXCEPTION_MSG_, DUEDATE_, REPEAT_, HANDLER_TYPE_, HANDLER_CFG_, CUSTOM_VALUES_ID_, CREATE_TIME_, TENANT_ID_) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )

The error occurred while setting parameters

The error may involve org.flowable.job.service.impl.persistence.entity.JobEntityImpl.insertJob-Inline

The error may exist in org/flowable/job/service/db/mapping/entity/Job.xml

Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

Caused by: org.apache.ibatis.exceptions.PersistenceException:

Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

SQL: delete from ACT_GE_BYTEARRAY where ID_ = ? and REV_ = ?

The error occurred while setting parameters

The error may involve org.flowable.common.engine.impl.persistence.entity.ByteArrayEntityImpl.deleteByteArray-Inline

The error may exist in org/flowable/common/db/mapping/entity/ByteArray.xml

Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

Caused by: org.apache.ibatis.exceptions.PersistenceException:

4

This is a MySQL exception, not a Flowable exception. It means that the database is under high load and can’t get a lock to a row it needs in acceptable time, because other transactions have also requested a lock for it.

How is this mysql set up (hardware / configuration wise)?

5

HI Joram,

Here i mentioned the configuration of our MySQL DB and also the memory and CPU details of the production server.

Mysql parameters -

#server#####:~ # cat /etc/my.cnf

For advice on how to change settings please see

MySQL :: MySQL 8.0 Reference Manual :: 5.1.2 Server Configuration Defaults

[mysqld]

Remove leading # and set to the amount of RAM for the most important data

cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

innodb_buffer_pool_size = 128M

Remove the leading "# " to disable binary logging

Binary logging captures changes between backups and is enabled by

default. It’s default setting is log_bin=binlog

disable_log_bin

Remove leading # to set options mainly useful for reporting servers.

The server defaults are faster for transactions and fast SELECTs.

Adjust sizes as needed, experiment to find the optimal values.

join_buffer_size = 128M

sort_buffer_size = 2M

read_rnd_buffer_size = 2M

Remove leading # to revert to previous value for default_authentication_plugin,

this will increase compatibility with older clients. For background, see:

MySQL :: MySQL 8.0 Reference Manual :: 5.1.8 Server System Variables

default-authentication-plugin=mysql_native_password

#datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

basedir=/data/mysql

datadir=/data/mysql/data

lc_messages_dir=/data/mysql/share

lc_messages=en_US

socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysql/mysqld.log

pid-file=/var/run/mysql/mysqld.pid

log-bin=/data/mysql/log/mysql-bin

#log-error=/var/log/mysql/mysqld.log

#pid-file=/var/run/mysql/mysqld.pid

expire_logs_days=30

MySQL log files

Errors Including Startup Errors

log-error=/var/log/mysql/mysqld.log

all queries, This log will grow quickly

#log=/var/log/mysql/mysql.log

all queries, This log will grow quickly

general_log=1

general_log_file=/var/log/mysql/mysql.log

queries that take a long time (in this case over 1 second as specified below)

#log-slow-queries = /var/log/mysql/mysql-slow.log

long_query_time = 1

slow_query_log=1

slow_query_log_file=/var/log/mysql/mysql-slow.log

Purge flushed binary logs over 30 days = 302460*60 seconds

binlog_expire_logs_seconds=2592000

###To enhance mysql performance as per play and test

innodb-buffer-pool-size=21474836480

innodb_buffer_pool_instances=20

innodb_log_file_size=4294967296

sort_buffer_size = 50M

read_rnd_buffer_size = 50M

join_buffer_size = 256M

innodb_log_buffer_size = 167772160

innodb_sort_buffer_size = 10485760

innodb_read_io_threads = 16

innodb_write_io_threads = 16

innodb_status_output = ON

innodb_status_output_locks = ON

innodb_rollback_on_timeout = ON

server details:
Platform : Google x86_64
Total CPU : 32 CPUs Total Memory : 128923 MB
OS Release : SLES 12 SP5 OS kernel : 4.12.14-122.136-default

Let me know if you need any further details.

Regards,
Preeti Shirur

6

Hi Joram,

I am closing this thread and opening other one for lock owner issue.

Regards,
Preeti Shirur

7

Just scanning through the settings, that’s 21GB RAM (innodb-buffer-pool-size), right?