How do I achieve database consistency when rewriting sections of a table?

Question

I have a simple database relationship: one Item relates to many ItemDetails. ItemDetails are updated all at once: a message comes in via HTTP POST with the full set of ItemDetails to replace any old ItemDetails with. The logic I use is this:

with transaction.atomic():
    ...
    ItemDetails.objects.filter(item_id=item_id).all().delete()
    for item in new_item_list:
        ItemDetails.objects.create(title=item.title, ...)

But this seems to have a race condition if two senders send an update at the same time. I sometimes see duplicated lists, since (I think) the delete() is running on two threads in more or less parallel and then the creates happen.

What locking strategy can I use to avoid this problem? I am using Django with PostgreSQL.

Do you know about `SELECT FOR UPDATE`? You should lock the parent item, make the update and commit the transaction. — Richard Huxton, Aug 25 '14 at 18:46
I did not but I do now and that looks perfect. Write it as an answer and I'll accept after testing it. — Scott Stafford, Aug 25 '14 at 19:09
I'm in no urgent need of the points. If you've got time feel free to write your own and accept it - it will better explain the motivation+solution anyway. — Richard Huxton, Aug 25 '14 at 19:53

score 0 · Accepted Answer · answered Aug 26 '14 at 13:48

As pointed out in the question comments, one Django/Postgres solution is to use select_for_update() on the proper Item row before messing with the children. That causes Django to block awaiting the lock. So changing the logic to the below fixes the race condition:

with transaction.atomic():
    # Just add this and Django will block here until other threads let go.
    item = Item.objects.select_for_update().get(pk=item_id)
    ...
    ItemDetails.objects.filter(item_id=item_id).all().delete()
    for item in new_item_list:
        ItemDetails.objects.create(title=item.title, ...)

How do I achieve database consistency when rewriting sections of a table?

1 Answers1