Django - assign a single record from a queryset to a user and update it. Only releasing the record to another user if the transaction fails

Question

I'm a Django beginner and I'm trying to create a warehouse management system in Django. The system keeps track of what products enters the warehouse and therefore knows what is in the warehouse and at which location.

There are several users (order pickers) who are logged in simultaneously and who have to get the products from the warehouse.

My model looks like this:

class Product(models.Model):

    location = models.IntegerField(validators=[MinValueValidator(1),MaxValueValidator(5000)])
    already_picked = models.BooleanField(default=False, blank=True)
    available_for_picking = models.DateField(blank=True, null=True)

    def __str__(self):
        return str(self.id)

The products that have to be picked from the warehouse on a certain day must meet a number of conditions.

For instance:

available_for_picking = today or date in the past

AND

aldready_picked = False

What I want to do:

Every user goes to the url: www.mydomain.com/get_order_location_to_pick and gets a location of a product that he has to pick up from the warehouse.

However, there are several products that meet the selection criteria and there are several users who use the software at the same time. So I want to avoid assigning the same product (and location) to more than 1 user. When the user has taken the product, boolean "already_picked" should be changed from False to True. If the user has not been able to retrieve the product assigned to him from the warehouse for whatever reason, the product must be assigned to another user when he/she goes to the same url www.mydomain.com/get_order_location_to_pick

What's a good way to do this?

In my view I currently have this:

@login_required(login_url='login')
def get_order_location(request):
    products_to_pick = Product.objects.filter(already_picked=False, available_for_picking__lte= datetime.now())
    first_product_to_pick = products_to_pick.first()
    context = {
        'first_product_to_pick' : first_product_to_pick
    }
    return render(request, 'show_the_order_to_pick.html', context)

This isn't really working. It just gives the same product from the QuerySet and assigns it to all users currently visiting the url www.mydomain.com/get_order_location_to_pick

I read something about the Django function select_for_update() but I don't get my head wrapped around what it does. Is this something I might use to "lock" the first product to the first user while is busy looking for the product in the warehouse?

In a way I'm trying to assign a single record from a queryset to a user while he is performing an action on it and only making this object available for other users again when the transaction wasn't successful.

score 1 · Answer 1 · answered Feb 23 '22 at 22:59

1

This isn't really working. It just gives the same product from the QuerySet and assigns it to all users currently visiting the url

This view doesn't change the already_picked attribute of the Product it selects, so this will show the same Product to everyone.

I read something about the Django function select_for_update() but I don't get my head wrapped around what it does. Is this something I might use to "lock" the first product to the first user while is busy looking for the product in the warehouse?

Yes, that's correct. select_for_update() can be used to lock rows for a short period of time while you mark them as being picked. Also note the skip_locked parameter to that function, which can be used to tell the database that if the select encounters a locked row, it should skip it instead of waiting for the row to become available.

answered Feb 23 '22 at 22:59

Nick ODell

15,465
3
32
66

Hi Nick, thanks for your input! Could you be more specific on how you would implement a solution to my problem? – user18293173 Feb 25 '22 at 19:02
@user18293173 Are you already familiar with how to [modify a model object](https://stackoverflow.com/questions/3681627/how-to-update-fields-in-a-model-without-creating-a-new-record-in-django)? – Nick ODell Feb 25 '22 at 19:33
Yes, I know how to update a model object. A solution would indeed be changing the attribute already_picked on the Product model from a BooleanField to an IntegerField. I could set the value of this attribute to 0 when a new record is created (a product is stored in the warehouse), then when I assign the product to a user, to be picked, I could change the status to 1 (meaning it's being picked). Then, when the user confirm the picking has been done, the status could again be updated to 2 (meaning the picking is finished). My question is more if this is really the way to handle this problem? – user18293173 Feb 26 '22 at 22:45
@user18293173 I agree, that seems like a good solution. – Nick ODell Feb 26 '22 at 22:47
Really? To me, this feels somehow as some kind of clumsy solution to this problem. And it doesn't make use of the select_for_update() function in Django. I assumed there would be a more elegant solution. – user18293173 Feb 26 '22 at 22:55
@user18293173 If you have multiple Django processes serving traffic in parallel, you can use select_for_update() to prevent concurrent access to a product. You can use a queryset with select_for_update() followed by a filter() looking for already_picked=0, then inside a transaction, set already_picked to 1. Then, you can exit the transaction, and know that no other Django process concurrently picked the same product. The docs explain how to do this: https://docs.djangoproject.com/en/4.0/ref/models/querysets/#select-for-update – Nick ODell Feb 27 '22 at 02:16

Django - assign a single record from a queryset to a user and update it. Only releasing the record to another user if the transaction fails

1 Answers1