Java MYSQL/JDBC query is returning stale data from cached Connection

Question

I've been searching Stackoverflow for an answer but can't seem to find one that doesn't involve Hibernate or some other database wrapper.

I'm using JDBC directly via the MYSQL 5.18 JDBC driver in a Tomcat 6 Java EE app. I am caching Connection objects, but not caching Statement objects. The ResultSets for the query are correctly returning up to date data on the first run. When I change a few rows via PHPMyAdmin or some other external tool, rerun the query, I get stale out-of-date data.

I'm using normal Statements, not PreparedStatements. I've tried ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE. I'm also closing out the result set. These do not solve the problem. I've also tried ResultSet.refreshRows(), but that results in an error because the query has a JOIN clause.

The only thing that clearly solves the problem is closing the Connection and reconnecting to the database, which results in a heavy cost for each query attempt.

Is there a way to reuse Connections without returning stale data?

EDIT: I'm not using transactions for queries at the moment.

Here's the general code.

Connection conn; //created elsewhere and reused
...

String query = "SELECT p.ID as oid,rid,handle,summary,city,state,zip,t.name AS category     
                FROM profiles AS p
                JOIN (terms AS t) ON (p.tid = t.ID)
                WHERE p.ID = 1";

ResultSet resultSet;
Statement s;
synchronized (conn)
{                            
   s = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                           ResultSet.CONCUR_UPDATABLE);                        
   resultSet = s.executeQuery(query);
}

//Iterate over the results using .next() and copy data to another data structure   
List retval = getResults(resultSet);
s.close();

Thanks for the help in advance!

Answer 1

Turns out it was a matter of uncommited queries. Thanks to Brent Worden for the question about transactions which led me to look around and notice that I had disabled auto commit and was not committing after queries.

So the solutions that worked for me:

conn.setAutoCommit(true);

or

statement.executeQuery(query);
conn.commit();

This allows the queries to be flushed out and stale data is prevented.

Answer 2

Set the transaction isolation level as below.

conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);