How can I improve the following code with row locking / stored procedure.
UPDATE `data` SET status=':temp_status' WHERE account_id = :account_id AND dataset_id=':dataset' AND status=':source' LIMIT $limit; SELECT id,phone,firstname,lastname FROM `data` WHERE account_id = :account_id AND status = ':temp_status'; UPDATE `data` SET status=':destination',status_updated=NOW() WHERE account_id = :account_id AND status = ':temp_status';
I am essentially reading data from a table my many clients to then further process, I need to make sure that each client only grabs a bunch of unique records, which then can't be obtained by any other users.
I am running this from PHP with multi-query on InnoDB tables;
Can I achieve this with row locking?