Transaction Support

Multiple-Write Consistency

Assume user_info is empty and id is its primary key:

CREATE TABLE user_info (
    id NUMBER(10) PRIMARY KEY,
    name VARCHAR2(100)
);

The Builder API below inserts three records. The first two deliberately use the same primary key:

import java.util.List;
import java.util.Map;
import net.hasor.dbvisitor.lambda.LambdaTemplate;

LambdaTemplate lambda = new LambdaTemplate(dataSource);
List<Map<String, Object>> users = List.of(
        Map.of("ID", 1001, "NAME", "Alice"),
        Map.of("ID", 1001, "NAME", "Bob"),
        Map.of("ID", 1002, "NAME", "Carol"));

lambda.insert("USER_INFO")
        .applyMap(users)
        .executeSumResult();

dbVisitor executes INSERT statements individually in list order, rather than combining the records into one statement:

1. Alice is inserted successfully.
2. Bob fails with a duplicate primary key exception.
3. Carol is not inserted.

Note

Without a transaction, when the connection uses auto-commit, Alice has already been committed. The exception does not undo that insert: Alice remains in the table.

Use a transaction to insert all three records successfully or roll them all back. Start again with the empty table and the same three records:

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 -> {
    return lambda.insert("USER_INFO")
            .applyMap(users)
            .executeSumResult();
});

When Bob's insert fails, the exception leaves the callback and Alice's insert is rolled back too. None of these three records remains in the table. The statements still execute individually.

Use the same dataSource for lambda and the transaction manager. The calling method must handle or declare the exception thrown by execute. See Transaction Template for complete usage.

Choosing an isolation level

Use READ_COMMITTED for normal transactions, or SERIALIZABLE when serializable isolation is required. Do not configure REPEATABLE_READ; Oracle JDBC rejects it rather than converting it to SERIALIZABLE.

The transaction template above can select SERIALIZABLE explicitly:

tx.execute(status -> {
    return lambda.insert("USER_INFO").applyMap(users).executeSumResult();
}, Propagation.REQUIRED, Isolation.SERIALIZABLE);

Import Isolation and Propagation from net.hasor.dbvisitor.transaction. Serializable conflicts can abort a transaction; handle the exception around the whole business transaction.