Connection pool query results jumping between live and old data?

Question

I'm developing a Java SE application with a MySQL database. It uses a connection pool of size 10 as there are many screens. Each screen has a Thread that will update some JTables about every 10 seconds. My connection pooling code is taken from http://java.sun.com/developer/onlineTraining/Programming/JDCBook/conpool.html with a few methods added for my own convenience.

My problem is that when I edit some of the data in the application, and save it back to the database, the JTables will now randomly display either the new updated data, or the old original data, each time the Thread runs to update the screen. It flickers back and forth between new and old, each time the thread loops around.

I also have some objects that I load by clicking on a row in the JTable, and displaying their details in textBoxes. If I click on a row with data that is having the problem above, the object which is loaded, also shows the same "old" values. (This is despite the object having got a new, different connection from the database pool to load its values)

When the JTable refreshes again, and shows the correct "updated" data - and I load the object, the object also displays the correct data.

Is this a problem with the database connection pooling library i'm using, is there a better alternative? I've tried running all my refreshing code with SQL_NO_CACHE but this has no effect. I'm new here so let me know if there's anything i'm missing from the details, thanks!

Answer 1

Try to change the isolation level on your connections:

connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

Also, make sure you handle your transactions correctly, otherwise you might have random problems with phantom reads!

Edit: In order to have a correct isolation between your connections, you need to disable auto-commit and surround write operations inside a transaction. If you do not do that a read from another connection might occur in the middle of a write and it might return inconsistent data.