Imagine you have a simple table with work-items:
|ID |OWNER|...
+---+-----+---
|123| |...
|456| |...
|789| |...
We want to provide a http API to get the next work-item which does not have an owner yet.
We use PostgreSQL.
We access the table with Django-ORM.
I guess there are several race-conditions if the API gets access simultaneously by many users.
How can I ensure with the given tools (PostgreSQL, Django) that all race conditions are solved (it is a major fault if a work-item is given to two ore more users).
With Django 1.11, select_for_update started supporting skip_locked. This means you can save on save() calls since you don't have to assign it to an owner right away.
For example, building on top of @user73657's answer:
with transaction.atomic():
work_item = WorkItem.objects.select_for_update().filter(owner__isnull=True).first()
work_item.owner = request.user
work_item.save(update_fields=['owner'])
# process work_item
you can do:
with transaction.atomic():
work_item = WorkItem.objects.select_for_update(skip_locked=True).filter(owner__isnull=True).first()
work_item.owner = request.user
# process work_item, edit other fields
work_item.save()
With skip_locked=True, the transaction skips the locked row, and is therefore non-blocking. As a bonus, you'll only need to save to the db once.
With select_for_update:
with transaction.atomic():
work_item = WorkItem.objects.select_for_update().filter(owner__isnull=True).first()
work_item.owner = request.user
work_item.save(update_fields=['owner'])
# process work_item
https://docs.djangoproject.com/en/1.11/ref/models/querysets/#select-for-update
select_for_update will make sure that only one connection can update the matching rows until the transaction has ended.