Reliably assign a row to a user in MySQL

Question

I have a table with millions of rows:

id | info | uid

The uid is null by default. I want to select 10 rows and assign them to a uid, but I want to avoid any potential concurrency issues. So I think the only way to do that is to somehow select 10 rows based on certain criteria, lock those rows and then make my changes before unlocking them.

Is there a way to do row-locking in MySQL and PHP? Or is there some other way I can gaurantee that this doesnt happen:

user a queries the table where uid is null
finds row 1
user b queries the table where uid is null
finds row 1
user a process row and sets it back to null
user b process row and sets it back to null

See my problem?

Search is your friend, possible duplicate of [PHP / MySQL - how to prevent two requests \*Update](http://stackoverflow.com/a/15027067/342740) — Prix, Nov 07 '14 at 21:37
possible duplicate of [PHP / MySQL - how to prevent two requests \*Update](http://stackoverflow.com/questions/15026825/php-mysql-how-to-prevent-two-requests-update) — Mike Brant, Nov 07 '14 at 21:46

score 0 · Answer 1 · answered Nov 08 '14 at 01:55

What you probably need is SELECT ... FOR UPDATE. With this, retrieved rows are locked until a COMMIT or a ROLLBACK is made. So you can do something like :

START TRANSACTION;

SELECT * FROM yourTable WHERE uid IS NULL FOR UPDATE;

-- UPDATE to whatever you want

COMMIT;

Reliably assign a row to a user in MySQL

1 Answers1