All geek questions in one place

Spring transaction border and DB connection join

I am using spring boot and sleep via jpa with tomcat connection pool. Could you please help me understand how spring uses database connections during transactions. For example, consider the following scenario:

  • We have a DB connection pool of 2 connections.
  • Spring starts the transaction, i.e. the call method, decorated with the @Transactional annotation.
  • This method updates the database.
  • External service call
  • When a response is received from an external service, it updates the DB and returns.
  • Spring commits a transaction

Assuming the external service (step 4) takes 1 minute, how many DB connections will be available in the database pool ?. Assuming spring maintains communication with the database until the transaction is completed, only 1 DB connection will be available for any request received during this time, and if we receive more than 1 request, they will have to wait for the connection to the database.

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

thanks

+10
source share
2 answers

At first, your understanding is correct. See spring declarative transaction management documentation.

enter image description here

I suppose you are making an external service call within a transaction because you want the database changes to be rolled back in case of an exception. Or, in other words, you want the database updates to reflect the state of the external service call.

If so, you cannot move it outside of the transaction. In this case, you should either increase the size of the connection pool, or perhaps you can delegate lengthy transactions to a dedicated server node that processes them. This would save, for example, the "main" server node that processes user requests from lengthy transactions.

And you should think about data consistency. Is it really necessary for the database update to be synchronized with an external service call? Can an external service call be moved outside the transaction?

+8
source

You can specify the initial size and maximum size of the connection pool according to your requirement (depending on the performance of your application).

For instance,

<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 connections since initialSize is 20 and reaches 30 database connections if required, since maxActive is 30. You can configure the database connection pool using various properties provided by the Apache DBCP library. The above example creates an Oracle 11g database connection pool, and I use oracle.jdbc.driver.OracleDriver comes with ojdbc6.jar or ojdbc6_g.jar

+3
source

Source: https://habr.com/ru/post/1206227/

More articles:


All Articles