create another connection in transaction mysql

Question

I want know if it is possibile create two connection different connections in one transaction, So suppose to have this code( I do a create operation in table "employe" and I create a log in table "log"):

    try {
        InitialContext initialContext = new InitialContext();
                    DataSource datasource = (DataSource) initialContext.lookup("java:/comp/env/jdbc/postgres");

                    Connection connection_db= datasource.getConnection();
                    PreparateStatement p1 //Preparate statement to put the employe parameter

                    connessione_db.setAutoCommit(false);
                    connessione_db.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
                p1.execute();
                //This create another connect 
                LOGStatic.createLog("CREATE EMPLOYE");             

                connessione_db.commit();
                connessione_db.setAutoCommit(true);
         }catch(....){
                   connection_db.rollback();
        }

This is the method LOGStatic.createLog("CREATE EMPLOYE");

public static void creaLogException(String messaggio) {
        Connection connection_db= null;

        InitialContext initialContext;
        try {
            initialContext = new InitialContext();
            DataSource datasource = (DataSource) initialContext.lookup("java:/comp/env/jdbc/postgres");

            connection_db= datasource.getConnection();
            // the code continues with the save operation

        } catch (NamingException n) {

        }
        // actual jndi name is "jdbc/postgres"
        catch (SQLException s) {

        }

My question if is it possible this behavior and there aren't problem with commit and rollback operatio or it is not possible to have another connection? Anyone can help

Teddy · Accepted Answer · 2018-07-25T13:59:23.070

At least in JDBC, the transaction is tightly coupled with the Connection.

In a Java EE server, if you are writing session beans, then the transaction will be managed my the server. So, in that case, you could call several methods, and the transaction will follow the method calls.

In JDBC the simple solution is to not close the connection, but to pass it around to different methods as an input parameter.

But, be very careful, not closing connections is almost a sure shot way of getting to OutOfMemoryError.

(Instead of passing around the connection as input parameters to various methods, you could use ThreadLocal. But that is another source of memory leak, if you don't clean up ThreadLocal variables.)

For more information on how transaction-lifecycle and Connection are tied in JDBC, refer this: How to start a transaction in JDBC?

Note: Even in JavaEE nested transaction is not possible as nested transactions is not supported in JTA.

Passing the connection around:

public static void createEmployee(){
    InitialContext initialContext = new InitialContext();
    DataSource datasource = (DataSource) initialContext.lookup("java:/comp/env/jdbc/postgres");
    Connection connection_db= datasource.getConnection();
    try {
        connessione_db.setAutoCommit(false);
        connessione_db.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

        PreparateStatement p1 //Preparate statement to put the employe parameter
        p1.execute();
        //This create another connect 
        createLog("CREATE EMPLOYE", connessione_db);             

        connessione_db.commit();
        //connessione_db.setAutoCommit(true); //No need
    }catch(....){
        try{ connection_db.rollback(); }catch(Exception e){ /*You can also check some flags to avoid exception*/ }
    }finally{
        try{ connection_db.close(); }catch(Exception e){ /*Safe to ignore*/ }
    }
}

public static void createLog(String messaggio, Connection connection_db) {
    try {
        // the code continues with the save operation

    } catch (SQLException s) {

    }
}

create another connection in transaction mysql

1 Answers1