Get the 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 the http API to get the next work item that does not yet have an owner.

We use PostgreSQL.

We access the table using Django-ORM.

I assume that there are several race conditions if the API is accessed simultaneously by many users.

How can I ensure using these tools (PostgreSQL, Django) that all the conditions of the race will be resolved (this is a serious mistake if the work item is provided to two other users).

Answer 1

With Django 1.11, select_for_update started supporting skip_locked . This means that you can save save() calls, since you do not need to assign them to the owner right away.

For example, building on top of @ user73657 the 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 transaction skips a locked row and therefore does not block. As a bonus, you will only need to save it in 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 ensure that only one connection can update the corresponding rows before the transaction completes.