CODEWITHSUNDEEP
sqldjangoorm
Sarvi Shanmugham
Sarvi Shanmugham

Reputation: 505

Django atomically limit check and increment counter for multiple tables

I have 2 models/tables Cluster and Node, and possibly more, each having a "volume_limit" and "volume_count" field/column in it.

I want to atomically check the counter against the limit in its own table and increment the counter. To atomically check and increment for just Cluster, I would do the following

updated = Cluster.objects.filter(id=cluster_id,     volume_count__lt==F('volume_limit')).update(volume_count = F('volume_count')+1)
if not updated:
    raise Validation('limit reached ......')

If I wanted to increment both Node and Cluster, I would add the following, which would undo the cluster increment, if I cant increment the node counter. Like below

updated = Node.objects.filter(id=node_id, volume_count__lt==F('volume_limit')).update(volume_count = F('volume_count')+1)
if not updated:
    Cluster.objects.filter(id=cluser_id).update(volume_count = F('volume_count')-1)
    raise Validation('limit reached ......')

I am wondering if there is way to do both of this atomically?

Basically I want to do the following in query/update operation

  1. create a query for Cluster and Node objects, and possible more objects, that all have "volume_count" and "volume_limit", that need to be simultaneously checked/incremented.
  2. Check that the query translates to exactly one object for each query. i.e. the counts are within limit for each object/table. That is if I am checking Cluster and node, then the query should tranlsated to only 2 object,
  3. Do a volume_count=F('volume_count')+1 for all the query results i.e. objects/table together.

How do I do this?

Upvotes: 0

Views: 581

Answers (1)

Kevin Christopher Henry
Kevin Christopher Henry

Reputation: 48952

Using transactions should solve your problem. You didn't specify your database, but on PostgreSQL with the default transaction isolation level, I believe this will work:

with transaction.atomic():
    n_updated = Node.objects.filter(
                    id=node_id,
                    volume_count__lt==F('volume_limit')
                ).update(volume_count=F('volume_count') + 1)
    c_updated = Cluster.objects.filter(
                    id=cluster_id,
                    volume_count__lt==F('volume_limit')
                ).update(volume_count=F('volume_count') + 1)

    if not (n_updated and c_updated):
        # trigger rollback
        raise ValidationError("Limit reached")

The key point is that you're targeting specific rows, and if possible updating them, which will cause other transactions to block if they are trying to update the same rows. Since it's a transaction, either both updates will succeed or none will.

Upvotes: 1

Related Questions