CODEWITHSUNDEEP
javamysqljdbcisolation-level
Max Husiv
Max Husiv

Reputation: 315

Why 'Repeatable read' isolation level in java save me from 'Phantom reads'?

I've wrote code with 'phantom read' and my code should print different values if isolation level not serializable, but I have 'repeatable read' isolation level and it works like serializable. It shows me same digits, but should second time show bigger digit. Why so? I've MySql Database. Here my Example:

public class PhantomReadLesson {
static String url = "jdbc:mysql://localhost:3306/Lessons";
static String username = "root";
static String password = "1";
public static void main(String[] args) throws SQLException, InterruptedException {
    try(Connection conn = DriverManager.getConnection(url, username, password);
        Statement statement = conn.createStatement()) {
        conn.setAutoCommit(false);
        conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
        ResultSet rs = statement.executeQuery("Select count(*) from Books");
        while(rs.next()){
            System.out.println(rs.getInt(1));
        }
        new OtherTransaction2().start();
        Thread.currentThread().sleep(1000);
        rs = statement.executeQuery("Select count(*) from Books");
        while(rs.next()){
            System.out.println(rs.getString(1));
        }
    }
}

static class OtherTransaction2 extends Thread {
    @Override
    public void run() {
        try(Connection conn = DriverManager.getConnection(url, username, password);
            Statement stmt = conn.createStatement()) {
            conn.setAutoCommit(false);
            conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            stmt.executeUpdate("insert into Books (name) VALUES ('new Row')");
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
}

I'm imitating 'phantom reads' in here. If I use 'repeatable_read' or 'serializable' levels it show the same numbers, if use 'read_commmited' or 'read_uncomited' levels it will show different numbers. But according to java doc https://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html only serializable save from 'phantom reads'. So why repeatable read level save from 'phanotom read'?

Upvotes: 2

Views: 1592

Answers (1)

Andremoniy
Andremoniy

Reputation: 34900

From MySQL docs (about REPEATABLE READ):

Consistent reads within the same transaction read the snapshot established by the first read.

Consistent reads:

A read operation that uses snapshot information to present query results based on a point in time, regardless of changes performed by other transactions running at the same time.

As you set up auto-commit to false that means that both select's are performed in same transaction. So, what's your concerns? Looks like it works as it expected to be.

Notice also this remark:

Suppose that you are running in the default REPEATABLE READ isolation level. When you issue a consistent read (that is, an ordinary SELECT statement), InnoDB gives your transaction a timepoint according to which your query sees the database. If another transaction deletes a row and commits after your timepoint was assigned, you do not see the row as having been deleted. Inserts and updates are treated similarly.

Upvotes: 1

Related Questions