CODEWITHSUNDEEP
javamysql
nafas
nafas

Reputation: 5423

Selecting rows and updating them as one command in mysql using java API

I have this scenario that I need to select a batch of data from mysql and then update them accordingly.

this is what I do in terms of pseudo code :

//select
results --> select row1 from mytable where x=0;

//update
update --> update mytable  set x=1 where row1 in (results of previous query)

here is the piece of code relating to it:

Set<String>  output= select_row1(nextPatch);  // select_row1 returns a list of string where x=0
String blockQuery= "update mytable set x=1 where row1 in (" + ouput.toString + ");"  //output toString method is triggered to work accordingly.
update(blockQuery);  //this method will set column x=1 so it won't be used by other threads.

As part of my program I need to use the returned output. So there has to be 2 queries.

well this mechanism works great if I only use one machine, but it fails miserably in concurrent system. What I wanted to do is to make sure the selected rows won't be selected again (by setting x=1) but when there are so many threads running. they can still select same results.

any ideas how to fix it?

Upvotes: 2

Views: 1022

Answers (2)

nafas
nafas

Reputation: 5423

Right, firstly thanks to @MihaiC to narrow down my search to find a solution. I finally managed to find a way to do this. Here is a brief instruction if anyone come up with same problem.

so the question was that I wanted to select a set of rows then update them accordingly for a concurrent system.

using two separate queries ( as shown in question ) will work if you have only one thread. but fails if many threads trying to select and update, as they will select same rows before they get updated with the first thread.

  1. add "For UPDATE" at the end of your selectquery

query was:

query = "SELECT row1 FROM mytable WHERE x=0 LIMIT 10;"

*changed to: *

query ="SELECT row1,x FROM mytable WHERE x=0 LIMIT 10 FOR UPDATE;"
  1. Don't use Statement, instead use PreparedStatement :

was:

Statement stmt = conn.createStatement();
stmt.execute(selectQuery);
ResultSet r= stmt.getResultSet();

changed to:

PreparedStatement stmt =conn.prepareStatement(query, ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_UPDATABLE);
stmt.execute(query);
ResultSet r= stmt.getResultSet();
  1. now we can use ReultsSet to update the rows:

was:

while(r.next()){
   String row1=r.getString("row1");
}

changed to:

while(r.next()){
   String row1=r.getString("row1");
   r.updateString("x", "1");
}

This will ensure, that each thread will pick a set of rows and update them accordingly while not interfering with one another.

Also This post was quite useful:

Upvotes: 0

MihaiC
MihaiC

Reputation: 1583

EDIT

Try to lock the table being used (read more here Table-Locking Restrictions and Conditions). Warning this might cause Exceptions to be thrown by the threads, if they can't acces the tables, so you might need to catch and handle them in the threads.

LOCK TABLES mytable WRITE, mytable READ;
update mytable set x=1 where row1 in (select row1 from mytable where x=0);
UNLOCK TABLES;

Can you not do something like this?

update mytable set x=1 where row1 in (select row1 from mytable where x=0)

Upvotes: 2

Related Questions