0

I am trying to figure out the way to enforce a maximum number of relations in a highly concurrent system. The following is an example use case.

// Job table
job_id   | ... | num_positions
xx-xx-xx | ... | 3

// Positions table - only 1 spot left
position_id | job_id   | user_id
aa-aa-aa    | xx-xx-xx | ee-ee-ee
bb-bb-bb    | xx-xx-xx | ff-ff-ff

Now let's say two people try to fill the last spot at the same time. What is the best/right way to enforce that there will only ever be a maximum of 3 records? Ideally, the transaction that fires first fills the spot. What is the best way to implement this sort of functionality? Isolation levels etc?

Thanks in advance

Improve this question
1
  • two people try to fill the last spot at the same time. By two concurrent and independent INSERTs?
    – Akina
    Oct 30, 2020 at 9:29

1 Answer 1

Reset to default
1

Think about

// Positions table - 3 spots for `xx-xx-xx` job, only 1 spot is free
position_id | job_id   | user_id
------------+----------+---------
aa-aa-aa    | xx-xx-xx | ee-ee-ee    -- used spot
bb-bb-bb    | xx-xx-xx | ff-ff-ff    -- used spot
cc-cc-cc    | xx-xx-xx | NULL        -- free spot

The amount of spot rows per a job is equal to jobs.num_positions for according job_id value. They're created with according amount when the row is inserted into jobs table or when jobs table is updated and amount value for some job(s) is increased.

While user filling a spot users's id inserts to the user_id column. When user frees a spot its user_id value is updated to NULL back.

All operations are performed only by UPDATE queries. Spot filling query includes WHERE job_id = @job AND user_id IS NULL LIMIT 1.

If two concurrent users try to fill a spot the user which do this earlier will fill whereas the user who tries to do this later will fail because there is no record which matches the conditions WHERE job_id = @job AND user_id IS NULL.

Improve this answer
1
  • Further more you can maintain the list automatically with triggers. Block insert and delete operations that would change the number of rows on the position table by rasing an error from a trigger, use triggers on the job table to manage rows in position when a job is added/deleted or num_positions is updated, raising errors if there are too many to support a reduction in num_positions. You'll need to make sure that the changes made to position by the triggers on job don't trip the errors on modification in those triggers.
    – David Spillett
    Oct 30, 2020 at 10:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.