7

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!

Arjan Tijms
  • 37,782
  • 12
  • 108
  • 140
ricosrealm
  • 1,616
  • 1
  • 16
  • 26
  • 2
    Are you using transactions? If so what is the isolation level? It seems a little like REPEATABLE READ behavior. – Brent Worden Feb 17 '12 at 04:25
  • Good question. No transactions for these queries. I do use transactions for the updates/inserts in general, but in this particular case I'm not executing any within the app itself right now. I'm doing the UPDATEs via an external tool which may be throwing things off. If you recommend transactions as a solution to prevent dirty reads, please share an answer. Also, I haven't set any isolation level on the connection. Would that be the problem? – ricosrealm Feb 17 '12 at 05:05

4 Answers4

11

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.

ricosrealm
  • 1,616
  • 1
  • 16
  • 26
3

Set the transaction isolation level as below.

conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

Gurushanth
  • 31
  • 4
1

my mysql installation: ENGINE=InnoDB, default tx_isolation=REPEATABLE_READ

spring.xml

<tx:method name="find*"  propagation="SUPPORTS" read-only="true" timeout="600" />

if use pooled connection which will always return same results!

change mysql tx_isolation=READ_COMMITTED resolved my problem.

Chonan
  • 11
  • 1
0

Why don'y you use JDBC pools using Apache DBUtils. It allows you to use the same connection and also control the connections size. The link is http://commons.apache.org/dbutils

Sabyrzhan
  • 175
  • 2
  • 16
  • Thanks for the pointer. I can try and take a look at this code. However I'm trying to do something very simple here without a larger code wrapper. – ricosrealm Feb 17 '12 at 04:15