ACT_RU_EXECUTION table update query is running for hours

1

The below query is taking for hours and hours resulting in timeout and max connection issues in postgres db. Total duration is showing as 28 hrs, 93 hrs, etc. Not sure whether it is related to any locking but we are unable to get any logs related to it.

update ACT_RU_EXECUTION
SET REV_ = $1,
IS_ACTIVE_ = $2
where ID_ = $3 and REV_ = $4

Any possible reason or configuration we need to check ? We recent made history writing logic as ASYNC not sure whether its causing any issue.

2

Isn’t the problem in the running transaction?
Is any other thread trying to update the same row?

Regards
Martin

3

we couldnt find much from the logs, but this is occurring in the environment where we have 2 instance of the application up and running.

from the azure query insights , we were able to identify that this query is running for almost 94 hrs , 28 hrs, 25 hrs, etc. once the application is restarted, db becomes normal. but slowly it is building up in the background and aftersometime, all connections are utilised and starts throwing connection not found errors.

thanks

4

If you have access to the database, run this query:
select * from pg_stat_activity where state <> 'idle'
You will be able to see all the active transactions and how long they have been running.
I suspect that you will see the long running ones with state ‘idle in transaction’.
In this case you likely have an external factor causing the issue.
For example:
If I create a script task that sends an HTTP request with no timeout and the response never comes from the web server, then Flowable will wait forever for the response. While it is waiting it does not close the connection to the database server, because it has possibly executed some insert statements and is not ready to commit those changes until the process gets to the next async section or user task in the process. Hope that makes sense. You should see if you can find where the process is getting stuck waiting for something else to happen.

5

Thanks for sharing the query. Will try the same and get back with the results.

6

We were able to see 2 transactions where the first query was idle in transaction aborted and the second one in idle in transaction. The second call seems to be using the first broken transaction and they have resulted in a dead lock.

Is there a way to configure in the idle-in-transaction timeout for flowable ?

7

Have a look at these links:
https://jdbc.postgresql.org/documentation/use/

You may be able to use your connection string to set various timeout values for the DB connection.

8

Thanks for sharing the documentation.

We already looked into that and since its at the database level, we thought of checking whether we can configure anything specific to flowabl without affecting other services that are talking to that db.

We will look into it further.