如何在SQLite JDBC中开启IMMEDIATE类型的事务

问题原因 #

你遇到的两类报错都是SQLite JDBC驱动的默认事务机制导致的：

• 开启自动提交模式时，JDBC规范不允许手动调用Connection.commit()方法，每一条SQL执行完都会自动提交，所以你手动执行BEGIN IMMEDIATE后调用commit()就会抛出自动提交已开启的异常。
• 关闭自动提交模式时，SQLite JDBC驱动会默认自动开启一个DEFERRED模式的事务，你再手动执行BEGIN IMMEDIATE就等于在事务里开事务，自然会报嵌套事务错误。

实现方案 #

该需求完全可以实现，有两种常用方案可选：

方案1：配置连接默认事务模式（推荐）

直接在JDBC连接URL中添加transaction_mode参数指定全局事务模式，可选值就是SQLite支持的DEFERRED/IMMEDIATE/EXCLUSIVE，后续按照标准JDBC事务写法使用即可，不需要手动执行BEGIN语句。
示例代码：

// URL中指定事务模式为IMMEDIATE
db = DriverManager.getConnection("jdbc:sqlite:sqlite.db?transaction_mode=IMMEDIATE");
// 关闭自动提交，驱动会自动开启IMMEDIATE模式的事务
db.setAutoCommit(false);

// 执行业务逻辑
stmt = db.createStatement();
stmt.executeUpdate("UPDATE table SET column='value' WHERE id=1");

// 正常提交即可
db.commit();
db.setAutoCommit(true);

方案2：全程用原生SQL控制事务

如果需要在同一个连接中动态切换不同的事务模式，可以全程保持自动提交开启，所有事务控制完全通过原生SQL实现，不调用Connection的commit()/rollback()方法。
示例代码：

db = DriverManager.getConnection("jdbc:sqlite:sqlite.db");
// 全程保持自动提交开启
db.createStatement().execute("BEGIN IMMEDIATE;");
try {
    // 执行业务逻辑
    stmt = db.createStatement();
    stmt.executeUpdate("UPDATE table SET column='value' WHERE id=1");
    // 用SQL语句提交事务
    db.createStatement().execute("COMMIT;");
} catch (Exception e) {
    // 出错时用SQL语句回滚
    db.createStatement().execute("ROLLBACK;");
    throw e;
}

内容的提问来源于stack exchange，提问作者Gian Lorenzo Abaño