MySQL and JDBC - Updates by One Connection Object Not Visible Through Another?
Stumbled upon this while developing a multi-threaded Java application that connects to MySQL via JDBC. Basically, i have one thread updating a database table through a Connection (java.sql.Connection) object (connection1), and another querying the same table through a different connection object (connection2). What i found was that, after connection2 does its first query against the table, subsequent similar queries (by connection2) always return the same result (as the very first one) even though in between, connection1 has executed several updates against that table.
i wrote out a simple single-threaded test application to examine this and to ensure that it was not due to some other application bugs or an oversight:import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestMySqlDatabase
{
private static final String CLASSNAME = "com.mysql.jdbc.Driver";
// Database URL
private static final String URL
= "jdbc:mysql://192.168.1.1:3306/test";
// Database User Name
private static final String USERNAME = "root";
// Database User Password
private static final String PASSWORD = "password";
public static final void main(String[] args)
throws ClassNotFoundException, SQLException
{
// connection0 is used to CREATE and DROP
// the test table
Connection connection0 = null;
// connection1 is used to update the test table
Connection connection1 = null;
// connection2 is used to query the test table
Connection connection2 = null;
Statement statement = null;
ResultSet resultSet = null;
try
{
Class.forName(CLASSNAME);
// CREATE the test table
try
{
connection0
= DriverManager.getConnection(
URL, USERNAME, PASSWORD);
connection0.setAutoCommit(false);
statement = connection0.createStatement();
statement.executeUpdate(
"CREATE TABLE TestTable "
+ "(TestColumn VARCHAR(1))");
connection0.commit();
}
finally
{
if (statement != null)
{
try
{
statement.close();
}
catch (SQLException ignore)
{
//
}
}
if (connection0 != null)
{
try
{
connection0.close();
}
catch (SQLException ignore)
{
//
}
}
}
connection1
= DriverManager.getConnection(
URL, USERNAME, PASSWORD);
connection1.setAutoCommit(false);
connection2
= DriverManager.getConnection(
URL, USERNAME, PASSWORD);
connection2.setAutoCommit(false);
// Add one row to the test table
try
{
statement = connection1.createStatement();
statement.executeUpdate(
"INSERT INTO TestTable "
+ "(TestColumn) VALUES ('a')");
connection1.commit();
}
finally
{
if (statement != null)
{
try
{
statement.close();
}
catch (SQLException ignore)
{
//
}
}
}
// Query the test table and print result(s)
try
{
statement = connection2.createStatement();
resultSet
= statement.executeQuery(
"SELECT * FROM testtable");
System.out.println("Query 1:");
while (resultSet.next())
{
System.out.println(
resultSet.getString("TestColumn"));
}
}
finally
{
if (resultSet != null)
{
try
{
resultSet.close();
}
catch (SQLException ignore)
{
//
}
}
if (statement != null)
{
try
{
statement.close();
}
catch (SQLException ignore)
{
//
}
}
}
// Add a second row to the test table
try
{
statement = connection1.createStatement();
statement.executeUpdate(
"INSERT INTO TestTable "
+ "(TestColumn) VALUES ('b')");
connection1.commit();
}
finally
{
if (statement != null)
{
try
{
statement.close();
}
catch (SQLException ignore)
{
//
}
}
}
// Query the test table and print result(s) again
try
{
statement = connection2.createStatement();
resultSet
= statement.executeQuery(
"SELECT * FROM testtable");
System.out.println("Query 2:");
while (resultSet.next())
{
System.out.println(
resultSet.getString("TestColumn"));
}
}
finally
{
if (resultSet != null)
{
try
{
resultSet.close();
}
catch (SQLException ignore)
{
//
}
}
if (statement != null)
{
try
{
statement.close();
}
catch (SQLException ignore)
{
//
}
}
}
// DROP the test table
try
{
connection0
= DriverManager.getConnection(
URL, USERNAME, PASSWORD);
connection0.setAutoCommit(false);
statement = connection0.createStatement();
statement.executeUpdate("DROP TABLE TestTable");
connection0.commit();
}
finally
{
if (statement != null)
{
try
{
statement.close();
}
catch (SQLException ignore)
{
//
}
}
if (connection0 != null)
{
try
{
connection0.close();
}
catch (SQLException ignore)
{
//
}
}
}
}
finally
{
if (connection1 != null)
{
try
{
connection1.close();
}
catch (SQLException ignore)
{
//
}
}
if (connection2 != null)
{
try
{
connection2.close();
}
catch (SQLException ignore)
{
//
}
}
}
}
}
The output of this test application turned out to be:Query 1:
a
Query 2:
a
i.e. The results of the first and second queries were the same, even though a second record was inserted before the second query was performed.
This led me to do some online searching, and i found that the answer to this lies with the transaction isolation level setting. The default setting for MySQL is REPEATABLE READ, and this means that within a transaction, identical queries will return identical results. Because connection2 (above) had auto-commit set to false, and there was no commit (on connection2) executed in between its two query invocations, those two queries are considered to be within a single transaction. Hence, identical results for the two identical queries.
The few common transaction isolation level values are explained in detail in this article, so do give that a read as well.
Finally, the transaction isolation level setting for MySQL can be modified by changing the tx_isolation system variable using the MySQL Administrator.
