Improve the jOOQ transaction experience

When using Spring Boot with jOOQ, a common source of confusion is how to use transactions. There are mainly three options:

• Ignore jOOQ's transaction APIs, and do everything Spring style (using @Transactional annotations or programmatically, using a TransactionManager
• Ignore Spring's transaction APIs and use jOOQ's APIs only
• Mix the two APIs

The first case doesn't lead to any integration problems, but the second and third ones do. For example, the org.springframework.boot.autoconfigure.jooq.SpringTransactionProvider does not work with jOOQ's asynchronous transactions as can be seen in this issue here: https://github.com/jOOQ/jOOQ/issues/10850 (helpful MCVE here: https://github.com/anushreegupta44/jOOQ-mcve). The workaround is to manually remove the SpringTransactionProvider again from the jOOQ Configuration, but users can reasonably expect this to work out of the box, especially when there are no Spring transactions involved.

The SpringTransactionProvider was originally implemented as a bridge to allow for mixing Spring transactions (e.g. based on @Transactional annotations), and then in some methods, use jOOQ's transaction APIs, which delegate to Spring, but it's probably not implemented correctly for all cases, nor am I sure whether it should always be added to Spring Boot's jOOQ auto-configuration, because when people want to use jOOQ's transaction APIs, it's not necessary.

I'm not sure what the best approach here is to improve the out-of-the-box integration experience for all three of the above cases... My intutition tells me that the SpringTransactionProvider should get an instance of the org.jooq.impl.DefaultTransactionProvider and delegate to it for all three methods (begin, commit, rollback) when TransactionSynchronizationManager.isActualTransactionActive() is false, but that may not be sufficient for asynchronous transactions.

Thoughts?

Start with SpringTransactionProvider and its begin, commit, and rollback methods, then read issue #10850 and the linked jOOQ MCVE. Compare the three transaction usage cases and asynchronous behavior before deciding what integration behavior should be supported. Done requires an agreed approach and validation for the affected cases.