[Java] Number of connections required when nesting transactions

Is the number of transaction nests = the number of required connections? ?? ??

When estimating the pool size of the connection pool for multi-threaded batch processing, transaction nesting occurs, so ** naturally nesting connections are also required **, so thread count x transaction nesting count I set the pool size in.

Later, I felt vague anxiety about ** of course ** in myself, "Is that really the case (need a nesting part?)", And even when I googled, I couldn't reach the answer of ** itself. ** So I decided to build a sample code and check it.

Conclusion

Suddenly there is no body or lid, Transaction nesting = required connections was.

So, if you just want to know the result, you can skip the following.

Verification environment

• Oracle JDK 8 u161
• spring-boot 1.4.3
• Tomcat JDBC Connection Pool 8.5.6

Sample code (excerpt)

application.properties

# ConnectionPool
spring.datasource.tomcat.maxActive=1
spring.datasource.tomcat.maxIdle=1
spring.datasource.tomcat.minIdle=0
spring.datasource.tomcat.initialSize=0

App.java

@SpringBootApplication
public class App {

    public static void main(String[] args) {
        ApplicationContext context = SpringApplication.run(App.class, args);
        Main bean = context.getBean(Main.class);
        bean.doNestedTransaction();
    }

}

Main.java

@Component
public class Main {

    @Autowired
    private MainTarnsaction mainTransaction;

    public void doNestedTransaction() {
        mainTransaction.beginTransaction();
    }

}

MainTarnsaction.java

@Component
public class MainTarnsaction {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private NestedTransaction nestedTransaction;

    @Transactional
    public void beginTransaction() {
        jdbcTemplate.execute("update Hoge set val = 'aaa' where id = '1'");
        nestedTransaction.beginTransaction();
    }

}

NestedTransaction.java

@Component
public class NestedTarnsaction {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void beginTransaction() {
        jdbcTemplate.execute("update Hoge set val = 'bbb' where id = '2'");
    }

}

Execution result

(1) When the pool size is set to 1 ( spring.datasource.tomcat.maxActive = 1) and executed

org.springframework.transaction.CannnotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is org.apache.tomcat.jdbc.pool.PoolExhaustedException: [main] Timeout: Pool empty. Unable to fetch a connection in 30 seconds. none available[size:1; busy:1; idel:0; lastwait:30000].
...
at xxx.NestedTransaction$$EnhancerBySpringCGLib$$xxx.beginTransaction(<generated>)
...
at xxx.MainTransaction$$EnhancerBySpringCGLib$$xxx.beginTransaction(<generated>)
at xxx.Main.doNestedTransaction(Main.java:xx)
...

As mentioned above, the connection for the nested transaction could not be allocated from the pool and an exception occurred.

(2) When the pool size is set to 2 (spring.datasource.tomcat.maxActive = 2) and executed

It ended normally (as expected).

in conclusion

This made me feel refreshed. I hope it will be helpful to those who have encountered the same question.