Geek Answers Handbook

MYSQL / JDBC Java query returns stale data from a cached connection

I searched Stackoverflow for an answer, but cannot find one that is not related to Hibernate or any other database shell.

I use JDBC directly through the JSBC MYSQL 5.18 driver in the Java EE Tomcat 6 application. I cache Connection objects, but not cache Statement objects. ResultSets for the request correctly return updated data on first start. When I modify a few lines through PHPMyAdmin or some other external tool, retry the request, I get outdated obsolete data.

I use regular expressions, not PreparedStatements. I tried ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE. I also close the result set. This does not solve the problem. I also tried ResultSet.refreshRows (), but this leads to an error because the request has a JOIN clause.

The only thing that clearly solves the problem is to close the connection and reconnect to the database, which leads to a high cost for each request attempt.

Is there a way to reuse connections without returning obsolete data?

EDIT: I do not use transactions for queries at the moment.

Here is the common 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!

+5
source share
4 answers

, . Brent Worden , , .

, , :

conn.setAutoCommit(true);


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

.

+9

, .

connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

+3

mysql: ENGINE = InnoDB, tx_isolation = REPEATABLE_READ

spring.xml

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

, !

mysql tx_isolation = READ_COMMITTED .

+1

JDBC, Apache DBUtils. , . http://commons.apache.org/dbutils

0
source

More articles:


All Articles