Spring transaction boundary and DB connection holding

You can specify initial size and maximum size of connection pool as per your requirement(depends on the performance of your application).

For example,

<bean id="springDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" >
   <property name="url" value="jdbc:oracle:thin:@localhost:1521:SPRING_TEST" />
   <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
   <property name="username" value="root" />
   <property name="password" value="root" />
   <property name="removeAbandoned" value="true"/>
   <property name="initialSize" value="20" />
   <property name="maxActive" value="30" />
</bean>

this will create 20 database connection as initialSize is 20 and goes up to 30 Database connection if required as maxActive is 30. you can customize your database connection pool by using different properties provided by Apache DBCP library. Above example is creating connection pool with Oracle 11g database and i am using oracle.jdbc.driver.OracleDriver comes along with ojdbc6.jar or ojdbc6_g.jar


First your understanding is correct. See the spring documentation about declarative transaction management.

enter image description here

I guess you do the external service call within the transaction, because you want the database changes to be rollbacked in case of an exception. Or in other words you want the db updates to reflect the state of the external service call.

If so you can't move it out the transaction boundary. In this case you should either increase your connection pool size or maybe you can delegate long running transactions to a dedicated server node that handles them. This would keep e.g. a "main" server node that handles user requests away from long running transactions.

And you should think about the data consistency. Is it really necessary that the db update must be synchronized with the external service call? Can the external service call be moved out of the transaction boundary?