I have MySQL Server (5.7.16) running on my computer currently, and have this sample row in a database called 'Sakila' on this Server:
mysql> SELECT * FROM actor WHERE last_name = 'tugay';
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 201 | koray | tugay | 2017-06-11 21:42:08 |
+----------+------------+-----------+---------------------+
This is what JDBC returns for TRANSACTION ISOLATION LEVEL for my MySQL Server:
public static void main(String[] args) throws SQLException, InterruptedException {
final Connection connection = getConnection("jdbc:mysql://localhost:3306/sakila", "root", "root");
System.out.println(connection.getTransactionIsolation()); // Prints 4!
connection.close();
}
where 4 here is the constant for TRANSACTION_REPEATABLE_READ which is documented as follows:
int TRANSACTION_REPEATABLE_READ = 4;
A constant indicating that dirty reads are prevented.
This level prohibits a transaction from reading a row with uncommitted changes in it.
I want to see this behavior so I have 2 main methods, first being:
import java.sql.*;
import static java.sql.DriverManager.*;
public class Foo {
public static void main(String[] args) throws SQLException, InterruptedException {
final Connection connection = getConnection("jdbc:mysql://localhost:3306/sakila", "root", "root");
connection.setAutoCommit(false);
final Statement statement = connection.createStatement();
statement.executeUpdate("UPDATE actor SET first_name = 'ALTERED' WHERE first_name = 'koray'");
System.out.println("Sleeping!");
// Here I am expecting MySQL to lock the Row so no other Connection
// can read data from it. This row will be updated, so unless the transaction is
// either commited or rolled back, the read data will be 'dirty'.
Thread.sleep(5000);
connection.commit();
connection.close();
}
}
As you see, the transaction is not in auto-commit mode, and I am locking the row for 5 seconds. While this process is sleeping, I constantly run the following code as well:
public class Bar {
public static void main(String[] args) throws SQLException, InterruptedException {
final Connection connection = getConnection("jdbc:mysql://localhost:3306/sakila", "root", "root");
connection.setAutoCommit(false);
final Statement statement = connection.createStatement();
final ResultSet resultSet = statement.executeQuery("SELECT * FROM actor WHERE last_name = 'tugay'");
resultSet.next();
System.out.println(resultSet.getString("first_name"));
connection.close();
}
}
But even I see "sleeping" text in the console, as long as I run the main method in Bar, I will see values like
koray
koray
koray
ALTERED
My expectation was never to see koray since that is a "dirty data", where the first java process should be locking the row.
What am I missing and how can I lock this row?
