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.