transaction isolation problem or wrong approach?

Question

I was helping out some colleagues of mine with an SQL problem. Mainly they wanted to move all the rows from table A to table B (both tables having the same columns (names and types)). Although this was done in Oracle 11g I don't think it really matters.

Their initial naive implementation was something like

BEGIN
  INSERT INTO B SELECT * FROM A
  DELETE FROM A
  COMMIT;
END

Their concern was if there were INSERTs made to table A during copying from A to B and the "DELETE FROM A" (or TRUNCATE for what was worth) would cause data loss (having the newer inserted rows in A deleted).

Ofcourse I quickly recommended storing the IDs of the copied rows in a temporary table and then deleting just the rows in A that matched the IDS in the temporary table.

However for curiosity's sake we put up a little test by adding a wait command (don't remember the PL/SQL syntax) between INSERT and DELETE. THen from a different connection we would insert rows DURING THE WAIT.

We observed that was a data loss by doing so. I reproduced the whole context in SQL Server and wrapped it all in a transaction but still the fresh new data was lost too in SQL Server. This made me think there is a systematic error/flaw in the initial approach.

However I can't tell if it was the fact that the TRANSACTION was not (somehow?) isolated from the fresh new INSERTs or the fact that the INSERTs came during the WAIT command.

In the end it was implemented using the temporary table suggested by me but we couldn't get the answer to "Why the data loss". Do you know why?

Answer 1

    I have written a sample code:-

    First run this on Oracle DB:-


     Create table AccountBalance
        (
              id integer Primary Key,
              acctName varchar2(255) not null,
              acctBalance integer not null,
              bankName varchar2(255) not null
        );

        insert into AccountBalance values (1,'Test',50000,'Bank-a');

    Now run the below code 





 package com.java.transaction.dirtyread;
        import java.sql.Connection;
        import java.sql.DriverManager;
        import java.sql.SQLException;

        public class DirtyReadExample {

         /**
          * @param args
         * @throws ClassNotFoundException 
          * @throws SQLException 
          * @throws InterruptedException 
          */
         public static void main(String[] args) throws ClassNotFoundException, SQLException, InterruptedException {

             Class.forName("oracle.jdbc.driver.OracleDriver");
             Connection connectionPayment = DriverManager.getConnection(
                        "jdbc:oracle:thin:@localhost:1521:xe", "hr",
                        "hr");
             Connection connectionReader = DriverManager.getConnection(
                        "jdbc:oracle:thin:@localhost:1521:xe", "hr",
                        "hr");

          try {
              connectionPayment.setAutoCommit(false);
              connectionPayment.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);


          } catch (SQLException e) {
           e.printStackTrace();
          }


          Thread pymtThread=new Thread(new PaymentRunImpl(connectionPayment));
          Thread readerThread=new Thread(new ReaderRunImpl(connectionReader));

          pymtThread.start();
          Thread.sleep(2000);
          readerThread.start();

         }

        }



        package com.java.transaction.dirtyread;

        import java.sql.Connection;
        import java.sql.PreparedStatement;
        import java.sql.ResultSet;
        import java.sql.SQLException;

        public class ReaderRunImpl  implements Runnable{

         private Connection conn;

         private static final String QUERY="Select acctBalance from AccountBalance where id=1";

         public ReaderRunImpl(Connection conn){
          this.conn=conn;
         }

         @Override
         public void run() {
          PreparedStatement stmt =null; 
          ResultSet rs =null;

          try {
           stmt = conn.prepareStatement(QUERY);
           System.out.println("In Reader thread --->Statement Prepared");
           rs = stmt.executeQuery();
           System.out.println("In Reader thread --->executing");
           while (rs.next()){

            System.out.println("Balance is:" + rs.getDouble(1));

           }
           System.out.println("In Reader thread --->Statement Prepared");
           Thread.sleep(5000);
           stmt.close();
           rs.close();
           stmt = conn.prepareStatement(QUERY);
           rs = stmt.executeQuery();
           System.out.println("In Reader thread --->executing");
           while (rs.next()){

            System.out.println("Balance is:" + rs.getDouble(1));

           }
           stmt.close();
           rs.close();
           stmt = conn.prepareStatement(QUERY);
           rs = stmt.executeQuery();
           System.out.println("In Reader thread --->executing");
           while (rs.next()){

            System.out.println("Balance is:" + rs.getDouble(1));

           }
          } catch (SQLException | InterruptedException e) {
           e.printStackTrace();
          }finally{
           try {
            stmt.close();
            rs.close();
           } catch (SQLException e) {
            e.printStackTrace();
           }   
          }
         }

        }

        package com.java.transaction.dirtyread;
        import java.sql.Connection;
        import java.sql.PreparedStatement;
        import java.sql.SQLException;

        public class PaymentRunImpl implements Runnable{

         private Connection conn;

         private static final String QUERY1="Update AccountBalance set acctBalance=40000 where id=1";
         private static final String QUERY2="Update AccountBalance set acctBalance=30000 where id=1";
         private static final String QUERY3="Update AccountBalance set acctBalance=20000 where id=1";
         private static final String QUERY4="Update AccountBalance set acctBalance=10000 where id=1";

         public PaymentRunImpl(Connection conn){
          this.conn=conn;
         }

         @Override
         public void run() {
          PreparedStatement stmt = null;

          try {   
           stmt = conn.prepareStatement(QUERY1);
           stmt.execute();
           System.out.println("In Payment thread --> executed");
           Thread.sleep(3000);
           stmt = conn.prepareStatement(QUERY2);
           stmt.execute();
           System.out.println("In Payment thread --> executed");
           Thread.sleep(3000);
           stmt = conn.prepareStatement(QUERY3);
           stmt.execute();
           System.out.println("In Payment thread --> executed");
           stmt = conn.prepareStatement(QUERY4);
           stmt.execute();
           System.out.println("In Payment thread --> executed");

           Thread.sleep(5000);
            //case 1
           conn.rollback();
           System.out.println("In Payment thread --> rollback");
          //case 2
           //conn.commit();
          // System.out.println("In Payment thread --> commit");
          } catch (SQLException e) {
           e.printStackTrace();
          } catch (InterruptedException e) {    
           e.printStackTrace();
          }finally{
           try {
            stmt.close();
           } catch (SQLException e) {
            e.printStackTrace();
           }
          }
         }

        }

    Output:-
    In Payment thread --> executed
    In Reader thread --->Statement Prepared
    In Reader thread --->executing
    Balance is:50000.0
    In Reader thread --->Statement Prepared
    In Payment thread --> executed
    In Payment thread --> executed
    In Payment thread --> executed
    In Reader thread --->executing
    Balance is:50000.0
    In Reader thread --->executing
    Balance is:50000.0
    In Payment thread --> rollback

U can test it by inserting new rows as defined by oracle:- A phantom read occurs when transaction A retrieves a set of rows satisfying a given condition, transaction B subsequently inserts or updates a row such that the row now meets the condition in transaction A, and transaction A later repeats the conditional retrieval. Transaction A now sees an additional row. This row is referred to as a phantom. It will avoid the above scenario as well as I have used TRANSACTION_SERIALIZABLE. It will set the most strict lock on the Oracle. Oracle only supports 2 type of transaction isolation levels:- TRANSACTION_READ_COMMITTED and TRANSACTION_SERIALIZABLE.

Answer 2

Alternatively, you can use snapshot isolation to detect lost updates:

When Snapshot Isolation Helps and When It Hurts

Answer 3

I can't speak to the transaction stability, but an alternate approach would be to have the second step delete from the source table where exists (select ids from target table).

Forgive the syntax, I have not tested this code, but you should be able to get the idea:

INSERT INTO B SELECT * FROM A;

DELETE FROM A WHERE EXISTS (SELECT B.<primarykey> FROM B WHERE B.<primarykey> = A.<primarykey>);

That way you are using the relational engine to enforce that no newer data will be deleted, and you don't need to do the two steps in a transaction.

Update: corrected syntax in subquery

Answer 4

This is the standard behaviour of the default read-committed mode, as mentioned above. The WAIT command just causes a delay in processing, there's no link to any DB transaction handling.

To fix the problem you can either:

1. set the isolation level to serializable, but then you can get ORA- errors, which you need to handle with retries! Also, you may get a serious performance hit.
2. use a temp table to store the values first
3. if the data is not too large to fit into the memory, you can use a RETURNING clause to BULK COLLECT INTO a nested table and delete only if the row is present in the nested table.

Answer 5

In Oracle, the default transaction isolation level is read committed. That basically means that Oracle returns the results as they existed at the SCN (system change number) when your query started. Setting the transaction isolation level to serializable means that the SCN is captured at the start of the transaction so all the queries in your transaction return data as of that SCN. That ensures consistent results regardless of what other sessions and transactions are doing. On the other hand, there may be a cost in that Oracle may determine that it cannot serialize your transaction because of activity that other transactions are performing, so you would have to handle that sort of error.

Tony's link to the AskTom discussion goes in to substantially more detail about all this-- I highly recommend it.

Answer 6

Depending on your isolation level, selecting all the rows from a table does not prevent new inserts, it will just lock the rows you read. In SQL Server, if you use the Serializable isolation level then it will prevent new rows if they would have been including in your select query.

http://msdn.microsoft.com/en-us/library/ms173763.aspx -

SERIALIZABLE Specifies the following:

• Statements cannot read data that has been modified but not yet committed by other transactions.

• No other transactions can modify data that has been read by the current transaction until the current transaction completes.

• Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

Answer 7

Yes Milan, I haven't specified the transaction isolation level. I suppose it's the default isolation level which I don't know which it is. Neither in Oracle 11g nor in SQL Server 2005.

Furthermore the INSERT that was made during the WAIT command (on the 2nd connection) was NOT inside a transaction. Should have it been to prevent this data loss?

Answer 8

It's just the way transactions work. You have to pick the correct isolation level for the task at hand.

You're doing INSERT and DELETE in the same transaction. You don't mention the isolation mode transaction is using, but it's probably 'read committed'. This means that the DELETE command will see the records that were committed in the meantime. For this kind of job, it's much better to use 'snapshot' type of transaction, because then both INSERT and DELETE would know about the same set of records - only those and nothing else.

Answer 9

This can be achieved in Oracle using:

Alter session set isolation_level=serializable;

This can be set in PL/SQL using EXECUTE IMMEDIATE:

BEGIN
    EXECUTE IMMEDIATE 'Alter session set isolation_level=serializable';
    ...
END;

See Ask Tom: On Transaction Isolation Levels

Answer 10

You need to set your transaction isolation level so that the inserts from another transaction don't affect your transaction. I don't know how to do that in Oracle.

Answer 11

i don't know if this is relevant, but in SQL Server the syntax is

begin tran
....
commit

not just 'begin'