1

From what I read in the PostgreSQL documentation, it seems that queries like

UPDATE accounts SET balance = balance + 100 WHERE account_id = 12345

are atomic and can be safely executed concurrently. Is this only the case when a single integer column is updated? In my Django app I have a query like this (generated by the ORM): 

UPDATE "mytable" SET "counter" = ("mytable"."counter" + 1), 
  "owner" = NULL, "updated" = '2018-04-12T12:53:17.826257+00:00'::timestamptz
WHERE "mytable"."id" = 27; args=(1, datetime.datetime(2018, 4, 12, 12, 53, 17, 826257, tzinfo=<UTC>), 27)

which is executed concurrently by multiple background jobs. Will the counter be updated atomically in this case? I'm using the autocommit mode (Django default).

planetp
  • 14,248
  • 20
  • 86
  • 160
  • Counter will be updated atomically anyway, either in explicit or implicit transaction (autocommit). Some info is here https://www.postgresql.org/docs/current/static/sql-begin.htm – Eugene Apr 12 '18 at 23:49
  • The question was more about whether the query would be executed concurrently by multiple background jobs **without race conditions**. It's not clear from the docs and other similar [questions](https://stackoverflow.com/q/40162952/275088) on SE (they use statements that update a single column). – planetp Apr 15 '18 at 07:43

1 Answers1

0

What you might need is an F Expression: https://docs.djangoproject.com/en/2.1/ref/models/expressions/#f-expressions

From the docs: Another useful benefit of F() is that having the database - rather than Python - update a field’s value avoids a race condition.

from django.db.models import F
instance = MyModel.objects.get(pk=xxx)
instance.counter = F('instance.counter') + 1
instance.owner = null
instance.save()

Reload instance to get updated values from db.

FriC
  • 786
  • 10
  • 14