我有以下的spring数据源设置：

datasource:
    name: postgres-datasource
    url: ${POSTGRES_URL:jdbc:postgresql://localhost:5432/mydb}?reWriteBatchedInserts=true&prepareThreshold=0
    username: ${POSTGRES_USER:mydb}
    password: ${POSTGRES_PASS:12345}
    driver-class: org.postgresql.Driver
    hikari:
      minimumIdle: 2
      maximum-pool-size: 30
      max-lifetime: 500000
      idleTimeout: 120000
      auto-commit: false
      data-source-properties:
        cachePrepStmts: true
        useServerPrepStmts: true
        prepStmtCacheSize: 500
  jpa:
    database-platform: org.hibernate.dialect.PostgreSQLDialect
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQLDialect
#        generate_statistics: true
        order_inserts: true
        order_updates: true
        jdbc:
          lob:
            non_contextual_creation: true
          batch_size: 50

请注意，自动提交是false。
由于我需要同时使用jooq和JPA，并且在我的数据库中有多个模式，所以我配置了以下DataSourceConnectionProvider

public class SchemaSettingDataSourceConnectionProvider extends DataSourceConnectionProvider {

    public SchemaSettingDataSourceConnectionProvider(TransactionAwareDataSourceProxy dataSource) {
        super(dataSource);
    }

    public Connection acquire() {
        try {
            String tenant = TenantContext.getTenantId();
            log.debug("Setting schema to {}", tenant);
            Connection connection = dataSource().getConnection();
            Statement statement = connection.createStatement();
            statement.executeUpdate("SET SCHEMA '" + tenant + "'");
            statement.close();
            return connection;
        } catch (SQLException var2) {
            throw new DataAccessException("Error getting connection from data source " + dataSource(), var2);
        }
    }

我在spring boot config上有@EnableTransactionManagement。有了这个设置，连接在事务结束后就不提交了。

@Transactional(propagation = Propagation.REQUIRES_NEW)
    public FlowRecord insert(FlowName name, String createdBy) {
        return dslContext.insertInto(FLOW, FLOW.NAME, FLOW.STATUS)
                .values(name.name(), FlowStatus.CREATED.name())
                .returning(FLOW.ID)
                .fetch()
                .get(0);
    }

所以，我尝试将以下代码添加到我的SchemaSettingDataSourceConnectionProvider类中

@Override
    public void release(Connection connection) {
        connection.commit();
        super.release(connection);
    }

然而，现在的问题是，即使当事务应该回滚时，例如由于运行时异常，它仍然一直提交。
我是否遗漏了某些配置

UPDATE按照下面的答案，我提供了一个DataSourceTransactionManager bean，它适用于JOOQ。

public DataSourceTransactionManager jooqTransactionManager(DataSource dataSource) {
        // DSTM is a PlatformTransactionManager
        return new DataSourceTransactionManager(dataSource);
    }

但是，现在我的常规JPA调用都失败了，

Caused by: javax.persistence.TransactionRequiredException: Executing an update/delete query
    at org.hibernate.internal.AbstractSharedSessionContract.checkTransactionNeededForUpdateOperation(AbstractSharedSessionContract.java:445)
    at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1692)

因此，我提供了一个JpaTransactionManager bean。现在，这会导致JOOQ自动配置抛出多个DataSourceTransactionManager bean存在异常。经过多次试验和错误，对我有效的是：

private final TransactionAwareDataSourceProxy dataSource;

    public DslConfig(DataSource dataSource) {
        // A transaction aware datasource is needed, otherwise the spring @Transactional is ignored and updates do not work.
        this.dataSource = new TransactionAwareDataSourceProxy(dataSource);
    }

    @Bean("transactionManager")
    public PlatformTransactionManager transactionManager() {
        // Needed for jpa transactions to work
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setDataSource(dataSource);
        return transactionManager;
    }

请注意，我使用的是JpaTransactionManager，但将datasource设置为TransactionAwareDataSourceProxy。需要进一步测试，但看起来JPA和JOOQ事务现在都在工作。