Short example

@Service
class ServiceA(private val serviceB: ServiceB) {

  fun doA() {
    serviceB.doB(..) // fast query
    Thread.sleep(5000) // slow operation (e.g. external request call)
  } 
}

@Service
class ServiceB {

  @Transactional
  fun doB(..) {
    // some fast query to db
  }
}

Expected

  1. start doB
  2. acquire connection
  3. start transaction
  4. execute logic doB
  5. commit or rollback transaction
  6. closed connection (return to pool)
  7. end doB

Actual

connection not closed after commit transaction As result "Connection is not available, request timed out after"

Example with test

Comment From: sbrannen

The connections are closed and returned to the pool.

The issue is that you have limited the pool to only two connections using maximum-pool-size: 2, and you simulate 10 concurrent users using Executors.newFixedThreadPool(10) in your test.

If you increase the pool size (e.g., 5 or higher), the problem goes away. Similarly, if you reduce the number of concurrent users in the test (e.g., 4 or fewer), the problem also goes away.

If you want to see what Spring is doing with the transactions, you can turn on trace logging as below.

logging:
  level:
    org.springframework.transaction: TRACE

In light of the above, I am closing this issue.

Comment From: DVMaslov

@sbrannen

The connections are closed and returned to the pool.

It is true but it happens not immediately after end doB(..). doB is very fast method (<10 ms) and when it complete connection must return to pool. When execute Thead.sleep(..) connection already must be released.

The connection should only be acquired during the execution of the doB method. Therefore I expect that even 100 simultaneous users should not lead to a long wait for a connection from the pool

Comment From: DVMaslov

@sbrannen

So, is it the expected behavior that the connection is not released immediately after the doB(..) completes?

Comment From: sbrannen

I would actually expect the connection to be closed (i.e., returned to the pool) immediately after the transaction has completed (via commit or rollback).

Are you experiencing this behavior in a production deployment?

Or are you only seeing this behavior when running tests?

If you only see this during tests, have you also encountered this behavior when the test and the server are running on physically different machines?

Comment From: DVMaslov

I found this behavior in my pet project. Previously I run application in docker and load it using Jmeter. Added docker-compose, stopwatch, jmeter config to the project.

Comment From: DVMaslov

I found the reason for this behavior: spring.jpa.open-in-view: true (default) if set this property to false connection closed immediately after commit/rolback before end doB(..) method.

Whether to change the default value open-in-view?