How to make a thread wait until another thread finishes?

Is there a way to lock one/more rows in MySQL?

• I have written a client-server program in JAVA, connected to MySQL database. The server handles each client in a separate thread. Now, at most each thread might lookup/edit only one row of the table. In order to promote better concurrency, it should be a good idea to lock rows instead of locking the table itself. Consider two threads, thread A and thread B, thread A requires row 1 - initially reads it and updates if necessary. However, consider thread B also needs row 1, now thread B should wait until thread A finishes reading and update. Please note thread B should not be allowed to even read the row 1 while thread A is "reading for update". I did have a look at the query "SELECT .... FOR UPDATE". However, while performing SELECT ... FOR UPDATE, it just doesnt lock the row mentioned in the result set of the select query, but locks the table. I used InnoDB, set autocommit to false also. Answers with examples would be easier to understand.

• Answer:

  As long as you use different sessions, InnoDB should take care of row locking transparently. Of course there are details about the effects of concurrency and ways to tweak the storage engine's behavior, but the overall idea is that clients should not worry about contention. InnoDB's row-level locking and transaction-based storage are some of its advantages over MyISAM, which is nontransactional and only supports table-level locking.