CODEWITHSUNDEEP
pythondjangomultithreadingdjango-models
NeoWang
NeoWang

Reputation: 18513

How to increment a counter with upper limit in Django models, with a single query?

I have a Django model like this:

class MyModel(models.Model):
    upper_limit = models.PositiveIntegerField()
    counter = models.PositiveIntegerField()

The counter of a record needs to be incremented frequently:

 mymodel = MyModel.objects.get(pk=123)
 mymodel.counter = F('counter') + 1
 mymodel.save()

The thing is: I need to make sure mymodel.counter is less than mymodel.upper_limit before making the update. I can make a query before updating:

if mymodel.counter < mymodel.upper_limit:
    mymodel.counter = F('counter') + 1
    mymodel.save()

But there are problems with this approach: The counter may not be accurate. Other threads may have updated the counter in database after my query, and the counter will exceed the limit when I increment it. I know I can use select_for_update() to lock the row, but I don't want to do that, because that way all increment actions will be serialized, blocking the threads.

What I want is to only update the counter when it is less than upper_limit, in a single query like this:

UPDATE MyModel set counter = counter + 1 
where id=123 and counter<upper_limit

Is this doable with Django ORM?

I have been thinking about pre_save signal, but the signal handler needs to know about the upper_limit, so that is a dead-end.

UPDATE: How about this, will this generate one SQL?

updated_rows = MyModel.objects.filter(pk=123,   
   counter__lt=F("upper_limit")).update(counter=F("counter")+1)
# if updated_rows > 0, I know the counter is incremented

I am not sure if the statement above will get me the single query, because update method returns a int, not a queryset, so I cannot print its query.

Upvotes: 0

Views: 791

Answers (1)

Slam
Slam

Reputation: 8572

You need consistency at DB level, so neither of app-side methods will ensure that. What you need is logic inside database. You can achieve that with triggers or with conditional updates in Django >= 1.8.

Upvotes: 1

Related Questions