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.

Driver version for nested transactions

For NESTED transactions, use Microsoft JDBC Driver 13.4. Version 12.6 reports “This operation is not supported” when releasing a savepoint, causing the nested commit to fail.

Isolation settings

SQL Server supports the four standard JDBC isolation levels. READ_COMMITTED behavior also depends on the database's READ_COMMITTED_SNAPSHOT setting. SNAPSHOT isolation is an extension with no corresponding Isolation enum value; do not substitute REPEATABLE_READ for it. 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);