Start Spring Transaction per request but reduce database connections

In my previous blog post, I explained how to make spring declarative transactions to behave truly transactional. We do not need to do anything special in a Spring MVC project. But if we use any other UI controller then we have to start a dummy transaction at the beginning of a HTTP request and end that transaction at the end of that request.

One concern in this method is, since it starts and end transactions per HTTP request, will it increase the number of database connections and disconnections. Well there is a possibility and it will reduce the overall performance of the system.

As described in Class DataSourceTransactionManager it is a good  idea to create a database connection when it is actually needed for example only when executing a SQL statement. In this way a dummy transaction will be started but it will not create a database connection. The connection will be established only when it is needed during the transaction.

Extract from the class documentation

"Consider defining a LazyConnectionDataSourceProxy for your target DataSource, pointing both this transaction manager and your DAOs to it. This will lead to optimized handling of "empty" transactions, i.e. of transactions without any JDBC statements executed. A LazyConnectionDataSourceProxy will not fetch an actual JDBC Connection from the target DataSource until a Statement gets executed, lazily applying the specified transaction settings to the target Connection."

So we will define LazyConnectionDataSourceProxy based on a usual datasource. Then transaction manager as well as our JdbcTemplate will be pointed to this LazyConnectionDataSource as below.

Now JdbcTemplate as well as the Transaction manager will grab a database connection only when it is actually needed.