How to ensure the data is correct if two DMLs occur simultaneously in Django ORM?

Question

My Django project uses the mysql and InnoDB engines. I have a table as shown below, the queue field default value is 0

class Task(models.Model):
    task_id = models.CharField(max_length=32, primary_key=True)
    queue = models.IntegerField(default=0)

I have two processes, one is to insert a record based on the maximum queue in the table.

max_queue = Task.objects.all().annotate(data=Max('queue')).values('data')
 queue_number = max_queue[0]['data'] + 1
 record = {'task_id':task_id, 'queue': queue_number}
 Task.objects.create(**record)

Another process is to decrement the value of queue by 1 for each record where queue

is not equal to 0.

query_list = Task.objects.filter(~Q(task_queue=0))
for query in query_list:
    Task.objects.filter(task_id=task_id).update(queue=query.queue - 1)

What I care about here is whether these two processes occur at the same time. For example, if the subsequent process wants to decrement each value. At the same time, the first process inserts a new value. In this case, some errors may occur.

what should I do? Anyone has a good idea, thanks in advance.

Answer 1

You can use select_for_update() for this, it will lock the record you are updating until the end of the transaction

from django.db import transaction
from django.db.models import Q

with transaction.atomic():
    query_list = Task.objects.select_for_update().filter(~Q(queue=0))
    for query in query_list:
        query.queue -= 1
        query.save()