0

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

  • 1
    Learn about transactions and SELECT ... FOR UPDATE. – Rick James Jan 3 '18 at 20:50
  • Are those 2 queries being run on two separate connections? – Willem Renzema Jan 4 '18 at 17:55
  • @WillemRenzema I thought that is what the BEGIN statement did, creates two connections – Brian Leach Jan 4 '18 at 22:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.