MySQL - Avoid to select, and update, the same record on repeatable_read

Question

i'm developping a PHP service that need to get a unique TOKEN from a MySQL table. The table is like:

ID    TOKEN   ID_PROCESS
1     AAAAA   0
2     BBBBB   0
3     CCCCC   0

The table has millions of records with already generated TOKEN.

I need to be extra sure that, for example, the record with ID = 1 is selected and then updated ( UPDATE table set ID_PROCESS = 123 WHERE ID = 1) from one user and that nobody else could select it and then update it overwriting the former update.

For selecting an available TOKEN i just do:

SELECT FROM table WHERE ID_PROCESS = 0 ORDER BY RAND() LIMIT 1

If two concurrent sessions select the same record i'm in trouble, if i use transaction, with isolation level REPEATABLE_READ, i'm not going to see the first update.

How can i achieve the security to pick an available TOKEN in a concurrent scenario?

Thanks for your help!

Answer 1

The easiest solution is to UPDATE a single row out of those that have no process assigned.

UPDATE mytable SET ID_PROCESS = 123 WHERE ID_PROCESS = 0 LIMIT 1;

Then commit quickly to release the row locks, because all rows with ID_PROCESS = 0 will be locked until you end your transaction.

Once that's done, assuming each concurrent client uses a distinct process id, you can select the row you just updated.

SELECT ... FROM table WHERE ID_PROCESS = 123;

Note this has nothing to do with transaction isolation level. This locking behaves the same way in REPEATABLE READ, READ COMMITTED, and other isolation levels.