7.7. Working with Transactions

By default, jOOQ runs in a mode that commits transactions automatically. It starts a new transaction for each SQL statement and commits the transaction if there are no errors in the execution of the statement. The transaction is rolled back if an error occurs.

The default transaction has the following parameters: READ_WRITE | READ_COMMITTED | REC_VERSION | WAIT, the same parameters that are used by the JDBC driver. You can change the default isolation mode using the parameters of the connection pool — see BasicDataSource.setDefaultTransactionIsolation in the getDataSource method of the JooqConfig configuration class.

7.7.1. Explicit Transactions

In jOOQ you have several ways to control transactions explicitly. Since we are going to develop our application using the Spring Framework, we will use the transaction manager specified in the configuration (JooqConfig). You can get the transaction manager by declaring the txMgr property in the class as follows:

@Autowired
private DataSourceTransactionManager txMgr;

The standard scenario for using this technique with a transaction would be coded like this:

TransactionStatus tx = txMgr.getTransaction(new DefaultTransactionDefinition());
try {
    // actions in the context of a transaction
    for (int i = 0; i < 2; i++)
        dsl.insertInto(BOOK)
           .set(BOOK.ID, 5)
           .set(BOOK.AUTHOR_ID, 1)
           .set(BOOK.TITLE, "Book 5")
           .execute();
    // transaction commit
    txMgr.commit(tx);
}
catch (DataAccessException e) {
    // transaction rollback
    txMgr.rolback(tx);
}

However, Spring enables that scenario to be implemented much more easily using the @Transactional annotation specified before the method of the class. Thereby, all actions performed by the method will be wrapped in the transaction.

/**
 * Delete customer
 *
 * @param customerId
 */
@Transactional(propagation = Propagation.REQUIRED,
               isolation = Isolation.REPEATABLE_READ)
public void delete(int customerId) {
    this.dsl.deleteFrom(CUSTOMER)
            .where(CUSTOMER.CUSTOMER_ID.eq(customerId))
            .execute();
}

Transaction Parameters

Propagation

The propagation parameter defines how to work with transactions if our method is called from an external transaction.

• Propagation.REQUIRED

  execute in the existing transaction if there is one. Otherwise, create a new one.

  Propagation.MANDATORY

  execute in the existing transaction if there is one. Otherwise, raise an exception.

  Propagation.SUPPORTS

  execute in the existing transaction if there is one. Otherwise, execute outside the transaction.

  Propagation.NOT_SUPPORTED

  always execute outside the transaction. If there is an existing one, it will be suspended.

  Propagation.REQUIRES_NEW

  always execute in a new independent transaction. If there is an existing one, it will be suspended until the new transaction is ended.

  Propagation.NESTED

  if there is an existing transaction, execute in a new so-called “nested” transaction. If the nested transaction is rolled back, it will not affect the external transaction; if the external transaction is rolled back, the nested one will be rolled back as well. If there is no existing transaction, a new one is simply created.

  Propagation.NEVER

  always execute outside the transaction. Raise an exception if there is an existing one.

Isolation Level

The isolation parameter defines the isolation level. Five values are supported: DEFAULT, READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE. If the DEFAULT value of the isolation parameter is specified, that level will be used.

The other isolation levels are taken from the SQL standard, not all of them supported exactly by Firebird. Only the READ_COMMITED level corresponds in all of the criteria, so JDBC READ_COMMITTED is mapped into read_committed in Firebird. REPEATABLE_READ is mapped into concurrency (SNAPSHOT) and SERIALIZABLE is mapped into consistency (SNAPSHOT TABLE STABILITY).

Firebird supports additional transaction parameters besides isolation level, viz. NO RECORD_VERSION/RECORD_VERSION (applicable only to a transaction with READ COMMITTED isolation) and WAIT/NO WAIT. The standard isolation levels can be mapped to Firebird transaction parameters by specifying the properties of the JDBC connection (see more details in the Using Transactions chapter of Jaybird 2.1 JDBC driver Java Programmer’s Manual).

If your transaction works with more than one query, it is recommended to use the REPEATABLE_READ isolation level to maintain data consistency.

Read Mode

By default, a transaction is in the read-write mode. The readOnly property in the @Transactional annotation can be used to specify that it is to be read-only.