Add support for read only transactions, and transaction isolations

[Centaur]

I have read about advantages of marking transactions read-only and we are using Postgresql which support readonly transactions.
But I did not find out how to create a read-only transaction in either the doc or the source code.
So is it possible in current jooq version? Is there any plan to implement it if not ?

[lukaseder]

Thank you very much for your suggestion.

It isn't possible via the jOOQ API, but it is via the JDBC API. For this, you could implement your own TransactionProvider (e.g. based on the current DefaultTransactionProvider implementation), and set your JDBC Connection to readonly via Connection.setReadOnly(true).

We'll think about providing API support for this in a future jOOQ version.

[lukaseder]

When implementing this, we'll clearly need to think also about other transaction properties, such as:

• isolation
• propagation
• timeout
• "rollbackFor"

These will be implemented in separate feature requests, when needed, but the API introduced for this read only flag should already take into account the possibility of additional properties.

Inspiration can be taken from org.springframework.transaction.annotation.Transactional or corresponding Java EE types

[dmiorandi]

+1.

I think the challenge probably is to pass the @transactional parameters from spring method into TransactionProvider implementation (inside TransactionContext ?) on jooq side.

Due to jooq context doesn't know nothing about spring, to be simple probably something like a .derive()
from current jooq context method that accept the transaction behavior (create new one or partecipate or ...) should be enough,
demanding to developer to detect from spring context the transaction configuration of the current method.
This method should be used in inner @transactional methods.....

Do you think this is reasonable?

[lukaseder]

Hmm, I don't think that jOOQ should engage in AOP nor make any assumptions about how Spring (or Java EE) do annotation-based context evaluations. The idea here is to enrich the jOOQ API with additional flags that can be passed into the TransactionProvider, e.g. to Spring's programmatic APIs.

I think as a general rule of thumb, if you're using @Transactional, you should not be using jOOQ's transaction API. I should probably blog about that, the confusion has come up a couple of times, recently...

[dmiorandi]

I'm definitly agree with you, jooq should be spring agnostic.
Probably I din't explain my concept in a clear way.
My project is taken from reference project:
https://github.com/jOOQ/jOOQ/tree/master/jOOQ-examples/jOOQ-spring-boot-example
that implements those interfaces:

• org.jooq.Transaction
• org.jooq.TransactionProvider

Last one in particular match spring and jooq Transaction, but actually there is no possibility to manage @transactional attributes.

public class SpringTransactionProvider implements TransactionProvider {

    private static final JooqLogger log = JooqLogger.getLogger(SpringTransactionProvider.class);

    @Autowired DataSourceTransactionManager txMgr;

    @Override
    public void begin(TransactionContext ctx) {
        log.info("Begin transaction");

        // This TransactionProvider behaves like jOOQ's DefaultTransactionProvider,
        // which supports nested transactions using Savepoints
        TransactionStatus tx = txMgr.getTransaction(new DefaultTransactionDefinition(PROPAGATION_NESTED));
        ctx.transaction(new SpringTransaction(tx));
    }

    @Override
    public void commit(TransactionContext ctx) {
        log.info("commit transaction");

        txMgr.commit(((SpringTransaction) ctx.transaction()).tx);
    }

    @Override
    public void rollback(TransactionContext ctx) {
        log.info("rollback transaction");

        txMgr.rollback(((SpringTransaction) ctx.transaction()).tx);
    }
}

Actually I'm a bit confused about how to use spring transactions instead of jooq ones. If you have some suggestions to address me into this task I would really appreciate it.
Tks

[lukaseder]

For the record (if a future visitor finds this), this is also being discussed on the user group:
https://groups.google.com/forum/#!topic/jooq-user/FVFKKVPheh4

@dmiorandi: If you don't mind, I'd like to avoid duplication of discussion, so I'll continue to comment on the user group

[saibotma]

We used Kotlin Exposed for a previous project, and they offer the functionality to set the isolation level for a transaction.

transaction (Connection.TRANSACTION_SERIALIZABLE) {
    // DSL/DAO operations go here
}

After noticing that this is not possible in jOOQ at the moment I tried to come up with a small workaround solution wrapping the jOOQ transaction and setting the transactional isolation level on the connection from the DSLContext.

fun <T> DSLContext.transactionIsolation(isolationLevel: Int? = null, block: (DSLContext).() -> T): T {
    return transactionResult { config ->
        val db = DSL.using(config)
        if (isolationLevel != null) {
            connection { it.transactionIsolation = isolationLevel }
        }
        db.block()
    }
}

When checking the transaction isolation mode inside a transaction created with transactionIsolation using SHOW TRANSACTION ISOLATION LEVEL Postgres returns the correct level.
Subsequent transaction also fallback to the default level in case it is not set.

Is there anything wrong with this approach?

[lukaseder]

@saibotma Well, you should set it back to what it was before, after calling db.block()

[lukaseder]

I will have a look at this feature request in the context of jOOQ 3.18's #7106 support for procedural transactions