jooq+spring+ PROPAGATION_REQUIRED

[Denis Miorandi]

Hi, 

      I've got a spring4+jooq application, based on jOOQ-spring-boot-example/

I would like to use PROPAGATION_REQUIRES_NEW in a method called inside another transactional (to obtain and indipendent transaction) method

marked as PROPAGATION_REQUIRES that is default.

Should it work in your example project? 

It seems to me that it doens't work cause my transactionProvider define "statically" the default behavior as PROPAGATION_REQUIRES

and when it change on @Transactional annotation is doesn't take effect. Is it possible?

This is my provider.

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
		// but use PROPAGATION_REQUIRED AS DEFAULT
		TransactionStatus tx = txMgr.getTransaction(new DefaultTransactionDefinition(PROPAGATION_REQUIRED));
		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);
	}
}

[Lukas Eder]

Hi Denis,

Thanks for your question. The jOOQ transaction API was mainly defined to work in simple, JDBC-style situations where there are not Java EE style propagations and other transaction "flags". While it is currently possible to bind the jOOQ TransactionProvider to Spring TX (or to JTA), it is not so easy to pass the propagation flag to it, out of the box.

If you're using the current transaction API in jOOQ: DSLContext.transaction( () -> { ... } ), then you cannot use the @Transactional annotations. The two APIs are mutually exclusive. jOOQ uses a programmatic API, Spring supports both programmatic and declarative, annotation-based APIs. You can pass the propagation to your Spring DataSourceTransactionManager, which is inside of the SpringTransactionProvider. There is a pending feature request that will allow for that to work out of the box: https://github.com/jOOQ/jOOQ/issues/4836

Currently, this is not yet documented, but I'm happy to help you on the way.

I hope this answers your question thus far. If not, just let me know.

Best Regards,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Denis Miorandi]

Hi Lukas,

        when you say @Transactional is not supported you mean @Transactional attributes not supported I suppose.

Actually my SpringTransactionProvider allow use of @Transactional annotations 

( creating a jooq Transaction in a transparent way) the only missing things are @Transactional parameters. 

I suspect  actually due SpringTransactionProvider  is created at spring boot time it can't intercept specific instance call parameters.

Anyway I'm looking forward to #4836.

I've added some comments here.

Denis

[Lukas Eder]

Hi Denis,

2016-06-15 13:47 GMT+01:00 Denis Miorandi <denis.m...@gmail.com>:

Hi Lukas,

        when you say @Transactional is not supported you mean @Transactional attributes not supported I suppose.

What I really meant is that the two approaches (Spring / AOP / declarative vs. jOOQ / programmatic) are not really a good match. They shouldn't be mixed, in my opinion. This is independent of jOOQ. You probably wouldn't mix Spring's annotations with Spring's programmatic APIs either.

But perhaps you've found a use-case where mixing is reasonable?

 

Actually my SpringTransactionProvider allow use of @Transactional annotations 

( creating a jooq Transaction in a transparent way) the only missing things are @Transactional parameters. 

Oh, interesting! Would you be interested in sharing this code? Would be very useful to look into how you did this.

 

I suspect  actually due SpringTransactionProvider  is created at spring boot time it can't intercept specific instance call parameters.

Anyway I'm looking forward to #4836.

I've added some comments here.

Hmm, not sure. It's a "provider". You can implement it in any way you want. It doesn't have to initialise everything when it is initialised itself...

[Denis Miorandi]

I wouldn't call it "Mix" it's more a delegation process. Spring intercept @Transactional annotation and delegate jooq to manage it, but actually I don't know how jooq 

can manage "start a new transaction" instead of "use current one" in some cases. 

Code is almost identical to the reference one into

https://github.com/jOOQ/jOOQ/tree/master/jOOQ-examples/jOOQ-spring-boot-example

The hot part is the following where is not possible do manage different propagation style cause this parameter is not in ctx.

Of course I can implement provider as needed but I don't know how to have @Transactional parameters as input here, to create transaction in right way.

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

[Lukas Eder]

Hi Denis,

I'm sorry for the delay.

2016-06-17 10:27 GMT+02:00 Denis Miorandi <denis.m...@gmail.com>:

I wouldn't call it "Mix" it's more a delegation process. Spring intercept @Transactional annotation and delegate jooq to manage it, but actually I don't know how jooq 

can manage "start a new transaction" instead of "use current one" in some cases. 

The jOOQ model puts that responsibility into the Configuration.transactionProvider(), and implicitly also into Configuration.connectionProvider(). There is always a "current" Configuration, so the transaction semantics is clear.

 

Code is almost identical to the reference one into

https://github.com/jOOQ/jOOQ/tree/master/jOOQ-examples/jOOQ-spring-boot-example

The hot part is the following where is not possible do manage different propagation style cause this parameter is not in ctx.

Of course I can implement provider as needed but I don't know how to have @Transactional parameters as input here, to create transaction in right way.

Unfortunately, I don't know it either. Would be interesting as a Stack Overflow question, I suspect. "How to get the current thread's context's @Transactional parameters via Spring APIs?"

[Denis Miorandi]

I could investigate about this...

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/FVFKKVPheh4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

[steinar....@miles.no]

Hi!

Has anything happened here? I am trying to find out how I can override the default transaction isolation level using the jooq APIs.

Based on what I have read so far this is not possible out of the box, but it would seem to be possible to do the following with a small refactoring of the SpringTransactionProvider:

try(DSLContext ctx = dbCon.usingCtx()) {
    ctx.transaction(configuration -> {
        TransactionProvider jooqTransactionProvider =  configuration.transactionProvider();
        DSLContext innerCtx;
        if(jooqTransactionProvider instanceof SpringTransactionProvider) {
            SpringTransactionProvider stp = (SpringTransactionProvider)jooqTransactionProvider;
            SpringTransactionProvider localTsp = stp.derive(configuration.connectionProvider(), TransactionDefinition.ISOLATION_SERIALIZABLE);

            Configuration derivedCfg = configuration.derive(localTsp);
            innerCtx = DSL.using(derivedCfg);
        }
        else {
            // Use default configuration and transaction definition
            innerCtx = DSL.using(configuration);
        }

        innerCtx.insertInto()....
    });

Anyone have any opinions on this approach?

Cheers,

Steinar.

[Lukas Eder]

Hi Steinar,

I'm not sure if this approach is sound. You're already starting a transaction using the jOOQ transaction API, only to then patch the jOOQ transaction and transaction provider for subsequent statements. I think that your adapted transaction provider should be configured already prior to starting the transaction.

I hope this helps,

Lukas

You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

[Steinar Dragsnes]

I didn’t think I patched the current transaction, I thought I created a new inner transaction with a different/derived config.

So if I do a config.derive before starting the transaction then I am fine?

The main problem as I can see it right now is that the TransactionDefinition is hardcoded in the SpringTransactionProvider. For most things that is fine but this is a special case where I must use a different transaction isolation. 

Cheers,

Steinar 

[Lukas Eder]

On Wed, Jan 30, 2019 at 9:24 PM Steinar Dragsnes <steinar....@miles.no> wrote:

I didn’t think I patched the current transaction, I thought I created a new inner transaction with a different/derived config.

I don't think it will work this way. The TransactionProvider SPI should be in control of your entire set of nested transactions. The way your code looks right now, it may well be that the outer and inner transaction providers don't "communicate" with each other. It might as well work - I don't know your implementation of the TransactionProvider, but it does look like something the next developer might easily get wrong when modifying the code.

 

So if I do a config.derive before starting the transaction then I am fine?

That's what I would have done, yes.

 

The main problem as I can see it right now is that the TransactionDefinition is hardcoded in the SpringTransactionProvider. For most things that is fine but this is a special case where I must use a different transaction isolation. 

Would you mind sharing your SpringTransactionProvider?

Thanks,

Lukas

[Steinar Dragsnes]

Hi again,

I have rewritten the implementation now to only manipulate the configuration before starting the transaction as you pointed out. This is what is happening:

Configuration derivedCfg;
TransactionProvider jooqTransactionProvider = cfg.transactionProvider();
if (jooqTransactionProvider instanceof SpringTransactionProvider) {

    SpringTransactionProvider stp = (SpringTransactionProvider) jooqTransactionProvider;

    SpringTransactionProvider localStp = stp.derive(cfg.connectionProvider(), TransactionDefinition.ISOLATION_SERIALIZABLE);

    derivedCfg = cfg.derive(localStp);

} else {
    // Use default configuration and transaction definition

    derivedCfg = cfg;
}

try (DSLContext ctx = new DefaultDSLContext(derivedCfg)) {
    ctx.transaction(() -> ctx.insertInto(table("")).columns(field("")).values("").execute()); // <-- just dummy example
}

The noisy code creating the derived config is moved into a helper class so that I don't need to have this spread around my DAOs:

try (DSLContext ctx = dbCon.transactionWithIsolationLevel(TransactionDefinition.ISOLATION_SERIALIZABLE)) {
        ctx.transaction(() -> savedResult.set(saveMethod(ctx, entity))
    );
    return savedResult.get();
}

The SpringTransactionProvider is as it was in the previous post:

/**
 * An example <code>TransactionProvider</code> implementing the {@link TransactionProvider} contract for use with
 * Spring.
 *
 * @author Lukas Eder
 */
public class SpringTransactionProvider extends ThreadLocalTransactionProvider implements TransactionProvider {

    private static final JooqLogger LOGGER = JooqLogger.getLogger(SpringTransactionProvider.class);
    private DataSourceTransactionManager txMgr;
    private int transactionDefinition;

    public SpringTransactionProvider(ConnectionProvider provider, DataSourceTransactionManager txMgr) {
        super(provider);
        this.txMgr = txMgr;
        this.transactionDefinition = PROPAGATION_NESTED;
    }

    public SpringTransactionProvider(ConnectionProvider provider, DataSourceTransactionManager txMgr, int transactionDefinition) {
        super(provider);
        this.txMgr = txMgr;
        this.transactionDefinition = transactionDefinition;

    }

    @Override
    public void begin(TransactionContext ctx) {

        LOGGER.info("Begin transaction");

        // This TransactionProvider behaves like jOOQ's DefaultTransactionProvider,
        // which supports nested transactions using Savepoints

        TransactionStatus tx = txMgr.getTransaction(new DefaultTransactionDefinition(transactionDefinition));

        ctx.transaction(new SpringTransaction(tx));
    }

    @Override
    public void commit(TransactionContext ctx) {

        LOGGER.info("commit transaction");

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

    @Override
    public void rollback(TransactionContext ctx) {

        LOGGER.info("rollback transaction");

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

    public SpringTransactionProvider derive(ConnectionProvider provider, int transDefinition) {
        return new SpringTransactionProvider(provider, txMgr, transactionDefinition);
    }
}

What do you think about this approach?

Cheers Steinar

--

[steinar....@miles.no]

Hi!

After digging some more around I realized that TransactionDefinition basically holds two enums, TransactionPropagationBehaviour and TransactionIsolationLevel, both mixed into consts. Really confusing as the DefaultTransactionDefinition only takes propagation behaviour in the ctor.

So having this in mind then the proper refactoring of the SpringTransactionProvider should be something like this:

/**
 * An example <code>TransactionProvider</code> implementing the {@link TransactionProvider} contract for use with
 * Spring.
 *
 * @author Lukas Eder
 */
public class SpringTransactionProvider extends ThreadLocalTransactionProvider implements TransactionProvider {

    private static final JooqLogger LOGGER = JooqLogger.getLogger(SpringTransactionProvider.class);
    private DataSourceTransactionManager txMgr;

    private int propagationBehavior;
    private int isolationLevel;

    public SpringTransactionProvider(ConnectionProvider provider, DataSourceTransactionManager txMgr) {
        super(provider);
        this.txMgr = txMgr;

        this.propagationBehavior = PROPAGATION_NESTED;
        this.isolationLevel = ISOLATION_DEFAULT;
    }

    public SpringTransactionProvider(ConnectionProvider provider, DataSourceTransactionManager txMgr, int propagationBehavior, int isolationLevel) {
        super(provider);
        this.txMgr = txMgr;
        this.propagationBehavior = propagationBehavior;
        this.isolationLevel = isolationLevel;

    }

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

        // This TransactionProvider behaves like jOOQ's DefaultTransactionProvider,
        // which supports nested transactions using Savepoints

        DefaultTransactionDefinition def = new DefaultTransactionDefinition(propagationBehavior);
        if(isolationLevel != ISOLATION_DEFAULT) {
            def.setIsolationLevel(isolationLevel);
        }
        TransactionStatus tx = txMgr.getTransaction(def);

        ctx.transaction(new SpringTransaction(tx));
    }

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

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

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

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

    public SpringTransactionProvider derive(ConnectionProvider provider, int propagationBehavior, int isolationLevel) {
        return new SpringTransactionProvider(provider, txMgr, propagationBehavior, isolationLevel);
    }
}

Now both propagation behaviour and isolation level can be specified in special situations.

I use it like this:

try (DSLContext ctx = dbCon.useCustomTransaction(TransactionDefinition.PROPAGATION_NESTED, TransactionDefinition.ISOLATION_SERIALIZABLE)) {
    ctx.transaction(() -> savedResult.set(
            saveEntity(ctx, entity))
    );
    return savedResult.get();
}

Hope this is the correct way forward.

Cheers,

Steinar.

[Lukas Eder]

Hi Steinar,

From a high level, this looks great.

The noisy code creating the derived config is moved into a helper class so that I don't need to have this spread around my DAOs:

try (DSLContext ctx = dbCon.transactionWithIsolationLevel(TransactionDefinition.ISOLATION_SERIALIZABLE)) {
        ctx.transaction(() -> savedResult.set(saveMethod(ctx, entity))
    );
    return savedResult.get();
}

You could do it this way, or even extend jOOQ's DSLContext with your own, providing additional transaction() methods that take the isolation level as an argument.

Hope this helps,

Lukas