Could not start the transaction - Cannot change transaction isolation level in the middle of a transaction

[pikantono]

When we use connection pool like HickariCP , ... a DataSource will be passed as argument to "Sql2o( dataSource )" class.
So, we sets IsolationLevel in config of DataSource and at this line isolation level changed !

sql2o/core/src/main/java/org/sql2o/Sql2o.java

Line 300 in f9a9091

connection.getJdbcConnection().setTransactionIsolation(isolationLevel);

and always we got exception of :

org.sql2o.Sql2oException: Could not start the transaction - Cannot change transaction isolation level in the middle of a transaction.
	at org.sql2o.Sql2o.beginTransaction(Sql2o.java:303)
	at org.sql2o.Sql2o.beginTransaction(Sql2o.java:281)
	at org.sql2o.Sql2o.beginTransaction(Sql2o.java:320)

when a external data source is used must be ignored to change isolation level again

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.util.IsolationLevel;
import org.sql2o.Sql2o;

import javax.sql.DataSource;
import java.sql.SQLException;

public class DbConnection {

    private final static DataSource notAutoCommitDataSource, autoCommitReadOnlyDataSource, autoCommitDataSource;
    private final static Sql2o notAutoCommitSql2o, autoCommitReadOnlySql2o, autoCommitSql2o;

    static {
        HikariConfig notAutoCommitConfig = new HikariConfig();
        notAutoCommitConfig.setDataSourceClassName("org.postgresql.ds.PGSimpleDataSource");
        notAutoCommitConfig.addDataSourceProperty("serverName", "127.0.0.1");
        notAutoCommitConfig.addDataSourceProperty("portNumber", "10666");
        notAutoCommitConfig.addDataSourceProperty("databaseName", "tg");
        notAutoCommitConfig.addDataSourceProperty("user", "postgres");
        notAutoCommitConfig.addDataSourceProperty("password", "1234");
        notAutoCommitConfig.setMaximumPoolSize(5);
        notAutoCommitConfig.setSchema("tma");
        notAutoCommitConfig.setAutoCommit(false);
        notAutoCommitConfig.setReadOnly(false);

        notAutoCommitDataSource = new HikariDataSource(notAutoCommitConfig);
        notAutoCommitSql2o = new Sql2o(notAutoCommitDataSource);




        HikariConfig autoCommitReadOnlyConfig = new HikariConfig();
        autoCommitReadOnlyConfig.setDataSourceClassName("org.postgresql.ds.PGSimpleDataSource");
        autoCommitReadOnlyConfig.addDataSourceProperty("serverName", "127.0.0.1");
        autoCommitReadOnlyConfig.addDataSourceProperty("portNumber", "10666");
        autoCommitReadOnlyConfig.addDataSourceProperty("databaseName", "tg");
        autoCommitReadOnlyConfig.addDataSourceProperty("user", "postgres");
        autoCommitReadOnlyConfig.addDataSourceProperty("password", "1234");
        autoCommitReadOnlyConfig.setMaximumPoolSize(1);
        autoCommitReadOnlyConfig.setSchema("tma");
        autoCommitReadOnlyConfig.setAutoCommit(true);
        autoCommitReadOnlyConfig.setReadOnly(true);

        autoCommitReadOnlyDataSource = new HikariDataSource(autoCommitReadOnlyConfig);
        autoCommitReadOnlySql2o = new Sql2o(autoCommitReadOnlyDataSource);


        HikariConfig autoCommitConfig = new HikariConfig();
        autoCommitConfig.setDataSourceClassName("org.postgresql.ds.PGSimpleDataSource");
        autoCommitConfig.addDataSourceProperty("serverName", "127.0.0.1");
        autoCommitConfig.addDataSourceProperty("portNumber", "10666");
        autoCommitConfig.addDataSourceProperty("databaseName", "tg");
        autoCommitConfig.addDataSourceProperty("user", "postgres");
        autoCommitConfig.addDataSourceProperty("password", "1234");
        autoCommitConfig.setMaximumPoolSize(1);
        autoCommitConfig.setSchema("tma");
        autoCommitConfig.setAutoCommit(true);
        autoCommitConfig.setReadOnly(false);

        autoCommitDataSource = new HikariDataSource(autoCommitConfig);
        autoCommitSql2o = new Sql2o(autoCommitDataSource);
    }

    public static org.sql2o.Connection getSql2oNotAutoCommitConnection() throws SQLException {
        return notAutoCommitSql2o.beginTransaction();
    }

    public static org.sql2o.Connection getSql2oAutoCommitReadOnlyConnection() throws SQLException {
        return autoCommitReadOnlySql2o.open();
    }

    public static org.sql2o.Connection getSql2oAutoCommitConnection() throws SQLException {
        return autoCommitSql2o.open();
    }
}

new constructor needs I think !

[pikantono]

I comment this line code in Sql2o class
//connection.getJdbcConnection().setTransactionIsolation(isolationLevel);
and all things works as fine

[aaberg]

Hi @pikantono, thanks for letting me know about this issue.

I've investigated the problem, and as far as I understand, it is an issue with the connection pool when auto-commit is set to false. The line cannot be removed without removing some core functionality from Sql2o, but I think I've found a workaround. I'll build a new version shortly with a possible fix.

Another fix is to remove config.setAutocommit(false). Autocommit on the data source level is ignored when using Sql2o, so it isn't necessary unless you also use the data source for something unrelated to Sql2o.

[aaberg]

Looking a little more into this, I suspect that setting autoCommit(false) on the data source might cause Sql2o.open() to behave differently than expected. I guess that commands are not committed before a call to commit(), which should only be necessary when running commands in a transaction.

[aaberg]

I've released version 1.9.0. Please confirm if this fixes your problem :)