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?

score 33 · Answer 1 · edited Oct 21 '22 at 17:49

33

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:

edited Oct 21 '22 at 17:49

Super Kai - Kazuya Ito

22,221
10
124
129

answered Aug 22 '14 at 16:00

Kingpin2k

47,277
10
78
96

1

if Job has a foreign key, is the table related locked too? – Olivier Pons Nov 25 '17 at 09:11
1

Generally select for updates on databases lock the particular table/row, not related rows. – Kingpin2k Dec 11 '17 at 22:39
When using several databases make sure to open the transaction on the correct one with the using parameter of transaction.atomic(). – astifter Aug 22 '23 at 13:21

score 11 · Answer 2 · edited Jun 20 '20 at 09:12

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

score 3 · Answer 3 · edited Feb 09 '23 at 05:34

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
    },
}

score -1 · Answer 4 · answered Nov 15 '20 at 12:36

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
    }
}

That changes all database behavior not only a particular piece of code. — mrroot5, Jan 11 '21 at 13:41

Django select_for_update cannot be used outside of a transaction

4 Answers4

Addendum