JOOQ: Is a single statement implicitly transactional, or do I still have to wrap it in a transactional block?

Question

Say we have the following scenarios when wrirting a JOOQ query:

insertInto(TABLE)
.set(TABLE.NAME, "...")
.set(TABLE.FK, null)  -- breaks FK constraint!
//...
.execute();

And:

transactional(configuration -> {
    insertInto(TABLE)
    // list of sets that are OK
    .execute();

    throw new RuntimeException();

}

Are the following statements:

1. The first query will fail (at the latest) with a DataAccessException when executing the statement in the Database and roll back the entire statement (no insert is committed).

2. The second query, although already executed without error, will be rolled back upon the exception being thrown.

correct?

And finally, in the following case:

{ // non transactional code block
    insertInto(TABLE)
    // list of sets that are OK
    .execute();

    throw new RuntimeException();
}

the insert will be performed on the Database but will not be rolled back when the exception is thrown, because it is not in a transactional context.

Is this all correct, or have I misunderstood something?

Answer 1

The first query will fail (at the latest) with a DataAccessException when executing the statement in the Database

Correct

and roll back the entire statement

Well, there is no "roll back" as in a transaction roll back. The statement just fails

(no insert is committed).

Correct (in any case, nothing is committed from a statement by itself)

The second query, although already executed without error, will be rolled back upon the exception being thrown.

Correct.

the insert will be performed on the Database but will not be rolled back when the exception is thrown, because it is not in a transactional context.

Correct.

have I misunderstood something?

Yes. This isn't strictly related to jOOQ but to SQL statements in general. First off, JDBC has an auto commit mode, which is sometimes active by default.

With JDBC auto commit

When it is active, then:

• Every statement is always committed.
• Which means that if it succeeds, the statement's changes will be applied to the database by the commit.
• Which also means that if it fails, the statement has no effect because it fails. Yet, there is still a commit.

Note that auto-committing is a JDBC feature, not a jOOQ feature. So jOOQ inherits the JDBC setting here.

Without JDBC auto commit

If auto commit is inactive, then every new statement will start a transaction in JDBC. When you use jOOQ's transaction() API, then jOOQ will override the JDBC auto commit flag to become inactive and start a transaction for you. Now,

• A commit is issued if your Transactional block / lambda succeeds. All your changes are written to the database by the commit.
• A rollback is issued if your Transactional block / lambda fails with an exception. All your changes are reverted by the rollback.
• A statement still needs to succeed to have any effect when committing