How to ensure immediate data updation in mysql db through sql query in Java

Question

I am updating status value of a table in db(mysql), i.e -2 to 0 through jdbc connection in java, now i need to fetch all those rows having 0 as status, in other method. Now when i am trying to fetch all those values having status 0, the recent updated rows are not included. I think there might be some buffer issues but not sure about it, can somebody help me out about the solution regarding the above issue.

Edit:- Currently I am using something like this.

connection.setAutoCommit(false);
String taskUpdateString = "update query"
taskStatement = connection.prepareStatement(taskUpdateString);
taskStatement.execute();
connection.commit();

once you update the rows, are you performing commit operation? — YoungHobbit, Jan 08 '16 at 09:19
Have you tried [Stored procedures](https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-stored-procedures.html)? — janani, Jan 08 '16 at 09:22
I don't get it. You updated your rows and then you selected your rows and there you got unchanged status rows? — hurricane, Jan 08 '16 at 09:53

score 0 · Answer 1 · answered Jan 08 '16 at 09:22

updates in mysql happen as immediate after commit. but for the update mysql has to aquire a lock on the table or row, (for details: http://dev.mysql.com/doc/refman/5.7/en/internal-locking.html). so it might be cause mysql is waiting for locks, or the buffering is on the query side

score 0 · Answer 2 · edited May 23 '17 at 12:03

Are you sure you are closing all the java connections properly?

When you are done with using your Connection, you need to explicitly close it by calling its close() method in order to release any other database resources (cursors, handles, etc) the connection may be holding on to.

The safe pattern in Java is to close your ResultSet, Statement, and Connection (in that order) in a finally block when you are done with them, something like" ... See answer with 55 ticks on Stack overflow here: Closing Database Connections in Java

Hope that helps.

How to ensure immediate data updation in mysql db through sql query in Java

2 Answers2