On Mon, May 23, 2016 at 12:58 PM, Erik Cederstrand <
erik+li...@cederstrand.dk> wrote:
> Hi,
>
> I have inherited a legacy Item model that has a composite unique key
> consisting of a Customer ID and a per-customer, incrementing Item ID.
> Assume I can't change the model.
>
> On inserts, the legacy code would let the database increment the Item ID
> to avoid race conditions. Something like this:
>
> INSERT INTO item_table (customer_id, item_id, name, ...) VALUES (123,
> (SELECT MAX(item_id) FROM item_table WHERE customer_id =123) + 1, 'MyItem',
> ...);
> Is there any way I can do the same using the Django ORM without opening up
> for race conditions? I.e. something better than:
>
> i = Item(customer_id=123, name='MyItem', ...)
> i.item_id =
> Item.objects.filter(customer_id=123).aggregate(Max('item_id'))['item_id__max']
> + 1
> i.save()
>
>
I feel like an explicit transaction wrapping this set of queries would be
the way to go to help avoid (or at least detect) a race condition:
https://docs.djangoproject.com/en/1.9/topics/db/transactions/#controlling-transactions-explicitly
Or do I just wrap that in a loop and catch IntegrityError if I don't want
> to use raw SQL?
>
Even with the transactions in place, you'd still need to account for times
when the transaction fails. At that point the logic is up to you. I
definitely wouldn't put an infinite loop in, as there may be some other
problem that is preventing the transaction from completing, rather raise an
exception and handle it in the view accordingly. The transaction also gives
you the advantage of having a known start point (nothing incidentally
created as part of a failed operation), so it is easier to recover from.
The efficacy of this solution is also heavily dependent on your underlying
DB and the supported level of isolation. Postgres seems to be the winner in
terms of concurrency, transactions, and preemptive failure detection.
I don't believe there is a magic bullet for this case, but I'm no DB expert.
-James
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/CA%2Be%2BciW2N2M0-O5OcO%3DeHf_RskJSQtE6wckgrWXYZ9WRwBsUjw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
