Spring Boot+JDBI+HikariCP+PostgreSQL连接池未释放问题求助

Hey there, let's dig into this connection issue you're facing—it's a tricky one, but we can break it down step by step.

1. Core Problem Analysis #

The root cause here is that a single request is consuming multiple database connections instead of reusing one. Looking at your code and setup:

• You’ve configured all @Service classes to default to PROPAGATION_REQUIRED transactions, but your custom JdbiHandleManager isn’t synced with Spring’s transaction lifecycle.
• When PersonService.findFull() calls postalAddressService.findByPersonId() and documentsService.findByPersonId(), each of these @Service methods triggers Spring’s transaction interceptor. However, your withHandle method can’t detect an existing Jdbi Handle tied to the current Spring transaction, so it fetches a new connection every time.
• Even with TransactionAwareDataSourceProxy, without proper integration between Jdbi and Spring transactions, Jdbi can’t reuse connections within the same transaction, leading to multiple connections per request.

2. Fix: Use Jdbi's Official Spring Integration #

Rolling your own ThreadLocal-based Handle management is error-prone. Jdbi has an official Spring plugin that automatically syncs Handles with Spring transactions—let’s switch to that:

Step 1: Add the Dependency #

First, add the Jdbi Spring plugin to your build (match the version to your existing Jdbi setup):

// Gradle Kotlin DSL
implementation("org.jdbi:jdbi3-spring5:3.38.1")

Step 2: Simplify Jdbi Configuration #

Delete your custom JdbiHandleManager and update JdbiConfig to use the Spring transaction plugin:

@Configuration
open class JdbiConfig {
    @Bean
    open fun jdbi(dataSource: DataSource): Jdbi {
        return Jdbi.create(dataSource)
            .installPlugins()
            .installPlugin(SpringTransactionPlugin()) // Critical: Bind Jdbi to Spring transactions
    }

    // Optional: Add JdbiTemplate for easier usage
    @Bean
    open fun jdbiTemplate(jdbi: Jdbi): JdbiTemplate {
        return JdbiTemplate(jdbi)
    }
}

Step 3: Update AbstractPostgresRepository #

You no longer need manual Handle management—let Jdbi handle it automatically:

abstract class AbstractPostgresRepository {
    @Autowired
    private lateinit var jdbi: Jdbi

    protected fun <R, X : Exception> withHandle(callback: (handle: Handle) -> R): R {
        return jdbi.withHandle(callback)
    }
}

3. Optimize Transaction Configuration (Optional but Recommended) #

Since findFull() is a read-only operation, explicitly mark it as such to reduce database overhead and avoid unnecessary resource locking:

@Service
class PersonService(...) : EntityService<Person>(repository) {
    @Transactional(readOnly = true, propagation = Propagation.SUPPORTS)
    fun findFull(personId: String): PersonDto? {
        // Your existing code here
    }
}

Or adjust your global transaction config to default read-only for query methods:

override fun findTransactionAttribute(clazz: Class<*>): TransactionAttribute? {
    return if (clazz.getAnnotation(Service::class.java) != null && clazz.getAnnotation(Transactional::class.java) == null) {
        DefaultTransactionAttribute(TransactionAttribute.PROPAGATION_REQUIRED).apply {
            isReadOnly = true // Set read-only by default for service methods
        }
    } else super.findTransactionAttribute(clazz)
}

4. Verify the Fix #

After making these changes:

• A single /api/person/{id} request should only consume 1 database connection.
• 5 concurrent requests will use exactly 5 connections (matching your maximumPoolSize=5), no more waiting or timeouts.
• HikariCP’s active connection count will match PostgreSQL’s stats, even after any potential exceptions.

Why the Original Setup Caused Connection Leaks #

When requests timed out, Spring rolled back transactions, but your custom JdbiHandleManager wasn’t synced to this lifecycle—so Jdbi Handles weren’t properly closed. HikariCP thought connections were still active, while PostgreSQL had already marked them as idle. The official Spring plugin binds Handle lifecycle to Spring transactions, ensuring Handles are closed when transactions end.

内容的提问来源于stack exchange，提问作者m4nu56