UniqueConstraint and get_or_create not working?

I am trying to make a thread safe/concurrent aware create function for some moel, as I understood it the following should work:

@transaction.atomic
def create_new_request(self, request: Request):
    item_id = 1
    item = Item.objects.get(id=item_id)
    print("creating", request.user)
    purchase_request, request_created = PurchaseRequest.objects.select_for_update().get_or_create(
        requester=request.user,
        status="draft",
    )
    purchase_request.save()
    print("get or created: ", purchase_request.id, request_created)

Then I created a unique constraint in the meta (as only "drafts" have this constraint of a single draft per user)

class PurchaseRequest(models.Model):
    class Meta:
        verbose_name = "Original Purchaserequest"
        verbose_name_plural = "Original Purchaserequests"
        constraints = [
            models.UniqueConstraint(
                fields=["requester", "status"], condition=Q(status="draft"), name="unique_draft_user"
            )
        ]

However the moment I sent multiple requests at once from the frontend (using promise.all to sent many simultaneously), I notice that the application crashes with the following errors:

creating paulweijtens
creating paulweijtens
creating paulweijtens
get or created:  483 True
2024-12-08 22:21:27,732 ERROR root  duplicate key value violates unique constraint "unique_draft_user"
DETAIL:  Key (requester_id, status)=(224, draft) already exists.
Traceback (most recent call last):
  File "", line 916, in get_or_create
    return self.get(**kwargs), False
           ^^^^^^^^^^^^^^^^^^
  File "", line 637, in get
    raise self.model.DoesNotExist(
isp.procure.purchase.models.PurchaseRequest.DoesNotExist: PurchaseRequest matching query does not exist.
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
  File "", line 89, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "unique_draft_user"
DETAIL:  Key (requester_id, status)=(224, draft) already exists.

What is happening here? First the handler says the get doesn't exist, and the it "does". I guess this is due to the race condition of 3 requests at the exact same time, however what is the solution?

As shown I already made the function atomic, and I already made the line lock by select_for_update()

You are not selecting any rows to lock them.

You are trying to put lock on the new row (the one you just created) and other transactions are not trying to access it so lock is ignored.

You need to put lock on related model. In this case it will be User model from request.user.

Try something like this:

@transaction.atomic
def create_new_request(self, request: Request):

    user = User.objects.select_for_update().get(id=request.user.id)
    purchase_request, request_created = PurchaseRequest.objects.get_or_create(
        requester=user,
        status="draft",
    )
    print("get or created: ", purchase_request.id, request_created)

Now when other request tries to create new PurchaseRequest it will try to access user first, but there is lock on it so it will wait until previous transaction is over and lock is released

Back to Top