Spring transaction boundary and DB connection hold

问题:

I am using spring boot and hibernate over jpa with tomcat connection pooling. Can you please help me understanding how spring uses DB connections during transactions. For example consider following scenario:

1. We have DB connection pool of 2 connections.
2. Spring starts a transaction i.e. call method decorated with @Transactional annotation.
3. This method do a DB update
4. The calls an external service
5. As response is received from the external service, it updates DB and return.
6. Spring commits the transaction

Assuming the external service(step 4) takes 1 minute to complete, how many DB connections will be available in the DB pool?. Assuming, spring keeps hold of DB connection until the transaction completes, there will be only 1 DB connection available for any request received during this time and if we received more than 1 requests, they will have to wait for DB connection.

Please confirm my understanding and if it is correct, suggest how I can handle this situation in a high transaction volume system.

Thanks

回答1:

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

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?

回答2:

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