How to start an IMMEDIATE TRANSACTION in SQLITE JDBC-CodePudding

I'm having a little problem with transactions using JDBC. I want to start an IMMEDIATE transaction which in pure SQL is:

BEGIN IMMEDIATE;

In Java JDBC SQLite, you cannot do this. You can't call BEGIN IMMEDIATE on a statement if you have autocommit enabled. Committing queries will result in an "autocommit is enabled" error.

 db = DriverManager.getConnection("jdbc:sqlite:sqlite.db");

 // start a transaction using an sql query...
 db.createStatement().execute("BEGIN IMMEDIATE");

 // create another statement because this is running from another method...
 stmt = db.createStatement();
 stmt.executeUpdate("UPDATE table SET column='value' WHERE id=1");

 // this will cause an error(exception): AUTOCOMMIT IS ENABLED.
 db.commit();

The code above will throw an AUTOCOMMIT IS ENABLED exception.

However, there is also a problem when disabling autocommit because it starts the transaction after using that code. consider the code below:

db = DriverManager.getConnection("jdbc:sqlite:ez-inventory.db");
// doing the createstatement and setautocommit reciprocally still produce the same exception.
db.setAutoCommit(false);
db.createStatement().execute("BEGIN IMMEDIATE");

This code will throw another exception:

[SQLITE_ERROR] SQL error or missing database (cannot start a transaction within a transaction)

There is a setTransactionIsolation method in the connection but it's not for transaction locking. It's for isolation. I need to start a transaction using any of the SQLite transaction modes: DEFFERED, IMMEDIATE, or EXCLUSIVE

Is this possible with SQLite JDBC?

CodePudding user response：

OK I got it! You should create a Properties object with transaction_mode key and a desired transaction mode value. and put the Properties object as a parameter when your creating your new SQL Connection instance.

import java.sql.*; // <-- bad practice.. just too lazy to put the needed modules one by one for this example

public void immediate_transaction_example() throws SQLException {

    // create a properties with a transaction_mode value
    Properties sqlprop = new Properties();
    properties.put("transaction_mode", "IMMEDIATE"); // <-- can be DEFERRED, IMMEDIATE, or EXCLUSIVE


    db = new DriverManager.getConection("jdbc:sqlite:sqlite.db", sqlprop); // <-- pass the properties to the new sql connection instance.
    db.setAutoCommit(false); // <-- this will automatically begin the transaction with the specified transaction mode...
    // other new transactions attempts with immediate transaction mode will be blocked until the connection is closed.
    try {
        // proceed the transactions here...
        db.createStatement().execute("INSERT INTO table (id, value) VALUES (1, 'myvalue')");
        db.createStatement().execute("INSERT INTO table (id, value) VALUES (2, 'myvalue')");

        // no errors proceed
        db.commit();
    } catch (SQLException ex) {
        // there is an error!
        db.rollback();
    }
    db.close() // <-- you need to close the connection for sqlite to create a new immediate transaction.
}

Note: This uses xerial's sqlite-jdbc module.

Module Link: https://github.com/xerial/sqlite-jdbc