Django ORM 和锁表 [英] Django ORM and locking table

问题描述

我的问题如下:

我有一个汽车经销商 A 和一个名为 sold_cars 的数据库表.出售汽车时，我会在此表中创建条目.

I have a car dealer A, and a db table named sold_cars. When a car is being sold I create entry in this table.

表有一个名为 order_no 的整数列.它在经销商销售的汽车中应该是唯一的.

Table has an integer column named order_no. It should be unique within cars sold by dealer.

所以如果经销商A卖汽车a、b和c，那么这一列应该是1,2,3.我必须使用此列，而不是主键，因为我不想在我的编号中出现任何漏洞 - 经销商 A 和 B(可能会在稍后添加)的订单号应为 1、2、3，而不是 A: 1, 3, 5, and B: 2, 4, 6. 所以...我为给定的经销商选择最后一个最大的order_no，将其加1并保存.

So if dealer A sold cars a, b and c, then this column should be 1, 2, 3. I have to use this column, and not a primary key because I don't want to have any holes in my numeration - dealer A and B (which might be added later) should have order numbers 1, 2, 3, and not A: 1, 3, 5, and B: 2, 4, 6. So... I select last greatest order_no for given dealer, increment it by 1 and save.

问题是两个人在同一毫秒内从经销商 A 处购买了汽车，并且两个订单都获得了相同的order_no.有什么建议吗?我想在事务块中关闭此进程，并锁定此表直到事务完成，但找不到有关如何操作的任何信息.

Problem is that two people bought car from dealer A in the same millisecond and both orders got the same order_no. Any advice? I was thinking of closing this process in a transaction block, and locking this table until the transaction is complete, but can't find any info on how to to that.

推荐答案

我建议使用 F() 表达式 而不是锁定整个表.如果您的应用被大量使用，锁定表将对性能产生重大影响.

I would recommend using the F() expression instead of locking the entire table. If your app is being heavily used, locking the table will have significant performance impact.

Django 文档中提到了您描述的确切场景 此处.根据您的情况，您可以使用以下代码:

The exact scenario you described is mentioned in Django documentation here. Based on your scenario, here's the code you can use:

from django.db.models import F


# Populate sold_cars as you normally do..
# Before saving, use the "F" expression
sold_cars.order_num =F('order_num') + 1
sold_cars.save()

# You must do this before referring to order_num:
sold_cars.refresh_from_db()
# Now you have the database-assigned order number in sold_cars.order_num

请注意，如果您在更新操作期间设置 order_num，请改用以下内容:

Note that if you set order_num during an update operation, use the following instead:

sold_cars.update(order_num=F('order_num')+1)
sold_cars.refresh_from_db()

由于数据库负责更新字段，因此不会有任何竞争条件或重复的 order_num 值.此外，这种方法比使用锁定表的方法快得多.

Since database is in charge of updating the field, there won't be any race conditions or duplicated order_num values. Plus, this approach is much faster than one with locked tables.

这篇关于Django ORM 和锁表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！