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