Row locking to update

I have innodb table read by lot of different in开发者_开发百科stances (cloud)

Daemon in each instance takes 100 rows to "do things" of this table, but I don't want 2 (or more) instances to take the same things.

So I have a "status" column ("todo", "doing", "done").

INSTANCE 1: It takes 100 rows where status = "todo" ... Then I need to UPDATE these rows asap to status "doing", so INSTANCE 2,3,..x can't take the same rows.

How can i do it ?

Please, I would like a solution without LOCKING WHOLE table, but locking just the rows (that's because I use innodb) ... I have read a lot about that (LOCK SHARE MODE, FOR UPDATE, COMMITs ... ) but I do not get the right way ...

You should use LOCK TABLES and UNLOCK TABLES functions to do this:

http://dev.mysql.com/doc/refman/5.1/en/lock-tables.html

use a transaction and then SELECT ... FOR UPDATE when you read the records. This way the records you read are locked. When you get all the data update the records to "doing" and COMMIT the transaction. Maybe what you were missing is the use of a transaction, or the correct order of commands. Here is a basic example:

BEGIN TRANSACTION;
SELECT * FROM table WHERE STATUS = 'todo' FOR UPDATE;

// Loop over results in code, save necessary data to array/list..

UPDATE table SET STATUS ='doing' WHERE ...;
COMMIT;

// process the data...

UPDATE table SET STATUS ='done' WHERE ...;