Monday, May 23, 2011

Dirty Read,Phantom Read and Non Repeatable Read

What is Dirty Read?

Dirty read occurs wherein one transaction is changing the tuple/record, and a second transaction can read this tuple/record before the original change has been committed or rolled back. This is known as a dirty read scenario because there is always the possibility that the first transaction may rollback the change, resulting in the second transaction having read an invalid value. 

To understand it better,lets take a use case where on thread is viewing the record and other thread is updating the value of the record.Since the transaction isolation attribute is set to READ UNCOMMITTED.Second thread will be able to see the changes made by other thread.

Example of Dirty Read:-

In the current use case we have a table containing account balances.
One thread is reading the data from the account balances table.Other thread is updating the data from that table.Since isolation attribute is 'READ UNCOMMITTED' .Other thread can view non committed data.

Table Definition:-
Create table AccountBalance
(
      id integer Primary Key,
      acctName varchar2(255) not null,
      acctBalance integer(9,2) not null,
      bankName varcahr2(255)
);

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



ReaderRunImpl.java

package com.kunaal.pooling;

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

/**
 * Reader thread which selects the data while other thread is updating the 
 * data 
 * 
 * @author Kunaal A Trehan
 *
 */
public class ReaderRunImpl  implements Runnable{

 private Connection conn;
 
 private static final String QUERY="Select balance 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);
   rs = stmt.executeQuery();
   while (rs.next()){
    System.out.println("Balance is:" + rs.getDouble(1));
    
   }
  } catch (SQLException e) {
   e.printStackTrace();
  }finally{
   try {
    stmt.close();
    rs.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }   
  }
 }
 
}

PaymentRunImpl.java
package com.kunaal.pooling;

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

/**
 * @author Kunaal A Trehan
 *
 */
public class PaymentRunImpl implements Runnable{

 private Connection conn;
 
 private static final String QUERY="Update AccountBalance set balance=26000 where id=1";
 
 public PaymentRunImpl(Connection conn){
  this.conn=conn;
 }
 
 @Override
 public void run() {
  PreparedStatement stmt = null;
  
  try {   
   stmt = conn.prepareStatement(QUERY);
   stmt.execute();
   Thread.currentThread().sleep(3000);
   conn.rollback();
  } catch (SQLException e) {
   e.printStackTrace();
  } catch (InterruptedException e) {    
   e.printStackTrace();
  }finally{
   try {
    stmt.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }
 
}


DirtyReadExample.java
package com.kunaal.pooling;

import java.sql.Connection;
import java.sql.SQLException;

/**
 * @author Kunaal A Trehan
 *
 */
public class DirtyReadExample {

 /**
  * @param args
  * @throws SQLException 
  * @throws InterruptedException 
  */
 public static void main(String[] args) {
  ConnectionPool pool=new ConnectionPool(5,1000);
  
  Connection connPymt = pool.getConnection();
  Connection connReader = pool.getConnection();
  try {
   connPymt.setAutoCommit(false);
   connPymt.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
   
   connReader.setAutoCommit(false);
   //connReader.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
   connReader.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
  } catch (SQLException e) {
   e.printStackTrace();
  }
  
  
  Thread pymtThread=new Thread(new PaymentRunImpl(connPymt));
  Thread readerThread=new Thread(new ReaderRunImpl(connReader));
  
  pymtThread.start();
  try {
   Thread.currentThread().sleep(1000);
  } catch (InterruptedException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  readerThread.start();
  
  pool.returnConnection(connPymt);
  pool.returnConnection(connReader);
 }
 
}

Here reader thread views the account balance when payment thread is sleeping.So reader thread will view the balance as 24000 if the isolation level is Connection.TRANSACTION_READ_UNCOMMITTED as other transaction has roll back the transaction.

What is Phantom Read?

Phantom read occurs where in a transaction same query executes twice, and the second result set includes rows that weren't visible in the first result set. This situation is caused by another transaction inserting new rows between the execution of the two queries.

Example of Phantom Read:-

To understand it better consider  a use case where one thread is inserting the data while other thread is reading the data in different transaction.Since reader thread has isolation attribute as READ COMMITTED,reader thread will see the new rows inserted when it queries again.

Table definition:-
Create table Employee
(
      id integer Primary Key,
      empName varchar2(255) not null,
      empCity varchar2(255) not null,
      empCtry varchar2(255)not null
);


PhantomReader.java

package com.kunaal.pooling;

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

/**
 * @author Kunaal A Trehan
 *
 */
public class PhantomReader implements Runnable{

 private Connection conn;
 
 private static final String QUERY="Select * from Employee";
 
 public PhantomReader(Connection conn){
  this.conn=conn;
 }
 
 @Override
 public void run() {
  PreparedStatement stmt =null;
  ResultSet rs=null;
  
  try {
   stmt=conn.prepareStatement(QUERY);
   rs=stmt.executeQuery();
   
   while(rs.next()){
    System.out.println("Emp Details- "+ rs.getInt(1) + "-"+ rs.getString(2) + "-"+
        rs.getString(3)+ "-"+ rs.getString(4));
   }
   
   Thread.currentThread().sleep(3000);
   System.out.println("AFTER WAKING UP");
   System.out.println("===============================================");
   
   rs=stmt.executeQuery();
   
   while(rs.next()){
    System.out.println("Emp Details- "+ rs.getInt(1) + "-"+ rs.getString(2) + "-"+
        rs.getString(3)+ "-"+ rs.getString(4));
   }
  } catch (SQLException e) {
   e.printStackTrace();
  } catch (InterruptedException e) {
   e.printStackTrace();
  }finally{
   try {
    rs.close();
    stmt.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }

}


PhantomInsert.java

package com.kunaal.pooling;

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

/**
 * @author Kunaal A Trehan
 *
 */
public class PhantomInsert implements Runnable{

 private Connection conn;
 
 private static final String QUERY="Insert into Employee values(?,?,?,?)";
 
 public PhantomInsert(Connection conn){
  this.conn=conn;
 }
 
 @Override
 public void run() {
  PreparedStatement stmt =null;
  
  try {
   stmt = conn.prepareStatement(QUERY);
   stmt.setInt(1, 3);
   stmt.setString(2, "ABC");
   stmt.setString(3,"DELHI");
   stmt.setString(4, "INDIA");
   
   stmt.execute();
   conn.commit();
  } catch (SQLException e) {
   e.printStackTrace();
  }finally{
   try {
    stmt.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
  
 }

}


PhantomReadExample.java

package com.kunaal.pooling;

import java.sql.Connection;
import java.sql.SQLException;

/**
 * @author Kunaal A Trehan
 *
 */
public class PhantomReadExample {

 /**
  * @param args
  */
 public static void main(String[] args) {
  ConnectionPool pool=new ConnectionPool(5,1000);
  
  Connection connInsert = pool.getConnection();
  Connection connReader = pool.getConnection();
  try {
   connInsert.setAutoCommit(false);
   connInsert.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
   
   connReader.setAutoCommit(false);
   //connInsert.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
   connReader.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
  } catch (SQLException e) {
   e.printStackTrace();
  }
  
  Thread readThread=new Thread(new PhantomReader(connReader));
  Thread insertThread=new Thread(new PhantomInsert(connInsert));
  readThread.start();
  insertThread.start();
  
  pool.returnConnection(connReader);
  pool.returnConnection(connInsert);

 }

}

Here reader thread with isolation level as READ COMMITTED queries the employee table twice while other thread is inserting the data.As a result of which number of rows returned is different.

What is Non Repeatable Read?





Non Repeatable Reads happen when in a same transaction same query yields different results. This happens when another transaction updates the data returned by other transaction.

Example of Non Repeatable Read:-

To understand it better lets take a use case where one thread is viewing the data and other thread is updating the data.Since isolation level is READ COMMITED,other thread will be able to view the updated changes.So in the same transaction,same query will yield different data.



Updater.java

package com.kunaal.pooling;

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

/**
 * @author Kunaal A Trehan
 *
 */
public class Updater implements Runnable{

private Connection conn;
 
 private static final String QUERY="Update Employee set empCountry='USA'";
 
 public Updater(Connection conn){
  this.conn=conn;
 }
 
 @Override
 public void run() {
  PreparedStatement stmt =null; 
  
  try {
   stmt = conn.prepareStatement(QUERY);
   stmt.executeUpdate();
   conn.commit();   
  } catch (SQLException e) {
   e.printStackTrace();
  }finally{
   try {
    stmt.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }   
  }
  
 }

}


NonRepeatableReader.java

package com.kunaal.pooling;

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

/**
 * @author Kunaal A Trehan
 *
 */
public class NonRepeatableReader implements Runnable{

 private Connection conn;
 
 private static final String QUERY="Select * from Employee";
 
 public NonRepeatableReader (Connection conn){
  this.conn=conn;
 }
 
 @Override
 public void run() {
  PreparedStatement stmt =null;
  ResultSet rs=null;
  
  try {
   stmt=conn.prepareStatement(QUERY);
   rs=stmt.executeQuery();
   
   while(rs.next()){
    System.out.println("Emp Details- "+ rs.getInt(1) + "-"+ rs.getString(2) + "-"+
        rs.getString(3)+ "-"+ rs.getString(4));
   }
   
   Thread.currentThread().sleep(3000);
   System.out.println("AFTER WAKING UP");
   System.out.println("===============================================");
   
   rs=stmt.executeQuery();
   
   while(rs.next()){
    System.out.println("Emp Details- "+ rs.getInt(1) + "-"+ rs.getString(2) + "-"+
        rs.getString(3)+ "-"+ rs.getString(4));
   }
  } catch (SQLException e) {
   e.printStackTrace();
  } catch (InterruptedException e) {
   e.printStackTrace();
  }finally{
   try {
    rs.close();
    stmt.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }

}

NonRepeatbleExample.java

package com.kunaal.pooling;

import java.sql.Connection;
import java.sql.SQLException;

/**
 * @author Kunaal A Trehan
 *
 */
public class NonRepeatbleExample {

 /**
  * @param args
  */
 public static void main(String[] args) {
  ConnectionPool pool=new ConnectionPool(5,1000);
  
  Connection connUpdt = pool.getConnection();
  Connection connReader = pool.getConnection();
  try {
   connUpdt.setAutoCommit(false);
   connUpdt.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
   
   connReader.setAutoCommit(false);
   //connReader.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
   connReader.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
  } catch (SQLException e) {
   e.printStackTrace();
  }
  
  
  Thread updtThread=new Thread(new Updater(connUpdt));
  Thread readerThread=new Thread(new NonRepeatableReader(connReader));
  
  readerThread.start();
  try {
   Thread.currentThread().sleep(1000);
  } catch (InterruptedException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  updtThread.start();
  
  pool.returnConnection(connUpdt);
  pool.returnConnection(connReader);
 }

}



Here while reading same query will yield different results as other thread has updated the data.

Following table depicts the isolation level mapping with dirty read ,phantom read and others.

















22 comments:

  1. Where is this ConnectionPool class came from?
    Is this something that you designed?

    I could not able to find any libraries for this class.

    ReplyDelete
  2. thanks, very good information, I wasn't sure about NR read VS phantom read, now I am.

    ReplyDelete
  3. thanks, cleared the conception of Isolation level in transaction.

    ReplyDelete
  4. In example of Phantom read you can change isolation level to REPEATABLE_READ, so Phantom read problem remain.
    But when you start programm you will see that there is no Phantom record! Im confused why does it happen

    ReplyDelete
  5. nice explanation

    ReplyDelete
  6. There were a number of small errors which made understanding of the topic a little more difficult. However, the blog was a good attempt to describe the topic. This is especially true as there are very few descriptions of the Java code which is necessary.
    Thank you.

    Ron Wates

    ReplyDelete
  7. This is an awesome post. Really very informative and creative contents. This concept is a good way to enhance the knowledge. Like it and help me to development very well Thank you for this brief explanation and very nice information. Well got good knowledge.
    Java Training in Gurgaon

    ReplyDelete
  8. We have applied many of this stuff in Our institute. Informative stuff. Check this @ Java Training in Chandigarh

    ReplyDelete
  9. please correct balance as 26000 and not 24000 in below sentence.
    Here reader thread views the account balance when payment thread is sleeping.So reader thread will view the balance as 24000 if the isolation level is Connection.TRANSACTION_READ_UNCOMMITTED as other transaction has roll back the transaction.

    ReplyDelete
  10. Very Informative! This blog is great source of information which is very useful for me. Thank you very much for sharing this!

    Best Web Design Company
    Web Development Company
    Digital Marketing Company

    ReplyDelete
  11. This is an awesome post. Really very informative and creative contents. This concept is a good way to enhance the knowledge. Like it and help me to development very well Thank you for this brief explanation and very nice information..
    angular js training in chennai

    angular js training in omr

    full stack training in chennai

    full stack training in omr

    php training in chennai

    php training in omr

    photoshop training in chennai

    photoshop training in omr

    ReplyDelete
  12. Good Post! it was so good to read and useful to improve my knowledge as an updated one, keep blogging. After seeing your article, I want to say that also a well-written article with some very good information which is very useful for the AWS Online Training

    ReplyDelete
  13. Thanks for sharing information to our knowledge, it helps me plenty keep sharing…

    Python Training Institute in Pune
    Best Python Classes in Pune

    ReplyDelete