Postgres idling transaction for several hours.

[abel.surace]

Hi, hopefully somebody here can give us some guidelines to fix or diagnose this issue since there little to none documentation on how Servoy deals with this.
Recently we received a notofication from our dba manager that a transaction created from our servoy application had a select query iddling and lockingup some tables. This prevented other users from accesing those tables. The idling started on a Friday and was detected on sunday when the culprit conection was killed. Our servoy dev team was required to ensure autocommit is set to true at all times which might solve this issue from happening but not sure how to achieve that from within servoy, probably we need to add a setting to our db conection setup within the servoy.properties file?. In our code transactions are commited on success or rolled back if they fail but there might be cases where users might disconnect in the middle of a transaction not reaching to the commit or roll back so the question is how does Servoy handle this cases?, Does Servoy autocommit when a conection is released back to the pool?
I would appreciate any help to guide what is the best way to avoid this scenario and also how to ensure PG autocommit is set to true from within Servoy.

[rgansevles]

Hi Abel,

If you don't start transactions within your solution (databaseManager.startTransaction()), Servoy will very shortly set autocommit to false when doing updates, this is reset immediately after success (commit) or failure (rollback).

When a connection is returned to the connection pool, it will never have a transaction running.
It must be a solution still holding on to the connection.

Did you see what select query was locking the tables?

[abel.surace]

Hi Rob, thanks for the repply. yes we have a list of queries issued from within servoy captured by our dba manager that are iddling longer than 5min. We still need to figure out exactly where in the code those queries where initiated. We widely use databaseManager.getDataSetByQuery(server, query, null, 1).getValue(1, 1), however we do not initiate the transacion with databaseManager.startTransaction() before trying getDataSetByQuery. Would this still behave as you described, commit on succes, and rollback on fail and reset autocommit back to true?, the queries we see with issues are just regular selects not updates.

After further investigation, one of the iddling queries matches an auto generated query when the solution executes foundset.load() and another matches a relation

00:00:00:063 | 66 |00:00:000 | Relation | select item_id, a_ind, bar_code, catalogue_date, catalogue_name, . . . .
00:00:00:059 | 22 |00:00:002| Load foundset | select item_id, a_ind, bar_code, catalogue_date, catalogue_name, . . . .

Another question is, in case the solution is holding on to connections with pending transactions, how can we ensure any pending transaction is roll back and conection is returned to the pool when a user decides to pull the plug at any time, either it logs off or closes the browser etc, does servoy do a house cleaning for sessions no longer active?.

[rgansevles]

Hi Abel,

Hi Rob, thanks for the repply. yes we have a list of queries issued from within servoy captured by our dba manager that are iddling longer than 5min. We still need to figure out exactly where in the code those queries where initiated. We widely use databaseManager.getDataSetByQuery(server, query, null, 1).getValue(1, 1), however we do not initiate the transacion with databaseManager.startTransaction() before trying getDataSetByQuery. Would this still behave as you described, commit on succes, and rollback on fail and reset autocommit back to true?, the queries we see with issues are just regular selects not updates.

Yes, all queries executed within Servoy go through that same scenario.

After further investigation, one of the iddling queries matches an auto generated query when the solution executes foundset.load() and another matches a relation

00:00:00:063 | 66 |00:00:000 | Relation | select item_id, a_ind, bar_code, catalogue_date, catalogue_name, . . . .
00:00:00:059 | 22 |00:00:002| Load foundset | select item_id, a_ind, bar_code, catalogue_date, catalogue_name, . . . .

I think the queries that are idling are not the ones that are blocking, but the ones that are blocked by another update.
It would not make a difference if the select queries run with autocommit true or false, in both cases they would be blocked by the same update.

Another question is, in case the solution is holding on to connections with pending transactions, how can we ensure any pending transaction is roll back and conection is returned to the pool when a user decides to pull the plug at any time, either it logs off or closes the browser etc, does servoy do a house cleaning for sessions no longer active?.

When a browser window is closed, client will be shutdown after some time, all transactions for that client will be rolled back.