How can I uniquely pop from a MySQL InnoDB table without race hazard issues? [closed]

Question

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?

Answer 1

Row locking is the default locking feature of InnoDB

You may need to do two things:

First, setup a transaction

BEGIN TRANSACTION;
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';
COMMIT;

Second, (this is optional) experiment with switching the Transaction Isolation Level during the DB Session. This may provide the needed intermittency of data you need.