I've got a Spring Boot application that consists in an API. It exposes an endpoint /api/persons/:id.
Under the hood Spring Boot comes with Hibernate and HikariCP. My database is PostgreSQL 10.
The app is built in Kotlin.
I've identified a problem when simultaneous requests are received by the API, it seems that the app require 2 active connections to perform the operation findpersonById() called by the endpoint.
The pool is configured this way:
spring.datasource.hikari.maximumPoolSize=5
When I send 5 simultaneous requests to /api/persons/:id 5 connections are taken from the pool to execute the request behind and 5 are in pending state.
The pool finally throw an exception because the 5 pending connections have been waiting for the connectionTimeout period and the request fail as well.
The problem I'm facing is that after that HikariCP still says that there are 5 active connections on the pool. And if I look at the PostgreSQL statistics all connections are idle.
And FYI if I send only 4 requests at the same time everything goes as excepted, it begins with 5 active connection and 3 pendings and all requests returns the excepted results.
I've tried to change the Pool to Tomcat JDBC but with the exact same result.
I don't understand why 2 connections are needed in the first place.
I past some of the code here if anyone has an idea of what I am doing wrong..
@RestController
@RequestMapping("/api/person")
class PersonResource(private val personService: PersonService,
private val personUpdateService: PersonUpdateService,
private val identityManagementService: IdentityWithManagementService) : PersonApi {
@GetMapping("/{id}")
override fun findPersonById(@PathVariable id: String): PersonDto? {
return personService.findFull(id)
}
}
The personService:
@Service
class PersonService(private val documentsService: DocumentService,
private val postalAddressService: PostalAddressService) : EntityService<Person>(repository) {
fun findFull(personId: String): PersonDto? {
return find(personId)?.let { person ->
PersonDto(
person,
postalAddressService.findByPersonId(personId).map { it.toDto() },
documentsService.findByPersonId(personId).map { it.toDto() }
)
}
}
}
The PersonPostgresRepository:
@Repository
class PersonPostgresRepository : AbstractPostgresRepository(), PersonEntityRepository {
override fun find(id: String): Person? {
return withHandle<Person?, Exception> {
it.createQuery(
"select * " +
"from identiti_person " +
"where id = :id")
.bind("id", id)
.map(PersonRowMapper())
.firstOrNull()
}
}
}
The AbstractPostgresRepository:
abstract class AbstractPostgresRepository {
@Autowired
private lateinit var handleManager: JdbiHandleManager
@Autowired
private lateinit var jdbi: Jdbi
protected fun <R, X : Exception> withHandle(callback: (handle: Handle) -> R): R {
val handle = handleManager.handle
return if (handle.isPresent) {
callback.invoke(handle.get())
} else {
jdbi.withHandle(HandleCallback<R, X> {
callback.invoke(it)
})
}
}
}
And the JdbiHandleManager in case you ask:
@Component
@Scope("singleton")
class JdbiHandleManager(private val jdbi: Jdbi) {
private val currentHandle = ThreadLocal<Handle>()
val handle: Optional<Handle>
get() = Optional.ofNullable(currentHandle.get())
internal fun openHandle(): Handle {
val handle = jdbi.open()
currentHandle.set(handle)
return handle
}
internal fun closeHandle() {
val handle = currentHandle.get()
currentHandle.remove()
handle?.close()
}
}
And the JdbiConfig initializes Jdbi:
@Configuration
open class JdbiConfig {
@Bean
open fun jdbi(dataSource: DataSource): Jdbi {
// JDBI wants to control the Connection wrap the datasource in a proxy
// That is aware of the Spring managed transaction
val dataSourceProxy = TransactionAwareDataSourceProxy(dataSource)
val jdbi = Jdbi.create(dataSourceProxy)
jdbi.installPlugins()
return jdbi
}
}
All my @Service are transactionnal thank's to this:
@Bean
@Role(BeanDefinition.ROLE_INFRASTRUCTURE)
override fun transactionAttributeSource(): TransactionAttributeSource {
/*
Defines an annotation transaction source (the default) which consider that @Service components
are transactional by default (making the use of @Transactional optional on those classes).
Note that the class has to be processed by a TransactionInterceptor for that source to be applied,
this is the responsibility of the auto proxy creator below.
*/
return object : AnnotationTransactionAttributeSource() {
override fun findTransactionAttribute(clazz: Class<*>): TransactionAttribute? {
return if (clazz.getAnnotation(Service::class.java) != null && clazz.getAnnotation(Transactional::class.java) == null) {
DefaultTransactionAttribute(TransactionAttribute.PROPAGATION_REQUIRED)
} else super.findTransactionAttribute(clazz)
}
}
}
I did not run your code, but am quite confident that the underlying problem is the following:
Your JdbiHandleManager is redundant and leads to problems (2 connections opened). Why? Because TransactionAwareDataSourceProxy already handles the open and close of connections. When Spring encounters a method call which is "transactional" (via annotation or aspect) a connections is opened and bound to the current thread.
That means it is absolutely sufficient to just use jdbi.withHandle(...) as the next call to open a connection is returning the active transaction connection and calls to close are proxied since spring will close connections by itself.
Since you implemented your own "Manager" this happens twice, once by spring and once by you. Two threadlocal connections (one already wrapped inside a Handle)
My suggestion here is to remove you custom code and fully rely on the correct operation of TransactionAwareDataSourceProxy