I have a table called
assignments. A user will request an assignment from an API endpoint. I need to ensure that an assignment can only be handed to one user. There will be multiple users requesting their next assignment. I have a column called
locked_by, although I am not sure that marking this column will ensure that one and only one user can select that row.
What strategies are used in this scenario?
I am using mysql and SQLAlchemy
Edit: Here is a code snippet that does not work like I expect it to. Both queries return the same row.
BEGIN; SELECT * FROM task_queue_contents tqc WHERE tqc.locked_by_id IS NULL LIMIT 1 FOR UPDATE; BEGIN; SELECT * FROM task_queue_contents tqc WHERE tqc.locked_by_id IS NULL LIMIT 1 FOR UPDATE;
I would like to prevent that first row that has been read from being selected by any other user (since only one user may hold a task)
MySQL 2nd Gen 5.7 on Google Cloud Platform