I have a scenario where lets say we have a 'groups' table, a group can only have a certain number of 'members', say 10. We need to be sure that upon joining a group, if it fills the 10 places, that group is now no longer fillable.
To accomplish this we have a seperate table that tracks the counts of each group, and this table is used to find a group for the member to join.
I will use pseudocode for this discussion because this is more about, have i got the correct approach.
Firstly we use a db transaction to wrap it all up:
DB::transaction(function () {..
group = findAvailable();
group->first->counter++;
// add member
});
Next, we select for the available group using a lockForUpdate:
findAvailable()
return GROUP::join
->select()
->where()
->lockForUpdate()
->get();
Essentially, that is the approach, the idea is that the select query won't do anything until the first query is released, and when it does, if filled, it will now not be returned, and therefore not fillable.
Does that seem like it should work? Currently I am getting mixed results, it seems randomly to not work, which I am trying to figure out why. Again, hopefully that is enough info to point me in the right direction, it is not feasible to add all the code :)
I'm wondering if you even need locking. Could you do something like this?
group = findAvailable()->first();
group->counter++; // and save
startTransaction() {
group->addMember();
if (group->overLimit()) {
// rollback
group->counter--;
}
}
Then if a second process found the same group in the time before the counter++ took effect adding the member would be rolled back. Do you get enough requests at the same time that over filling groups is likely?
Please or to participate in this conversation.