Transactions

[Giuseppa Cefalu]

Hi,

It seems to me that if a trnasaction has not been commited; I should not be in the database and therefore; I should not be able to see in the result set before printing and I should not be able to see in the print out statement. In this program I commit the first insert query only; but not the second. Then I read from the database and print. It seems that as long as the select query is issued; even if the insert has has not been committed yet; the value has been inserted in the database and it can be printed. Also, the second connection; can read the data and print it becuase it is in the database. I do not udnerstand. Could any body read my code?
Thank you.

public class Main {
  public static void main(String[] args) throws Exception {
      Connection conn1 = null;
      Connection conn2 = null;
      Statement st1 = null;
      Statement st2 = null;
      try{
         conn1 = getConnection();
         conn1.setAutoCommit(false);
        // DatabaseMetaData dbMd = conn1.getMetaData();
         conn2 = getConnection();
         conn2.setAutoCommit(false);
       //  DatabaseMetaData dbMd2 = conn2.getMetaData();

      /*  if(dbMd.supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE)){
            System.out.println("Transaction isolation level "
            + "TRANSACTION_SERIALIZABLE is supported.");
            conn1.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
       }
        if(dbMd2.supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE)){
            System.out.println("Transaction isolation level "
            + "TRANSACTION_SERIALIZABLE is supported.");
            conn2.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
       }*/


        st1 = conn1.createStatement();
        st2 = conn2.createStatement();
       // st.executeUpdate("create table survey (id int,name varchar(30));");
        st1.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");
        conn1.commit();
        st1.executeUpdate("insert into survey (id,name ) values (2,'nameValue')");
        ResultSet rs1 = st1.executeQuery("SELECT *FROM survey");
        outputResultSet(rs1);
        ResultSet rs2 = st2.executeQuery("SELECT *FROM survey");
        outputResultSet(rs2);
        conn2.commit();
     
        conn1.setAutoCommit(true);
        conn2.setAutoCommit(true);


  }catch(Exception e){
          conn1.rollback();
        //  conn2.rollback();
          System.out.println(e.getMessage());
          e.printStackTrace();
  }finally{
       try{
              st1.close();
              st2.close();
              conn1.close();
              conn2.close();
       }catch(SQLException e){
              e.printStackTrace();
       }

  }


  }
  

it prints thefollwing:

id name
---------
1 nameValue
2 nameValue

id name
----------
1 nameValue
2, nameValue

[Jan Cumps]

You might see a dirty read.
What happens if you uncomment your setTransactionIsolation() call for conn2?

[Giuseppa Cefalu]

I have tried uncommenting conn1 isolation level only, conn2 isolation level only and both and I keep getting the same result. I wonder if the setTransationIsolation is working. May be I need some permissions to get it working?

  public class Main {
  public static void main(String[] args) throws Exception {
      Connection conn1 = null;
      Connection conn2 = null;
      Statement st1 = null;
      Statement st2 = null;
      try{
         conn1 = getConnection();
         
         DatabaseMetaData dbMd = conn1.getMetaData();
         conn2 = getConnection();
         
         DatabaseMetaData dbMd2 = conn2.getMetaData();

       if(dbMd.supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE)){
            System.out.println("Transaction isolation level "
            + "TRANSACTION_SERIALIZABLE is supported.");
            conn1.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
       }
        if(dbMd2.supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE)){
            System.out.println("Transaction isolation level "
            + "TRANSACTION_SERIALIZABLE is supported.");
            conn2.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
       }


        st1 = conn1.createStatement();
        conn1.setAutoCommit(false);
        st2 = conn2.createStatement();
        conn2.setAutoCommit(false);
       // st.executeUpdate("create table survey (id int,name varchar(30));");
        st1.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");
        conn1.commit();
        st1.executeUpdate("insert into survey (id,name ) values (2,'nameValue')");
        ResultSet rs1 = st1.executeQuery("SELECT *FROM survey");
        outputResultSet(rs1);
        ResultSet rs2 = st2.executeQuery("SELECT *FROM survey");
        outputResultSet(rs2);
        conn1.commit();
        conn2.commit();
     
        conn1.setAutoCommit(true);
        conn2.setAutoCommit(true);


  }catch(Exception e){
          conn1.rollback();
          conn2.rollback();
          System.out.println(e.getMessage());
          e.printStackTrace();
  }finally{
       try{
              st1.close();
              st2.close();
              conn1.close();
              conn2.close();
       }catch(SQLException e){
              e.printStackTrace();
       }

  }


  }

[Giuseppa Cefalu]

This is anew version of the same problem. I have looked in many places to see how this is done and it seems that I am writing this code correctly. Still get a dirty read though. Any ideas of wht might be going wrong? The output is:
transation isolation level = 4
Transaction isolation level c1 set to 8
Transaction isolation level c2 set to 8
id name
----------
1 foo

id name
-----------
1 foo
2 bar

Thank you

 public class Main {


    public static void updateDatabaseWithTransaction() throws Exception{

        Connection connection = null;
        Connection connection2 = null;
        Statement statement = null;
        Statement statement2 = null;
        ResultSet rs1 = null;
        ResultSet rs2 = null;

         try {

            connection = getConnection();
            //Here we set auto commit to false so no changes will take
            //effect immediately. DatabaseMetaData metadata = connection.getMetaData();
            DatabaseMetaData metadata = connection.getMetaData();
            boolean isSupportTransaction = metadata.supportsTransactions();
            System.out.println("Support Transaction = " + isSupportTransaction);
            connection2 = getConnection();
        
            if(metadata.supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE)){
               System.out.println("Transaction isolation level=" + connection.getTransactionIsolation());
               connection.setTransactionIsolation(8);
               connection2.setTransactionIsolation(8);
               System.out.println("Transaction isolatio level c1 set to " + connection.getTransactionIsolation());
               System.out.println("Transaction isolation level c2 set to " + connection2.getTransactionIsolation());
           }
            connection.setAutoCommit(false);
            connection2.setAutoCommit(false);
            statement = connection.createStatement();
            statement2 = connection2.createStatement();
            statement.executeUpdate("INSERT INTO survey Values( 1, 'foo')");
            statement.executeUpdate("INSERT INTO survey2 Values( 2, 'bar')");
            rs1 = statement.executeQuery("SELECT *FROM survey");
            outputResultSet(rs1);
           // connection.commit();
            statement2.executeUpdate("INSERT INTO survey Values( 2, 'bar')");
            rs2 = statement2.executeQuery("SELECT *FROM survey");
            outputResultSet(rs2);
            connection2.commit();
            connection.commit();


         } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();

            try {
                //An error occured so we rollback the changes.
                connection.rollback();
                connection2.rollback();
            } catch (SQLException ex1) {
                ex1.printStackTrace();
            }
        } finally {
            try {
                if(rs1 != null)
                    rs1.close();
                if (statement != null)
                    statement.close();
                if (connection != null)
                    connection.close();
                if(rs2 != null)
                    rs2.close();
                if(statement2 != null)
                    statement2.close();
                if(connection2 != null)
                    connection2.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
  }
  public static void main(String[] args) throws Exception{


        new Main().updateDatabaseWithTransaction();

  }