Transaction Support

Multiple-Write Consistency

Suppose two records must be saved together. If the second insert fails, the first must not remain in the database. Start with an empty table whose primary key is id:

CREATE TABLE user_info (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100)
);

These two calls deliberately use the same primary key:

import net.hasor.dbvisitor.jdbc.core.JdbcTemplate;

JdbcTemplate jdbc = new JdbcTemplate(dataSource);
jdbc.executeUpdate("INSERT INTO user_info (id, name) VALUES (?, ?)",
        new Object[] { 1001, "Alice" });
jdbc.executeUpdate("INSERT INTO user_info (id, name) VALUES (?, ?)",
        new Object[] { 1001, "Bob" });

With auto-commit enabled and no transaction started, Alice is committed first. Bob then fails with a duplicate-key exception, but Alice remains in the table.

To roll back both writes on failure, start again with an empty table and put the calls in a transaction:

import net.hasor.dbvisitor.transaction.TransactionTemplate;
import net.hasor.dbvisitor.transaction.TransactionTemplateManager;
import net.hasor.dbvisitor.transaction.support.TransactionHelper;

TransactionTemplate tx = new TransactionTemplateManager(
        TransactionHelper.txManager(dataSource));

tx.execute(status -> {
    jdbc.executeUpdate("INSERT INTO user_info (id, name) VALUES (?, ?)",
            new Object[] { 1001, "Alice" });
    jdbc.executeUpdate("INSERT INTO user_info (id, name) VALUES (?, ?)",
            new Object[] { 1001, "Bob" });
    return null;
});

Bob's failure leaves the callback as an exception and rolls back Alice's insert. Neither record remains. If the second ID is changed to 1002, both inserts succeed and commit together.

Note

Use the same dataSource for jdbc and the transaction manager. Do not catch and suppress the failure inside the callback; if you must catch it, call status.setRollback().

The calling method must handle or declare Throwable from execute. The Builder API and Mapper can also participate in the transaction; see Transaction Template.

Isolation settings

H2 accepts standard isolation settings through JDBC. It also provides SNAPSHOT, which has no corresponding Isolation enum value and is not automatically mapped to REPEATABLE_READ. Isolation.DEFAULT keeps the current connection setting. See database isolation documentation.

For example, run a query under SERIALIZABLE using the transaction template above:

import net.hasor.dbvisitor.transaction.Isolation;
import net.hasor.dbvisitor.transaction.Propagation;

tx.execute(status -> {
    return jdbc.queryForList("SELECT id, name FROM user_info");
}, Propagation.REQUIRED, Isolation.SERIALIZABLE);