Monday, 13 November 2017

Scaling database connection pools using LazyConnectionDataSourceProxy

While working on a server side application we ran into a problem where every API invocation was taking a database connection out of the connection pool. This was due to our services being "transactional" through use of  Spring's @Transactional annotation.The connection was being held till the transactional method completed execution.

This is the default behaviour and fine for most of the cases. However, it was problematic for us for a couple of reasons

a) Most of the time Hibernate's second level cache were sufficient and there was actually no need to fetch the data from the database.
b) We were invoking some other micro-services in our ecosystem at the start of the flow and did not require database connection until those micro-services returned.

We explored for solutions and chanced upon Spring's LazyConnectionDataSourceProxy. It has the property that it defers fetching of actual connection from the pool until it is absolutely required. This meant that we could continue to use Spring's Transaction demarcation on our services and not pay a performance penalty unless there was a cache miss or we got to a point inside the transactional method where data had to be loaded from the database.

Do check it out as it can significantly improve the scalability of database connection pools.