Question 200268 in Redis Question posted in
The official documentation can be found here.

Redis – How to safely and atomically decrement a counter with Django and PostgreSQL?

I’ve been reading up on PostgreSQL transaction isolation and how that relates to Django’s transaction.atomic() (e.g. this article, PostgreSQL docs), but I’m far from fluent in this topic and I’m not sure I understand what I’ve read.

We’ve got a PostgreSQL-backed Django app that involves quota objects. Simplified, it’s just this:

class Quota(models.Model):
    obj = models.OneToOneField(AnotherModel)
    count = models.PositiveIntegerField()

An instance of this controls how many times a certain operation can be performed against the obj instance. count is initialized to a certain number, and will only ever decrement until it hits zero.

Any number of processes/threads can concurrently perform these operations. Basically, we need to atomically decrement (with UPDATE) the count of a single database row without deadlocking and without two processes/threads ever e.g. starting with a count of 100 and both trying to decrement it to 99.

My naive approach would be this:

with transaction.atomic():
    cursor = connection.cursor()
    cursor.execute('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE')
    Quota.objects.filter(obj=instance).update(count=F('count')-1)

However, I’m not sure if this is subject to this issue, from the linked article:

if at COMMIT the database cannot determine that the transaction could have been performed serially with respect to the read/writes of other transactions, then it will fail with a django.db.DatabaseError. This can happen even if they updated different rows.

All the processes/threads performing operations against the same obj would be decrementing the same column of the same row, so… maybe? I don’t actually know what’s involved in PostgreSQL "determin[ing] that the transaction could have been performed serially".

An alternate approach could be:

with transaction.atomic():
    Quota.objects.select_for_update().filter(obj=instance).update(count=F('count')-1)

This seems to do row-level locking, and my understanding is that the isolation level change isn’t needed, but I don’t know if this is sufficient for correct handling of concurrent operations.

Is one of these approaches preferrable here, and are some modifications still necessary to guarantee atomicity and deadlock avoidance? We could use something like python-redis-lock to also prevent concurrent DB operations at the Django view level, but this feels like a more natural fit to do at the DB level.

Tags:

2

Answers


  1. 0

    You can try database constraints, that way the responsibility for keeping your data consistent will be taken care by your database.
    Django have support for constraints, so you can check documentation and experiment a little with it

    https://docs.djangoproject.com/en/4.2/ref/models/options/#django.db.models.Options.constraints

    In your case it should be something like

    class Quota(models.Model):
    obj = models.OneToOneField(AnotherModel)
    count = models.PositiveIntegerField()

        class Meta:
        constraints = [
            models.CheckConstraint(check=models.Q(count__gte=0), name="count_gte_0"),
        ]

    That way, when you save your changes, database will check your data and raise error if it fails

    Login or Signup to reply.
  2. 0

    something like this will stop your count to go negative.

    class Quota(models.Model):
    obj = models.OneToOneField(AnotherModel)
    count = models.PositiveIntegerField()
    
    def decrement(self):
        result = Quota.objects.select_for_update().filter(id=self.id,count__gt=0).update(F('count')-1)

    and now you can check if result is 0 then the filter has failed and maybe there is no row with count bigger than 0.( so basically never gets below zero)

    and if its 1 then database found a row and performed a decrement by 1.

    every time you call decrement your code waits to for select_for_update lock until it is released that row ( if there is a lock on it) so deadlock might happen you can use nowait=True option but beware of raised exceptions.(doc)

    you can also wrap this whole method in an atomic transaction and even check if anything went wrong to restore it to the last point.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search