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.
9 comments:
It really is been said that males want intercourse, girls want really like.
Well, I am a lady and that i want both. Do males seek adore?
What exactly is intimate to guys?
Feel free to visit my blog Attract Beautiful Women
what kind of oil do you guys prefer me to use for the oil pulling?
my web page - zoom teeth whitening reviews uk
From > NAVY SEAL Physical Physical fitness Guide
my site; mike chang six pack shortcuts pdf download
Blog is completely fantastic! All excellent information can be helpful in some or the
other way. Hold updating the blog,looking forward for much more material.
Feel free to surf to my blog; arrow newport 8 ft x 6 ft steel shed np8667
Magnificent....I am able to always occur back again to your web site when I am overcomplicating and acquire my head smacked again to sanity
Also visit my webpage: mike chang monster mass
I comprehend what you are stating but maintain in mind there are many
ways you are able to get funding to your training with out likely straight to loans first,
also not all degrees are beneficial,? I meet individuals that function with me with AA,AS,BS
degrees in FedEx, i operate and go to school but this guys are carried out with college,
but note that they have useless degrees,
social science, liberal arts, Chicano studies, Etc... they actually regret gonna college now they may be spending a lot money.
but I understand what you are declaring
my webpage; premature ejaculation prevention pills
I coupon. But not so very. I only clip for what I'd purchase in any case. Sure, often that's processed,
but my entire cart isn't loaded up with it. And each once inside a whilst I am capable to locate a coupon for that wholesome things I purchase each and every trip in any case. It will save time whilst clipping and pressure at the store. Free of charge does not often equal good.
Also visit my web-site :: eat healthy food cartoon
I've 1 single question. Google apps for training is also obtainable? for Archives outdoors US?
my web page: Six Tips To Attract Women
Jody, you ARE a runner! Just not a marathon one!
:-)
Feel free to surf to my blog: neck muscles and headaches
Post a Comment