mysql - Using SELECT...FOR UPDATE, then updating multiple rows -

i'm writing application to:

• select small recordset table of subscribers (150k records);
• update rows indicate email in process of being sent;
• send email subscribers in recordset;
• update rows again indicate email has been sent.

the wrinkle table simultaneously being accessed multiple clients distribute email workload, why there intermediate update (to indicate in-process) used -- keep different clients selecting same rows, results in multiple emails being sent same subscriber. i've applied randomizing logic reduce likelihood of 2 clients working same data, still happens occasionally.

so looking @ using select ... update in order lock relevant rows (so client won't select them). question: better write update statement based on ids of select...for update statement, or create loop update each row individually?

here's i've got far:

delimiter $$  create definer=`mydef`@`%` procedure `sp_subscriberstosend`(v_limit integer) begin  start transaction;  select _id, email, date_entered, date_format(date_entered, '%b %e, %y') 'date_entered_formatted' _subscribers send_state = 'send' , status = 'confirmed' limit v_limit update;  [[update _subscribers set send_state = 'sending' _id in (...?)]]  [[or]]  [[loop through resultset , update each row?]] commit;  end 

seems single update going more efficient; best way turn _id column of resultset comma-delimited list in() clause? (i've been doing client-side before this) -- or there better way altogether?

instead of trying create comma-delimited list, update same criteria select

start transaction;  update _subscribers set send_state = 'sending' send_state = 'send' , status = 'confirmed' order <something> limit v_limit;  select _id, email, date_entered, date_format(date_entered, '%b %e, %y') 'date_entered_formatted' _subscribers send_state = 'send' , status = 'confirmed' order <something> limit v_limit;  commit; 

the order by clause necessary ensure both queries process same rows; if use limit without order by, select different subset of rows.