I have the following spring data source setup:

 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

Notice that auto-commit is false.
Since, I need to use both jooq and JPA and also have multiple schemas in my db, I have configured the following 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);
        }
    }

I have the @EnableTransactionManagement on the spring boot config. With this setup in place, the connection is not committing after transaction is over.

@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);
    }

This does not commit. So, I tried adding the following code to my SchemaSettingDataSourceConnectionProvider class

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

However, now the issue is that even when a transaction should get rolled back, for e.g due to a runtime exception, it still commits all the time.

Is there some configuration I am missing

UPDATE Following the answer below, I provided a DataSourceTransactionManager bean and it worked for JOOQ.

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

However, now my regular JPA calls are all failing with

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)

So, I provided a JpaTransactionManager bean. Now this causes JOOQ Auto Configuration to throw multiple DataSourceTransactionManager beans present exception. After much trial and error, the one that worked for me was this:

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;
    }

Notice that I am using a JpaTransactionManager but setting the datasource to be TransactionAwareDataSourceProxy. Further testing required, but looks like both JPA and JOOQ transactions are working now.

CodePudding user response：

One thing to watch out for is to make sure you're using the correct @Transactional annotation. In my project there were two: one from a Jakarta package and one from a Spring package - make sure you're using the Spring annotation.

I don't know if your Spring config is correct. We use Java config so it's hard to compare.

One obvious difference is that we have an explicit TransactionManager defined, is that something you maybe need to do?