0

Off late, we have been getting hikari connection pools getting exhausted more than a couple of times. The exception being thrown is as follows:

"org.springframework.transaction.CannotCreateTransactionException: 
Could not open JDBC Connection for transaction;
nested exception is java.sql.SQLTransientConnectionException: 
HikariPool-1 - Connection is not available, request timed out after 30000ms."

What I have observed is that some unaware developers have added @Transactional annotation to a lot of simple get calls on the DB (Postgres).

We use JdbcTemplate to make DB calls with a default connection pool size of 10.

The public endpoints at the controllers are already annotated as @Transactional. Can adding the @Transactional annotation to the DAO Beans result in creation of nested connections when the service layer, which is a seperate bean, which calls the DAO layer is already @Transactional.

We also have a few scheduled CRON jobs, and I see that these are not exposed via a public api or exposed via a controller, but do I still need to add @Transactional to the parent level methods in such cron/internal methods to be able to optimally make db calls? We are not expecting changes to roll back for these cron jobs. We already use JdbcTemplate which uses Hikari Connection poo. Is @Transactional need at all in such cases to optimise performance.

All configurations are default spring boot configurations, so default Tx.Type is REQUIRED unless explicitly set.

M. Deinum
  • 115,695
  • 22
  • 220
  • 224
Sameer
  • 757
  • 1
  • 14
  • 35
  • Add your configuration to question, also does it happen when your system have high traffic or on specific requests? Did you add hikari leak detection property? – Ori Marko Sep 15 '20 at 04:59
  • Yes, we added leak detection, there is not configuration other than spring default. – Sameer Sep 15 '20 at 05:20
  • Maybe you are *over using* `@Transactional` even when not using database, for example in case of validation error – Ori Marko Sep 15 '20 at 05:26
  • Do you use additional connection pool as PGBouncer? – Ori Marko Sep 15 '20 at 06:12
  • For starters making your web layer, the transactional boundary is generally a bad idea as the service layer should be the transactional boundary. On the web, your tx stays active too long and thus connections are held open longer than needed. Regardless of things not expecting to be rolled back, they might every time you do data access you need a transaction, next to safety in java/spring this also allows for optimized resource usage. And make sure you aren't operating on connections directly unless you are also closing them! – M. Deinum Sep 15 '20 at 06:12
  • it will be great if you give some update/feedback – Ori Marko Oct 04 '20 at 09:24
  • @user7294900 No we are not using PGBouncer. I see that if its the same bean, new connection is not used, but if its different beans, it initiates a new connection. – Sameer Oct 07 '20 at 04:59

1 Answers1

0

@Transactional should be mainly use in service layer when you are about to use database connections, it should be used on minimum methods that represent a business flow.

I think you are over using it and may create unnecessary connections, thus create timeout on overloaded pool

Notice that you use only one database connection pool,as HikariCP, and not also PGBouncer for example.

Also see more performance/configuration tweaks for HikariCP in its wiki page

Ori Marko
  • 56,308
  • 23
  • 131
  • 233