Idle transaction PostgreSQL

Lately there is so many idle transanctions, the cause is one of the sequences created automatically by CUBA as you can see on this image:

Platform is 6.8.7 and PostgreSQL version is 10.3

You said “lately”, does it mean you didn’t see these idle transactions with previous versions of CUBA, or you didn’t noticed them before?

I ask this, because for what I can see CUBA uses a PreparedStatement to issue that command, but I think it doesn’t set auto-commit. After the command it closes the prepared statement, but closing it doesn’t guarantee a COMMIT.
So this behaviour should be present even in previous versions of the framework…


EDIT: that’s ok if getting the next sequence value must be executed in the context of another transaction, you definitely don’t want your transaction to be closed prematurely… check other parts of your code, especially where you create new entities

Hi @victor.sanchez ,

Unfortunately, we are unable to reproduce the problem. So could you please bring more details of your system?
What does the screenshot mean? Why are there several similar sessions on one database? Are several applications work with the same DB?
Which ID has the entity “Pedido”: Integer or Long?
How many records has the table?
Could you also share the properties on the “seq_id_pedidos_Pedido” sequence (current value and increment)?

As you can see in source code
This method uses persistence.getTransaction() - that means it joins existing transaction if called inside of another transaction.

So there is a chance that you’ve called UniqueNumbersAPI.getNextNumber() inside of another transaction and for some reason outer transaction was not committed (tx.end() missing or something like that).

1 Like

It was outside of any transaction.
I changed the code to put it inside a transaction and now there is not idle transactions.