When Atomikos meets Sql2o

 Today we play with something we don't normally do in a live application. We are going to married Atomikos and Sql2o.

Atomikos is a lightweight transaction manager for Java that enables applications using distributed transactions to be self-contained.

Sql2o is a small Java library, that makes it easy to execute sql statements against your JDBC compliant database.

We normally use Atomikos to manage the transaction. Sql2o also has it's own way for transaction management. 

// Sql2o Transaction
String sql1 = "INSERT INTO SomeTable(id, value) VALUES (:id, :value)";
String sql2 = "UPDATE SomeOtherTable SET value = :val WHERE id = :id";

try (Connection con = sql2o.beginTransaction()) {
    con.createQuery(sql1).addParameter("id", idVariable1).addParameter("val", valueVariable1).executeUpdate();
    con.createQuery(sql2).addParameter("id", idVariable2).addParameter("val", valueVariable2).executeUpdate();
    con.commit();
} 

So if we mix the two together, what kind of chemistry they will create?

Ok, first we create an init.sql script for the fruits.

--- We want clean data to test
DROP TABLE APPLE;
CREATE TABLE APPLE (
    id serial primary key,
    name varchar(50) not null
);

--- We want clean data to test
DROP TABLE MANGO;
CREATE TABLE MANGO (
    id serial primary key,
    name varchar(50) not null
);

--- We want clean data to test
DROP TABLE BANANA;
CREATE TABLE BANANA (
    id serial primary key,
    name varchar(50) not null
);

Then we create a DatasourceFactory that will give us 2 types of datasources. 

import java.util.Properties;
import javax.sql.DataSource;
import com.atomikos.jdbc.AtomikosDataSourceBean;
import org.apache.commons.dbcp2.BasicDataSource;

public class DatasourceFactory {

    static final String JDBC_USER = "sa";
    static final String JDBC_PASSWORD = "sa";
    static final String JDBC_URL = "jdbc:h2:~/atom2sql-db;INIT=RUNSCRIPT FROM 'classpath:init.sql'";

    public static DataSource getDatasource() {
        BasicDataSource ds = new BasicDataSource(); 
        ds.setInitialSize(3);
        ds.setUsername(JDBC_USER);
        ds.setPassword(JDBC_PASSWORD);
        ds.setUrl(JDBC_URL);
        return ds;
    }

    public static DataSource getAtomikosDatasource() {
        AtomikosDataSourceBean ds = new AtomikosDataSourceBean();
        ds.setUniqueResourceName("H2-DATASOURCE");
        ds.setXaDataSourceClassName("org.h2.jdbcx.JdbcDataSource");
        Properties p = new Properties();
        p.setProperty("user", JDBC_USER);
        p.setProperty("password", JDBC_PASSWORD);
        p.setProperty("URL", JDBC_URL);
        ds.setXaProperties(p);
        ds.setPoolSize(3);
        return ds;

    }
}

This is the Fruit

@Data
public class Fruit {
    private Integer id;
    private String name;
}

We create 3 simple functions to do some basic operation.

    private static void howManyFruit(String fruitName) {
        String query = "select count(*) from " + fruitName;
        log.info(query);
        try (Connection con = sql2o.open()) {
            Integer count = con.createQuery(query).executeScalar(Integer.class);
            log.info("count = {}", count);
        }
    }

    private static void putInSomeFruit(String fruitName) {
        String query = "INSERT INTO " + fruitName + "(name) VALUES(:name)";
        log.info(query);
        try (Connection con = sql2o.beginTransaction()) {
            for (int i = 1; i <= 5; i++) {
                con.createQuery(query).addParameter("name", fruitName + " " + i).executeUpdate();
            }
            con.commit();
        }
    }

    private static void showMeTheFruits(String fruitName) {
        String query = "select * from " + fruitName;
        log.info(query);
        try (Connection con = sql2o.open()) {
            List<Fruit> list = con.createQuery(query).executeAndFetch(Fruit.class);
            list.forEach(f -> {
                log.info("{}", f);
            });
        }
    }

Now let the fun begins... 

First test. Normal Datasource (DBCP). Our code is simple.

    public static void main(String[] args) {
        // DataSource dataSource = DatasourceFactory.getAtomikosDatasource();
        DataSource dataSource = DatasourceFactory.getDatasource();
        sql2o = new Sql2o(dataSource);

        howManyFruit(APPLE);
        putInSomeFruit(APPLE);
        showMeTheFruits(APPLE);
    }

and the output looks like this.

[main] INFO ong.ternchow.app.App - select count(*) from APPLE
[main] INFO ong.ternchow.app.App - count = 0
[main] INFO ong.ternchow.app.App - INSERT INTO APPLE(name) VALUES(:name)
[main] INFO ong.ternchow.app.App - select * from APPLE
[main] INFO ong.ternchow.app.App - Fruit(id=1, name=APPLE 1)
[main] INFO ong.ternchow.app.App - Fruit(id=2, name=APPLE 2)
[main] INFO ong.ternchow.app.App - Fruit(id=3, name=APPLE 3)
[main] INFO ong.ternchow.app.App - Fruit(id=4, name=APPLE 4)
[main] INFO ong.ternchow.app.App - Fruit(id=5, name=APPLE 5)

Now we change the Datasource to AtomikosDatasource

DataSource dataSource = DatasourceFactory.getAtomikosDatasource();

And now the result looks like this.

[main] INFO ong.ternchow.app.App - select count(*) from APPLE
[main] WARN com.atomikos.jdbc.internal.AtomikosJdbcConnectionProxy - atomikosJdbcConnectionProxy (state = sessionHandleState (1 context(s), isTerminated = false) for resource H2-DATASOURCE) for vendor instance conn1: url=jdbc:h2:~/atom2sql-db user=SA: WARNING: transaction manager not running?
[main] WARN com.atomikos.jdbc.internal.AtomikosJdbcConnectionProxy - atomikosJdbcConnectionProxy (state = sessionHandleState (1 context(s), isTerminated = false) for resource H2-DATASOURCE) for vendor instance conn1: url=jdbc:h2:~/atom2sql-db user=SA: WARNING: transaction manager not running?
[main] INFO ong.ternchow.app.App - count = 0
[main] WARN com.atomikos.jdbc.internal.AtomikosJdbcConnectionProxy - atomikosJdbcConnectionProxy (state = sessionHandleState (1 context(s), isTerminated = false) for resource H2-DATASOURCE) for vendor instance conn1: url=jdbc:h2:~/atom2sql-db user=SA: WARNING: transaction manager not running?
[main] WARN com.atomikos.jdbc.internal.AtomikosJdbcConnectionProxy - atomikosJdbcConnectionProxy (state = sessionHandleState (1 context(s), isTerminated = false) for resource H2-DATASOURCE) for vendor instance conn1: url=jdbc:h2:~/atom2sql-db user=SA: WARNING: transaction manager not running?
[main] INFO ong.ternchow.app.App - INSERT INTO APPLE(name) VALUES(:name)
[main] WARN com.atomikos.jdbc.internal.AtomikosJdbcConnectionProxy - atomikosJdbcConnectionProxy (state = sessionHandleState (1 context(s), isTerminated = false) for resource H2-DATASOURCE) for vendor instance conn1: url=jdbc:h2:~/atom2sql-db user=SA: WARNING: transaction manager not running?
[main] WARN com.atomikos.jdbc.internal.AtomikosJdbcConnectionProxy - atomikosJdbcConnectionProxy (state = sessionHandleState (1 context(s), isTerminated = false) for resource H2-DATASOURCE) for vendor instance conn1: url=jdbc:h2:~/atom2sql-db user=SA: WARNING: transaction manager not running?
[main] WARN com.atomikos.jdbc.internal.AtomikosJdbcConnectionProxy - atomikosJdbcConnectionProxy (state = sessionHandleState (1 context(s), isTerminated = false) for resource H2-DATASOURCE) for vendor instance conn1: url=jdbc:h2:~/atom2sql-db user=SA: WARNING: transaction manager not running?
[main] WARN com.atomikos.jdbc.internal.AtomikosJdbcConnectionProxy - atomikosJdbcConnectionProxy (state = sessionHandleState (1 context(s), isTerminated = false) for resource H2-DATASOURCE) for vendor instance conn1: url=jdbc:h2:~/atom2sql-db user=SA: WARNING: transaction manager not running?
[main] WARN com.atomikos.jdbc.internal.AtomikosJdbcConnectionProxy - atomikosJdbcConnectionProxy (state = sessionHandleState (1 context(s), isTerminated = false) for resource H2-DATASOURCE) for vendor instance conn1: url=jdbc:h2:~/atom2sql-db user=SA: WARNING: transaction manager not running?
[main] WARN com.atomikos.jdbc.internal.AtomikosJdbcConnectionProxy - atomikosJdbcConnectionProxy (state = sessionHandleState (1 context(s), isTerminated = false) for resource H2-DATASOURCE) for vendor instance conn1: url=jdbc:h2:~/atom2sql-db user=SA: WARNING: transaction manager not running?
[main] WARN com.atomikos.jdbc.internal.AtomikosJdbcConnectionProxy - atomikosJdbcConnectionProxy (state = sessionHandleState (1 context(s), isTerminated = false) for resource H2-DATASOURCE) for vendor instance conn1: url=jdbc:h2:~/atom2sql-db user=SA: WARNING: transaction manager not running?
[main] WARN com.atomikos.jdbc.internal.AtomikosJdbcConnectionProxy - atomikosJdbcConnectionProxy (state = sessionHandleState (1 context(s), isTerminated = false) for resource H2-DATASOURCE) for vendor instance conn1: url=jdbc:h2:~/atom2sql-db user=SA: WARNING: transaction manager not running?
[main] WARN com.atomikos.jdbc.internal.AtomikosJdbcConnectionProxy - atomikosJdbcConnectionProxy (state = sessionHandleState (1 context(s), isTerminated = false) for resource H2-DATASOURCE) for vendor instance conn1: url=jdbc:h2:~/atom2sql-db user=SA: WARNING: transaction manager not running?
[main] INFO ong.ternchow.app.App - select * from APPLE
[main] WARN com.atomikos.jdbc.internal.AtomikosJdbcConnectionProxy - atomikosJdbcConnectionProxy (state = sessionHandleState (1 context(s), isTerminated = false) for resource H2-DATASOURCE) for vendor instance conn1: url=jdbc:h2:~/atom2sql-db user=SA: WARNING: transaction manager not running?
[main] WARN com.atomikos.jdbc.internal.AtomikosJdbcConnectionProxy - atomikosJdbcConnectionProxy (state = sessionHandleState (1 context(s), isTerminated = false) for resource H2-DATASOURCE) for vendor instance conn1: url=jdbc:h2:~/atom2sql-db user=SA: WARNING: transaction manager not running?
[main] INFO ong.ternchow.app.App - Fruit(id=1, name=APPLE 1)
[main] INFO ong.ternchow.app.App - Fruit(id=2, name=APPLE 2)
[main] INFO ong.ternchow.app.App - Fruit(id=3, name=APPLE 3)
[main] INFO ong.ternchow.app.App - Fruit(id=4, name=APPLE 4)
[main] INFO ong.ternchow.app.App - Fruit(id=5, name=APPLE 5)
[main] WARN com.atomikos.jdbc.internal.AtomikosJdbcConnectionProxy - atomikosJdbcConnectionProxy (state = sessionHandleState (1 context(s), isTerminated = false) for resource H2-DATASOURCE) for vendor instance conn1: url=jdbc:h2:~/atom2sql-db user=SA: WARNING: transaction manager not running?
[main] WARN com.atomikos.jdbc.internal.AtomikosJdbcConnectionProxy - atomikosJdbcConnectionProxy (state = sessionHandleState (1 context(s), isTerminated = false) for resource H2-DATASOURCE) for vendor instance conn1: url=jdbc:h2:~/atom2sql-db user=SA: WARNING: transaction manager not running?

Atomikos keep screaming "WTF are you doing!?" "WTF are you doing!?" "WTF are you doing!?"

And Sql2o just said, "Nah~ I'll just commit the changes.". And it did. The apples were created. 

Ok. Now we introduce com.atomikos.icatch.jta.UserTransactionManager. We update the code like this:

    public static void main(String[] args) throws Exception {
        
        UserTransactionManager utm = new UserTransactionManager();
        utm.setForceShutdown(true);
        utm.init();
        TransactionManager tm = utm;

        DataSource dataSource = DatasourceFactory.getAtomikosDatasource();
        // DataSource dataSource = DatasourceFactory.getDatasource();
        sql2o = new Sql2o(dataSource);

        try {
            tm.begin();
            howManyFruit(APPLE);
            putInSomeFruit(APPLE);
            showMeTheFruits(APPLE);
            tm.commit();
        } catch(Exception e) {
            log.error("", e);
        } finally {
            utm.close();
        }
    }

and we have some interesting findings.

[main] INFO ong.ternchow.app.App - select count(*) from APPLE
[main] INFO ong.ternchow.app.App - count = 0
[main] WARN com.atomikos.jdbc.internal.AtomikosSQLException - Cannot call method 'rollback' while a global transaction is running
[main] WARN org.sql2o.Connection - Could not roll back transaction. message: {}
com.atomikos.jdbc.internal.AtomikosSQLException: Cannot call method 'rollback' while a global transaction is running
        at com.atomikos.jdbc.internal.AtomikosSQLException.throwAtomikosSQLException(AtomikosSQLException.java:29)
        at com.atomikos.jdbc.internal.AtomikosSQLException.throwAtomikosSQLException(AtomikosSQLException.java:40)
        at com.atomikos.jdbc.internal.AbstractJdbcConnectionProxy.rollback(AbstractJdbcConnectionProxy.java:176)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:566)
        at com.atomikos.util.DynamicProxySupport.callProxiedMethod(DynamicProxySupport.java:159)
        at com.atomikos.util.DynamicProxySupport.invoke(DynamicProxySupport.java:116)
        at com.sun.proxy.$Proxy3.rollback(Unknown Source)
        at org.sql2o.Connection.rollback(Connection.java:132)
        at org.sql2o.Connection.close(Connection.java:296)
        at ong.ternchow.app.App.howManyFruit(App.java:55)
        at ong.ternchow.app.App.main(App.java:38)

As we can see, we call howManyFruit(APPLE) and it already throws an exception. We are using sql2o.open() and it seems that sql2o will call rollback() when closing the connection.

Now, let's change the howManyFruit(APPLE) function a little.

    private static void howManyFruit(String fruitName) {
        String query = "select count(*) from " + fruitName;
        log.info(query);
        try (Connection con = sql2o.beginTransaction()) {
            Integer count = con.createQuery(query).executeScalar(Integer.class);
            log.info("count = {}", count);
            con.commit();
        }
    }

This is a bad example because you should not need to commit in a select operation. For the sake of the experiment, who cares~ lol

Now, this is what we got this time.

[main] INFO ong.ternchow.app.App - select count(*) from APPLE
[main] INFO ong.ternchow.app.App - count = 0
[main] WARN com.atomikos.jdbc.internal.AtomikosSQLException - Cannot call method 'commit' while a global transaction is running
[main] WARN com.atomikos.jdbc.internal.AtomikosSQLException - Cannot call 'setAutoCommit(true)' while a global transaction is running
[main] WARN org.sql2o.Connection - Could not reset autocommit state for connection to true.
com.atomikos.jdbc.internal.AtomikosSQLException: Cannot call 'setAutoCommit(true)' while a global transaction is running
        ...
        at com.sun.proxy.$Proxy3.setAutoCommit(Unknown Source)
        at org.sql2o.Connection.resetAutoCommitState(Connection.java:328)
        at org.sql2o.Connection.closeJdbcConnection(Connection.java:315)
        at org.sql2o.Connection.commit(Connection.java:156)
        at org.sql2o.Connection.commit(Connection.java:144)
        at ong.ternchow.app.App.howManyFruit(App.java:55)
        at ong.ternchow.app.App.main(App.java:38)
[main] ERROR ong.ternchow.app.App - 
org.sql2o.Sql2oException: com.atomikos.jdbc.internal.AtomikosSQLException: Cannot call method 'commit' while a global transaction is running
        at org.sql2o.Connection.commit(Connection.java:152)
        at org.sql2o.Connection.commit(Connection.java:144)
        at ong.ternchow.app.App.howManyFruit(App.java:55)
        at ong.ternchow.app.App.main(App.java:38)
Caused by: com.atomikos.jdbc.internal.AtomikosSQLException: Cannot call method 'commit' while a global transaction is running
        at com.atomikos.jdbc.internal.AtomikosSQLException.throwAtomikosSQLException(AtomikosSQLException.java:29)
        at com.atomikos.jdbc.internal.AtomikosSQLException.throwAtomikosSQLException(AtomikosSQLException.java:40)
        at com.atomikos.jdbc.internal.AbstractJdbcConnectionProxy.commit(AbstractJdbcConnectionProxy.java:168)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:566)
        at com.atomikos.util.DynamicProxySupport.callProxiedMethod(DynamicProxySupport.java:159)
        at com.atomikos.util.DynamicProxySupport.invoke(DynamicProxySupport.java:116)
        at com.sun.proxy.$Proxy3.commit(Unknown Source)
        at org.sql2o.Connection.commit(Connection.java:149)
        ... 3 more

"You can't commit. I'm in charge", said Atomikos. 

After playing around, the only way to make it work is to change the functions into these:

    private static void howManyFruit(String fruitName) {
        String query = "select count(*) from " + fruitName;
        log.info(query);
        Connection con = sql2o.open(); // only allowed to open but not close.
        Integer count = con.createQuery(query).executeScalar(Integer.class);
        log.info("count = {}", count);
    }

    private static void putInSomeFruit(String fruitName) {
        String query = "INSERT INTO " + fruitName + "(name) VALUES(:name)";
        log.info(query);
        Connection con = sql2o.beginTransaction();
        for (int i = 1; i <= 5; i++) {
            con.createQuery(query).addParameter("name", fruitName + " " + i).executeUpdate();
        }
        // con.commit(); // can't commit here :(
    }

    private static void showMeTheFruits(String fruitName) {
        String query = "select * from " + fruitName;
        log.info(query);
        Connection con = sql2o.open();
        con.createQuery(query).executeAndFetch(Fruit.class).forEach(f -> {
            log.info("{}", f);
        });
    }

Because sql2o will always try to call rollback()when closing the connection, we can not use the try-with-resources statement anymore. Commit() is totally forbidden. 

Now the responsibility of transaction management goes to Atomikos, our main function will need to be modified like this:

    try {
        tm.begin();
        howManyFruit(APPLE);
        putInSomeFruit(APPLE);
        tm.commit();
        tm.begin(); // need to start a new one after commit()
        showMeTheFruits(APPLE);
    } catch (Exception e) {
        tm.rollback();
        log.error("", e);
    } finally {
        utm.close();
    }

Worth mention is we need to call tm.begin(); again after the transaction is committed. 

Now, we are able to see the apples again.

[main] INFO ong.ternchow.app.App - select count(*) from APPLE
[main] INFO ong.ternchow.app.App - count = 0
[main] INFO ong.ternchow.app.App - INSERT INTO APPLE(name) VALUES(:name)
[main] INFO ong.ternchow.app.App - select * from APPLE
[main] INFO ong.ternchow.app.App - Fruit(id=1, name=APPLE 1)
[main] INFO ong.ternchow.app.App - Fruit(id=2, name=APPLE 2)
[main] INFO ong.ternchow.app.App - Fruit(id=3, name=APPLE 3)
[main] INFO ong.ternchow.app.App - Fruit(id=4, name=APPLE 4)
[main] INFO ong.ternchow.app.App - Fruit(id=5, name=APPLE 5)
[main] INFO com.atomikos.icatch.imp.TransactionServiceImp - Entering shutdown (false, 0)...

Conclusion

Atomikos and Sql2o do seem to be able to play nice together after all. 

Do not use this code in PRODUCTION. It is only for a simple proof of concept. 

Source code is hosted here: https://github.com/devilkazuya99/atomikos-sql2o