databasebackendktorkotlin-exposed

ConnectionTimeout for ktor/exposed backend concurrent requests

I have a ktor backend with JetBrains exposed framework for db access. I am running into a weird issue where my connection pool (using hikari for jdbc access) immediately fills up and times out whenever I get just a few (~10) concurrent requests. I see this exception:

SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 10000ms (total=7, active=7, idle=0, waiting=18)

A single request on its own just takes about 300ms so the db query is not the problem. Interestingly enough all but the first request (which succeeds) take 10+ seconds (the configured connection timeout).

This is my hikari config:

    driverClassName = "org.postgresql.Driver"
    maximumPoolSize = 7
    minimumIdle = 1
    connectionTimeout = 10_000
    isAutoCommit = false
    transactionIsolation = "TRANSACTION_REPEATABLE_READ"
    addDataSourceProperty("socketTimeout", "30")

My server is running on a single instance using ktor/netty. For transactions I use:

  newSuspendedTransaction(Dispatchers.IO) {
// ... db queries
}

once per request.

Any ideas how I can debug this or what the root cause might be?

Solution

  • Ok so I found the reason myself. Posting it so maybe somebody facing this can use it:

    I actually did not have one call of newSuspendedTransaction per request but one per (internal) service-call which lead to nested transactions. These naturally deadlocked each other when many concurrent requests came in, especially for calls with multiple nested ones.

    I resolved it by unnesting the transactions. Its noteworthy that in contrast to transaction { .. } newSuspendedTransaction always creates a new transaction.