Skip to main content

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)
) ENGINE=InnoDB;

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 table must use a transactional engine such as InnoDB.

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​

InnoDB accepts READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, and SERIALIZABLE. REPEATABLE_READ is the usual default, but pool or session settings can change it. Isolation.DEFAULT keeps the current connection setting. These guarantees require InnoDB; isolation settings do not make nontransactional tables rollback-capable. 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);