jOOQ and Spring transaction management

Question

I use jOOQ 3.8 and Spring Boot 1.4.1. I see that jOOQ uses a mechanism to guarantee the handling of transactions.

If I define a method annotated as transactional and inside a execute two insert, are they executed in the same transaction, e.g.

@Transactional(propagation = Propagation.MANDATORY)
public doInsert(){
    DSL.using(configuration).insertInto(...);
    DSL.using(configuration).insertInto(...);
}

will all the executed insert rollback in case of exception? Will they be executed in one transaction?

Or, should I do as follows:

public doInsert(){
  create.transaction(configuration -> {

    DSL.using(configuration).insertInto(...);
    DSL.using(configuration).insertInto(...);    
  });
}

And what happens if I use the annotation and the jOOQ transaction as follows:

@Transactional(propagation = Propagation.MANDATORY)
public doInsert(){
  create.transaction(configuration -> {

    // Wrap configuration in a new DSLContext:
    DSL.using(configuration).insertInto(...);
    DSL.using(configuration).insertInto(...);    
  });
  throw new RuntimeException(":)");
}

Will the changes in the transaction be committed regardless of the exception? (I would expect it)

Answer 1

I see that jOOQ uses a mechanism to guarantee the handling of transactions.

jOOQ doesn't actually do that. jOOQ provides an API for convenient transaction usage through lambdas. The API, however, is implemented by you (or indirectly, by Spring) through the jOOQ TransactionProvider SPI.

Using spring only (the easiest)

Given:

DSLContext ctx = ...

If you do this:

@Transactional(propagation = Propagation.MANDATORY)
public doInsert(){
    ctx.insertInto(...);
    ctx.insertInto(...);
}

You're not using jOOQ's transaction API at all, you're using only spring, which is perfectly fine for jOOQ.

Using jOOQ (possibly with spring behind the scenes)

If you do this:

public doInsert(){
  ctx.transaction(configuration -> {
    DSL.using(configuration).insertInto(...);
    DSL.using(configuration).insertInto(...);    
  });
}

Then you use jOOQ's transaction API, which you may or may not configure to be implemented using spring transactions. By default, jOOQ will implement transactions via JDBC directly.

Using both APIs

However, this:

@Transactional(propagation = Propagation.MANDATORY)
public doInsert(){
  ctx.transaction(configuration -> {

    // Wrap configuration in a new DSLContext:
    DSL.using(configuration).insertInto(...);
    DSL.using(configuration).insertInto(...);    
  });
  throw new RuntimeException(":)");
}

Currently (jOOQ 3.8) does not work without you implementing a rather sophisticated TransactionProvider that detects spring's declarative transaction scope in the current thread's context.