如何通过限制SUM防止INSERT上Django中的竞争条件？ [英] How to prevent race condition in Django on INSERT with limiting SUM?

问题描述

考虑两个模型：

• 项目：
  
  
  
  
  • id：一个 AutoField 主键
  
  
  • 预算：A PositiveIntegerField
  
  
  • 一些不相关的字段
  
  
  • Project:
    • id: An AutoField primary key
    • budget: A PositiveIntegerField
    • some unrelated fields
    
    
    • id：一个 AutoField 主键
    
    
    • 金额：A PositiveIntegerField
    
    
    • 项目：A ForeignKey 到 Project
    
    
    • 一些不相关的字段
    
    
    • id: An AutoField primary key
    • amount: A PositiveIntegerField
    • project: A ForeignKey to Project
    • some unrelated fields

    对于每个项目，我希望始终确保其支出总额小于或等于预算。

    For each project, I wish to ensure that the sum of their expenses is less than or equal to the budget at all times.

    在SQL方面： 从费用中选择SUM（amount），而project_id =？; 应始终小于或等于从项目WHERE id =？中选择预算；

    In SQL terms: SELECT SUM(amount) FROM expense WHERE project_id = ?; should always be less than or equal SELECT budget FROM project WHERE id = ?;

    在Django中有任何方法可以做到这一点，请记住，可能有多个人正在访问网络erver并同时创建 Expense s？

    Is there any way to do this in Django, keeping in mind that multiple people may be accessing the web server and creating Expenses at the same time?

    我正在使用postgresql作为数据库后端。

    I am using postgresql as my database backend.

    我尝试使用 select_for_update ，但这不会阻止 INSERT

    I have tried using select_for_update but that doesn't prevent INSERTs from taking place and it doesn't seem to work on aggregations anyway.

    我正在考虑保存 费用首先进入数据库，然后查询总费用，并删除费用如果预算超支，但是我会需要该代码在事务之外，以便其他线程可以看到它，然后如果服务器停止中间处理，则数据可能处于无效状态。

    I was considering saveing the Expense to the database first, then query the total cost and remove the Expense if it's over-budget but then I would need that code to be outside of a transaction so that other threads can see it and then if the server stops mid-processing, the data could be left in an invalid state.

    推荐答案

    感谢@Alasdair向我指出正确的方向。

    Thanks to @Alasdair for pointing me in the right direction.

    在填写字段后实例（新的费用），请执行以下操作：

    After filling out the fields of inst (a new Expense), do:

    with transaction.atomic():
        project = models.Project.objects.select_for_update().get(
            pk=project_id)
        cost = project.total_cost()
        budget = project.budget
    
        if cost + inst.cost > budget:
            raise forms.ValidationError(_('Over-budget'))
    
        self._inst.save()
    

    请注意，我将总成本定义为 Project ：

    class Project:
        def total_cost(self):
            return self.expense_set.all().aggregate(
                t=Sum(F('cost')))['t']
    

    这篇关于如何通过限制SUM防止INSERT上Django中的竞争条件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！