Spring transaction boundary and DB connection holding

Question

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

Answer 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?