MYSQL SELECT ... FOR UPDATE Not working?

I am having issues with MySQL's SELECT .. FOR UPDATE, here is the query I am trying to run:

SQL = "SELECT * " +
      "FROM " + TableName + " " + 
      "WHERE out_status IN ("+outSStatus+") AND queued <= NOW() " +
      "ORDER BY out_status, id_queue ASC "+ limitSql+
      "FOR UPDATE";

After this, the thread will do an UPDATE and change the out_status to 99, which is then it should unlock the row.

I am running a multi-threaded java application, so 3 threads are running this SQL statement, but when thread 1 runs this, it doesn't lock (h开发者_Python百科ide) its results from thread 2 & 3. Therefore threads 2 & 3 are getting the same results.

Also each thread is on its own mysql connection.

Can anybody please help me with this? OR perhaps have a better solution?

Much Appreciated.

You can use transactions

I had a similar issue. Both answers, from @Vikash and @Adeel Ansari, are valid suggestions. However, I solved the issue by using InnoDB engine instead of MyISAM. MyISAM only allows locking on table level, not on row level. You still need transactions, when using InnoDB.

It might be working, and you are unable to notice. Say thread 1 executed the statement, and committed the transaction automatically, because auto-commit was on. Then of course thread 2 would be able to run that too.

Try setting auto-commit off, using this method of Connection class and then see the result.