Avoid concurrent access to same queue element

Question

I'm reviewing/refactoring a queue that's going to be used internally by up to 20 people simultaneously but as of now, multiple people can access the first element ( We tried it locally clicking on the link at the same time. )

The flux is similar to this:

views.py

[GET]
def list_operator(request, id):
  if request.POST:
    utilitary = Utilitary(id)
    pool = ThreadPool(processes=1)
    async_result = pool.apply_async(utilitary.recover_people, (id, ))
    return_val = async_result.get()
    person = People.objects.get(pk=return_val)
    return redirect('people:people_update', person.pk)

utilitary.py

This file has the method recover_people which performs around 4-5 queries (where people have flag_allocated=False) across multiple tables and sorts a list, to return the first element. The final step is this one:

for person in people:
  p_address = People_Address.objects.get(person_id=person.id)
  p_schedule = Schedules.objects.get(schedules_state=p_address.p_state)

  if datetime.now() > parse_time(p_schedule.schedules_hour):
    person = People.objects.get(pk=person.id)
    person.flag_allocated = True
    person.date_of_allocation = datetime.now()
    person.save()
    return person.pk

Perhaps something in the Utilitary method's logic is wrong? Or I should be expecting this problem with this amount of people simultaneously calling this method?

Could using a cache help? I'm sorry, I'm new to django and mvc.

Answer 1

In this case, it seems like a lot to select the "person" in the database again just to lock it in memory and then write it to the database. This action is not atomic.

You can have the record locked by other process between these actions:

person = People.objects.get(pk=person.id)
person.flag_allocated = True
person.date_of_allocation = datetime.now()
person.save()

That is where your problem is. But... If you directly update the record on the database passing a condition on which the update will only write on a record where the flag_allocated=False, you just have to see if your update affected any row or not. If not, you go to the next person on the queue.

Something like:

for person in people:
    rows = People.objects.filter(pk=person.id, flag_allocated=False).update(flag_allocated=True)
    if rows:
        break # Got the person... And nobody else will.

The update will have the record locked to write it to the allocation_flag (SQL principle). If two updates try to mess with the same row, one will do it first and then the second won't update anything and will try the next person.