Django select_for_update cannot be used outside of a transaction

Question

I was using Django 1.5.1 and upgraded to Django 1.6.6.

In Django 1.5.1, I was using select for update to guarantee atomic execution.

# "views.py"

from django.db import transaction

def some_method():    
    job_qs = Job.objects.select_for_update().filter(pk=job.id)
    for job in job_qs:

Unfortunately this now throws an error:

  File "/srv/venvs/django-picdoc/local/lib/python2.7/site-packages/django/db/models/query.py", line 96, in __iter__
    self._fetch_all()

  File "/srv/venvs/django-picdoc/local/lib/python2.7/site-packages/django/db/models/query.py", line 857, in _fetch_all
    self._result_cache = list(self.iterator())

  File "/srv/venvs/django-picdoc/local/lib/python2.7/site-packages/django/db/models/query.py", line 220, in iterator
    for row in compiler.results_iter():

  File "/srv/venvs/django-picdoc/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 713, in results_iter
    for rows in self.execute_sql(MULTI):

  File "/srv/venvs/django-picdoc/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 776, in execute_sql
    sql, params = self.as_sql()

  File "/srv/venvs/django-picdoc/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 147, in as_sql
    raise TransactionManagementError("select_for_update cannot be used outside of a transaction.")

TransactionManagementError: select_for_update cannot be used outside of a transaction.

What are some of the solutions for solving this?

Boettcher

Answer 1

The answer is in the error, wrap the query in a transaction

Django's documentation is located here: https://docs.djangoproject.com/en/dev/topics/db/transactions/#django.db.transaction.atomic

One approach is:

# "views.py"

from django.db import transaction

def some_method():    
    with transaction.atomic():
        job_qs = Job.objects.select_for_update().filter(pk=job.id)
        for job in job_qs:

Boettcher

if Job has a foreign key, is the table related locked too? – Samora

Generally select for updates on databases lock the particular table/row, not related rows. – Boettcher

When using several databases make sure to open the transaction on the correct one with the using parameter of transaction.atomic(). – Dordrecht

Answer 2

Addendum

As of Django 2.0, related rows are locked by default (not sure what the behaviour was before) and the rows to lock can be specified in the same style as select_related using the of parameter:

By default, select_for_update() locks all rows that are selected by the query. For example, rows of related objects specified in select_related() are locked in addition to rows of the queryset’s model. If this isn’t desired, specify the related objects you want to lock in select_for_update(of=(...)) using the same fields syntax as select_related(). Use the value 'self' to refer to the queryset’s model.

https://docs.djangoproject.com/en/dev/ref/models/querysets/#select-for-update

Urbani

Answer 3

select_for_update() must be run in a transaction.

So, use @transaction.atomic for the view as shown below:

# "views.py"

from django.db import transaction

@transaction.atomic # Here
def some_method():    
    with transaction.atomic():
        job_qs = Job.objects.select_for_update().filter(pk=job.id)
        for job in job_qs:

Or, use with transaction.atomic(): in the view as shown below:

# "views.py"

from django.db import transaction

def some_method():    
    with transaction.atomic(): # Here
        job_qs = Job.objects.select_for_update().filter(pk=job.id)
        for job in job_qs:

Or, set 'ATOMIC_REQUESTS': True to database settings in settings.py as shown below:

# "settings.py"

DATABASES = {
    'default':{
        'ENGINE':'django.db.backends.postgresql',
        'NAME':'postgres',
        'USER':'postgres',
        'PASSWORD':'admin',
        'HOST':'localhost',
        'PORT':'5432',
        'ATOMIC_REQUESTS': True, # Here
    },
}

Bloodfin

Answer 4

For me it happened even when using with transaction.atomic():. The problem was that we didn't set 'ATOMIC_REQUESTS': True in the settings.py file. Now this solved the problem.

As documented here: https://docs.djangoproject.com/en/3.1/topics/db/transactions/

"Set ATOMIC_REQUESTS to True in the configuration of each database for which you want to enable this behavior."

So in settings.py we added:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': os.environ['DB_NAME'],
        'USER': os.environ['DB_USER'],
        'PASSWORD': os.environ['DB_PASSWORD'],
        'HOST': os.environ['DB_HOST'],
        'PORT': '3306',
        'ATOMIC_REQUESTS': True
    }
}

Baste

That changes all database behavior not only a particular piece of code. – Cambyses