I have a Django model like this:
我有一个像这样的Django模型：
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:
问题是：在进行更新之前，我需要确保mymodel.counter小于mymodel.upper_limit。我可以在更新之前进行查询：
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.
但这种方法存在问题：计数器可能不准确。其他线程可能在我的查询后更新了数据库中的计数器，并且当我递增它时计数器将超过限制。我知道我可以使用select_for_update（）来锁定行，但我不想这样做，因为这样所有的增量操作都会被序列化，阻塞线程。
What I want is to only update the counter when it is less than upper_limit, in a single query like this:
我想要的是只在小于upper_limit时更新计数器，在这样的单个查询中：
UPDATE MyModel set counter = counter + 1
where id=123 and counter<upper_limit
Is this doable with Django ORM?
这对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.
我一直在考虑pre_save信号，但信号处理程序需要知道upper_limit，所以这是一个死胡同。
UPDATE: How about this, will this generate one SQL?
更新：这个会产生一个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.
我不确定上面的语句是否会给我单个查询，因为update方法返回一个int，而不是一个查询集，所以我无法打印它的查询。
1 个解决方案
#1
1
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.
您需要在数据库级别保持一致性，因此应用程序方法都不能确保这一点。你需要的是数据库中的逻辑。您可以使用触发器或Django> = 1.8中的条件更新来实现。
#1
1
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.
您需要在数据库级别保持一致性，因此应用程序方法都不能确保这一点。你需要的是数据库中的逻辑。您可以使用触发器或Django> = 1.8中的条件更新来实现。