Get next work-item in Django with PostgreSQL

Question

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).

Answer 1

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.

Answer 2

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.