CODEWITHSUNDEEP
javamysqljdbctransactionsisolation-level
Adelin
Adelin

Reputation: 19011

Unable to produce a phantom read

I am trying to produce a phantom read, for the sake of learning but unfortunately I am unable to. I am using Java threads, JDBC, MySQL. Here is the program I am using: 

package com.isolation.levels.phenomensa;

import javax.xml.transform.Result;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.concurrent.CountDownLatch;

import static com.isolation.levels.ConnectionsProvider.getConnection;
import static com.isolation.levels.Utils.printResultSet;

/**
 * Created by dreambig on 13.03.17.
 */
public class PhantomReads {


    public static void main(String[] args) {


        setUp(getConnection());// delete the newly inserted row, the is supposed to be a phantom row
        CountDownLatch countDownLatch1 = new CountDownLatch(1);  // use to synchronize threads steps
        CountDownLatch countDownLatch2 = new CountDownLatch(1);  // use to synchronize threads steps

        Transaction1 transaction1 = new Transaction1(countDownLatch1, countDownLatch2, getConnection()); // the first runnable
        Transaction2 transaction2 = new Transaction2(countDownLatch1, countDownLatch2, getConnection()); // the second runnable

        Thread thread1 = new Thread(transaction1); // transaction 1
        Thread thread2 = new Thread(transaction2); // transaction 2

        thread1.start();
        thread2.start();

    }

    private static void setUp(Connection connection) {

        try {
            connection.prepareStatement("DELETE from actor where last_name=\"PHANTOM_READ\"").execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public static class Transaction1 implements Runnable {


        private CountDownLatch countDownLatch;
        private CountDownLatch countDownLatch2;
        private Connection connection;


        public Transaction1(CountDownLatch countDownLatch, CountDownLatch countDownLatch2, Connection connection) {
            this.countDownLatch = countDownLatch;
            this.countDownLatch2 = countDownLatch2;
            this.connection = connection;
        }

        @Override
        public void run() {


            try {

                String query = "select * from actor where first_name=\"BELA\"";

                connection.setAutoCommit(false); // start the transaction

                // the transaction isolation, dirty reads and non-repeatable reads are prevented !
                // only phantom reads can occure
                connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);

                //read the query result for the first time.
                ResultSet resultSet = connection.prepareStatement(query).executeQuery();
                printResultSet(resultSet);                 // print result.

                //count down so that thread2 can insert a row and commit.
                countDownLatch2.countDown();
                //wait for the second query the finish inserting the row
                countDownLatch.await();

                System.out.println("\n ********* The query returns a second row satisfies it (a phantom read) ********* !");
                //query the result again ... 
                ResultSet secondRead = connection.createStatement().executeQuery(query);

                printResultSet(secondRead);  //print the result

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

        }
    }


    public static class Transaction2 implements Runnable {


        private CountDownLatch countDownLatch;
        private CountDownLatch countDownLatch2;
        private Connection connection;


        public Transaction2(CountDownLatch countDownLatch, CountDownLatch countDownLatch2, Connection connection) {
            this.countDownLatch = countDownLatch;
            this.countDownLatch2 = countDownLatch2;
            this.connection = connection;
        }


        @Override
        public void run() {

            try {
                //wait the first thread to read the result 
                countDownLatch2.await();

                //insert and commit ! 
                connection.prepareStatement("INSERT INTO actor (first_name,last_name) VALUE (\"BELA\",\"PHANTOM_READ\") ").execute();
                //count down so that the thread1 can read the result again ... 
                countDownLatch.countDown();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (InterruptedException e) {
                e.printStackTrace();
            }

        }


    }


}

However this is actually the result

----------------------------------------------------------
 | 196 |  | BELA |  | WALKEN |  | 2006-02-15 04:34:33.0 | 
---------------------------------------------------------- The query returns a second row satisfies it (a phantom read)
----------------------------------------------------------
 | 196 |  | BELA |  | WALKEN |  | 2006-02-15 04:34:33.0 | 
----------------------------------------------------------

But I think it should be 

----------------------------------------------------------
 | 196 |  | BELA |  | WALKEN |  | 2006-02-15 04:34:33.0 | 
---------------------------------------------------------- The query returns a second row satisfies it (a phantom read)  !
----------------------------------------------------------
 | 196 |  | BELA |  | WALKEN |  | 2006-02-15 04:34:33.0 | 
----------------------------------------------------------
----------------------------------------------------------
 | 196 |  | BELA |  | PHANTOM_READ |  | 2006-02-15 04:34:33.0 | 
----------------------------------------------------------

I am using: Java 8 JDBC MySQL InnoDB SakilaDB insert into mysql

Upvotes: 1

Views: 297

Answers (1)

Solarflare
Solarflare

Reputation: 11116

A phantom read is the following scenario: a transaction reads a set of rows that satisfy a search condition. Then a second transaction inserts a row that satisfies this search condition. Then the first transaction reads again the set of rows that satisfy a search condition, and gets a different set of rows (e.g. including the newly inserted row).

Repeatable read requires that if a transaction reads a row, a different transaction then updates or deletes this row and commits these changes, and the first transaction rereads the row, it will get the same constistent values as before (a snapshot).

It actually doesn't require that phantom reads have to happen. MySQL will actually prevent phantom reads in more cases than it has to. In MySQL, phantom reads (currently) only happen after you (accidently) updated a phantom row, otherwise the row stays hidden. This is specific to MySQL, other database system will behave differently. Also, this behaviour might change some day (as MySQL only specifies that it supports consistent reads as required by the sql standard, not under which specific circumstances phantom reads occur).

You can use for example the following steps to get phantom rows: 

insert into actor (first_name,last_name) values ('ADELIN','NO_PHANTOM');

transaction 1:
select * from actor;
-- ADELIN|NO_PHANTOM

transaction 2:
insert into actor (first_name,last_name) values ('BELA','PHANTOM_READ');
commit;

transaction 1:
select * from actor;  -- still the same
-- ADELIN|NO_PHANTOM

update actor set last_name = 'PHANTOM READ'
where last_name = 'PHANTOM_READ';

select * from actor;  -- now includes the new, updated row
-- ADELIN|NO_PHANTOM
-- BELA  |PHANTOM READ

Another funny thing happens btw when you delete rows:

insert into actor (first_name,last_name) values ('ADELIN','NO_PHANTOM');
insert into actor (first_name,last_name) values ('BELA','REPEATABLE_READ');

transaction 1:
select * from actor; 
-- ADELIN|NO_PHANTOM
-- BELA  |REPEATABLE_READ

transaction 2:
delete from actor where last_name = 'REPEATABLE_READ';
commit;

transaction 1:      
select * from actor;  -- still the same 
-- ADELIN|NO_PHANTOM
-- BELA  |REPEATABLE_READ

update actor set last_name = '';

select * from actor;  -- the deleted row stays unchanged
-- ADELIN|
-- BELA  |REPEATABLE_READ

This is exactly what the sql standard requires: if you reread a (deleted) row, you will get the original value.

Upvotes: 2

Related Questions