Spring Boot集成JPA时如何正确配置jOOQ事务管理

我们首先通过如下集成测试验证jOOQ与Spring事务的集成正确性：

@ExtendWith(SpringExtension::class)
@SpringBootTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@AutoConfigureTestEntityManager
@Transactional(isolation = READ_UNCOMMITTED)
class JooqConfigIT {
    companion object {
        const val testProjectId = 2021520
    }

    @Autowired
    lateinit var ctx: DSLContext

    @Test
    fun `dialect is set to postgres`() {
        assertThat(ctx.configuration().dialect()).isEqualTo(SQLDialect.POSTGRES)
    }

    @RepeatedTest(2, name = LONG_DISPLAY_NAME)
    fun `A - if jOOQ does not respect Spring transactions, either A or B should fail`() {
        ctx.insertInto(PROJECT)
            .columns(PROJECT.ID, PROJECT.NAME)
            .values(testProjectId, "Hakuna")
            .execute()
    }

    @RepeatedTest(2, name = LONG_DISPLAY_NAME)
    fun `B- if Spring does not respect jOOQ transactions, either A or B should fail`() {
        ctx.transaction { config ->
            DSL.using(config)
                .insertInto(PROJECT)
                .columns(PROJECT.ID, PROJECT.NAME)
                .values(testProjectId, "Matata")
                .execute()
        }
    }
}

测试逻辑非常简单：

• 每个@Test方法启动时会开启事务，测试结束时自动回滚；
• 若jOOQ集成正确，测试结束后testProjectId不会持久化到数据库，不会抛出任何异常；
• 若配置有误，A或B测试会将数据写入数据库，重复执行时会因主键ID重复导致至少一次用例失败；
• 对A、B测试各重复执行2次的原因是：若仅单向集成正确（例如Spring可感知jOOQ事务但jOOQ无法感知Spring事务），重复执行可避免测试全部执行完才提交事务导致漏检问题，只要有一次写入持久化，后续重复插入就会失败。

常见配置与自动配置逻辑 #

明确验证目标后，我们需要找到正确的配置方式。网络上多数教程给出的配置方案如下：

@Configuration
class JooqConfig(
    private val dataSource: DataSource,
    private val transactionManager: DataSourceTransactionManager,
) {

    /** 让jOOQ感知Spring事务（例如@Transactional注解声明的事务）*/
    @Bean
    fun dataSourceConnectionProvider() = DataSourceConnectionProvider(TransactionAwareDataSourceProxy(dataSource))

    /** 让Spring感知jOOQ事务（即`ctx.transaction{config -> ...}`声明的事务）*/
    @Bean
    fun springTransactionProvider(): TransactionProvider = SpringTransactionProvider(transactionManager)

    private class SpringTransactionProvider(private val transactionManager: DataSourceTransactionManager) : TransactionProvider {
        companion object {
            private val log: JooqLogger = JooqLogger.getLogger(SpringTransactionProvider::class.java)
        }

        override fun begin(ctx: TransactionContext) {
            log.info("begin transaction")
            val transactionDefinition = DefaultTransactionDefinition(PROPAGATION_NESTED)
            val transactionStatus = transactionManager.getTransaction(transactionDefinition)
            ctx.transaction(SpringTransaction(transactionStatus))
        }

        override fun commit(ctx: TransactionContext) {
            log.info("commit transaction");
            transactionManager.commit(ctx.transactionStatus)
        }

        override fun rollback(ctx: TransactionContext) {
            log.info("rollback transaction");
            transactionManager.rollback(ctx.transactionStatus)
        }

        private class SpringTransaction(val status: TransactionStatus) : Transaction

        private val TransactionContext.transactionStatus
            get() = when (val tx = this.transaction()) {
                is SpringTransaction -> tx.status
                else -> throw IllegalStateException("Expected SpringTransaction but was $tx")
            }
    }
}

但经查阅Spring Boot源码可知，上述配置已经由org.springframework.boot.autoconfigure.jooq.JooqAutoConfiguration自动完成，无需手动编写：

@Configuration(proxyBeanMethods = false)
@ConditionalOnClass(DSLContext.class)
@ConditionalOnBean(DataSource.class)
@AutoConfigureAfter({ DataSourceAutoConfiguration.class, TransactionAutoConfiguration.class })
public class JooqAutoConfiguration {

    @Bean
    @ConditionalOnMissingBean(ConnectionProvider.class)
    public DataSourceConnectionProvider dataSourceConnectionProvider(DataSource dataSource) {
        return new DataSourceConnectionProvider(new TransactionAwareDataSourceProxy(dataSource));
    }

    @Bean
    @ConditionalOnBean(PlatformTransactionManager.class)
    public SpringTransactionProvider transactionProvider(PlatformTransactionManager txManager) {
        return new SpringTransactionProvider(txManager);
    }

    //... 其余自动配置省略

}

JPA集成场景的报错问题 #

上述手动配置仅在纯jOOQ场景下不需要，但当应用同时集成JPA（例如计划从JPA逐步迁移到jOOQ的场景）时，测试B会执行失败，报错原因并非Spring无法感知事务，而是抛出如下嵌套事务不支持异常：

JpaDialect does not support savepoints - check your JPA provider's capabilities
org.springframework.transaction.NestedTransactionNotSupportedException: JpaDialect does not support savepoints - check your JPA provider's capabilities
org.springframework.transaction.NestedTransactionNotSupportedException: JpaDialect does not support savepoints - check your JPA provider's capabilities
    at org.springframework.orm.jpa.JpaTransactionManager$JpaTransactionObject.getSavepointManager(JpaTransactionManager.java:762)
    at org.springframework.orm.jpa.JpaTransactionManager$JpaTransactionObject.createSavepoint(JpaTransactionManager.java:741)
    at org.springframework.transaction.support.AbstractTransactionStatus.createAndHoldSavepoint(AbstractTransactionStatus.java:140)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.handleExistingTransaction(AbstractPlatformTransactionManager.java:457)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:352)
    at org.springframework.boot.autoconfigure.jooq.SpringTransactionProvider.begin(SpringTransactionProvider.java:48)
    at org.jooq.impl.DefaultDSLContext.lambda$transactionResult0$0(DefaultDSLContext.java:537)
    at org.jooq.impl.Tools$35$1.block(Tools.java:5246)
    at java.base/java.util.concurrent.ForkJoinPool.managedBlock(ForkJoinPool.java:3128)
    at org.jooq.impl.Tools$35.get(Tools.java:5243)
    at org.jooq.impl.DefaultDSLContext.transactionResult0(DefaultDSLContext.java:595)
    at org.jooq.impl.DefaultDSLContext.transactionResult(DefaultDSLContext.java:512)
    at org.jooq.impl.DefaultDSLContext.transaction(DefaultDSLContext.java:612)
    at my.app.config.JooqConfigIT.B- if Spring does not respect jOOQ transactions, either A or B should fail(JooqConfigIT.kt:36)
    at ...

从该报错可以得到三个结论：

• JPA存在一定局限性，这也是迁移jOOQ的原因之一；
• 即使手动编写自定义springTransactionProvider Bean，Spring也不会加载，仍会使用自动配置的内置实现，堆栈信息可验证这一点；
• 实际业务场景中我们并不需要嵌套事务：Repository层实现会统一由添加了@Transactional注解的Service层托管事务，事务管理逻辑全部由Spring统一处理。

临时解决方案可以是将类上的@Transactional注解移到A测试方法上，B测试方法通过抛出异常实现手动回滚，但这并非规范的持久化配置。曾尝试自定义springTransactionProvider，将事务传播行为改为*PROPAGATION_REQUIRED*，避免jOOQ创建嵌套事务解决问题，但自定义Bean不生效导致该方案无法落地。

正确配置方案 #

自定义TransactionProvider不生效的核心原因是Spring Boot默认的jOOQ自动配置中，SpringTransactionProvider Bean没有添加@ConditionalOnMissingBean注解，同类型自定义Bean不会自动替换默认实现，只需要在自定义的TransactionProvider上添加@Primary注解，让Spring优先注入自定义实现即可。

自定义实现将事务传播行为设置为PROPAGATION_REQUIRED：当调用ctx.transaction { ... }时，如果当前上下文已经存在Spring托管的事务（比如Service层@Transactional注解开启的事务，或者测试类上的事务注解开启的事务），就直接加入当前事务，不会尝试创建需要savepoint支持的嵌套事务；如果没有现有事务，才会新建独立事务，完全符合业务场景中统一由Spring托管事务的需求。
完整配置代码如下：

@Configuration
class JooqConfig(
    private val transactionManager: PlatformTransactionManager
) {
    @Bean
    @Primary
    fun jooqTransactionProvider(): TransactionProvider {
        return object : TransactionProvider {
            private val log = JooqLogger.getLogger(this::class.java)

            override fun begin(ctx: TransactionContext) {
                log.info("begin jOOQ transaction")
                val txDef = DefaultTransactionDefinition(PROPAGATION_REQUIRED)
                val txStatus = transactionManager.getTransaction(txDef)
                ctx.transaction(SpringTransaction(txStatus))
            }

            override fun commit(ctx: TransactionContext) {
                log.info("commit jOOQ transaction")
                transactionManager.commit(ctx.getSpringTxStatus())
            }

            override fun rollback(ctx: TransactionContext) {
                log.info("rollback jOOQ transaction")
                transactionManager.rollback(ctx.getSpringTxStatus())
            }

            private fun TransactionContext.getSpringTxStatus(): TransactionStatus {
                val tx = this.transaction()
                check(tx is SpringTransaction) { "Expected SpringTransaction but got $tx" }
                return tx.status
            }

            private inner class SpringTransaction(val status: TransactionStatus) : Transaction
        }
    }
}

配置完成后重新运行集成测试，A、B两组重复测试都可以正常通过，不会出现主键冲突或嵌套事务不支持的报错。生产环境中jOOQ和JPA的数据库操作会被同一个Spring事务托管，任意环节抛出异常都会触发整体回滚，符合预期的事务语义。

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