Sorry, it doesn't work that way. Separate processes want to get the _same_ set of 10 rows, regardless of LOCK mode, etc. The locks prevent stepping one each other.
Each row needs 3 states (not 2):
* Unprocessed
* Being processed
* Finished
Something like this would work for 1 at a time:
BEGIN;
# Find one item:
SELECT @id := id, ...
FROM t
WHERE state = 'unprocessed'
ORDER BY id
LIMIT 1
FOR UPDATE; # Note
# Grab it:
UPDATE t SET state = 'being_processed'
WHERE id = @id;
COMMIT; # Release control
BEGIN;
# ... do the work ...
COMMIT;
BEGIN;
# Mark it finished:
UPDATE t SET state = 'processed'
WHERE id = @id;
COMMIT;
To get batches of 10 is a little trickier:
BEGIN;
SET @worker_num = 1; # unique to each worker process
# Claim the items:
UDPATE t SET
state = 'being_processed'
worker = @worker_num # Note: need extra field
WHERE state = 'unprocessed'
ORDER BY id
LIMIT 10
FOR UPDATE;
COMMIT; # Release control
BEGIN;
# Find out which ones I claimed:
SELECT * FROM t
WHERE state = 'being_processed'
AND worker = @worker_num
FOR UPDATE;
# ... do the work ...
COMMIT;
BEGIN;
# Mark them finished:
UPDATE t SET state = 'processed'
WHERE state = 'being_processed'
AND worker = @worker_num;
COMMIT;
If you want all this to be really automated, you need to think about recovering from a process dying. If a process dies, the multi-row example has recorded who died. It should also record a timestamp so that you could automatically discover an orphaned "being_processed" record (after some timeout), and try to automatically reprocess it.