long open db connections

1
Suppose that you have a microflow with these actions : Retrieve data drom db → 2. Rest action → 3. Update data in database   It seems like on first data retrieve action, a db connection is taken from the pool and kept in use for the entire microflow execution.  (what I saw in the Oracle Java mission control app > MBean : org.apache.commons.pool2 > NumActive) This means that when the Rest action takes 10 seconds, the db connection is kept in use and is not available for others. How to release the db connection for the duration of the REST action? I tried adding Start- and EndTransaction before and after the REST action, but that doesn’t change this behaviour.
asked
2 answers
1

Do you actually run into an issue where your connection pool gets depleted?

I would worry about my users having to wait over 10 seconds to get a response, not about micromanaging my connection pool.

If your database runs into locking issues because of long running transactions that’s something I would investigate because it shouldn't.

For reference; if for some weird reason you would want to tackle this issue (I can think of some scenario's), what you should do is do every separate part of your action in a separate transaction. The original transaction will still be open, but since it doesn't do anything it will not have any impact whatsoever. If you would do this you would need to have a very clear idea on what kind of transactional integrity you still want to achieve.

answered
0

 

“How to release the db connection for the duration of the REST action? I tried adding Start- and EndTransaction before and after the REST action, but that doesn’t change this behaviour.”

By doing this you place the REST action in a separated transaction, but keep the original transaction started at the DB retrieve. So I suspect that placing the Retrieve in a separated transaction would do the trick. 

 

answered