[Django]-How to set "REPEATABLE READ" for a transaction In Django?

by

23👍

✅

You’re right, default transaction isolation level in postgres is READ COMMITTED.
You can easily change it in settings to test whether it would fit your needs:
https://docs.djangoproject.com/en/1.8/ref/databases/#isolation-level

Also I doubt you will face some performance issues because postgres operates very efficiently while working with transactions. Even in SERIALIZABLE mode. Also mysql has REPEATABLE READ default isolation level and as we see it doesn’t hurt performance too.

Anyway you can set isolation mode manually whenever you need like this:
http://initd.org/psycopg/docs/extensions.html#isolation-level-constants

To set custom transaction isolation level you can try smth like:

from django.db import connection

with transaction.atomic():
    cursor = connection.cursor()
    cursor.execute('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ')
    # logic

Also I would suggest you to change default mode in settings first (if you can).
Then if will fit your needs you can remove it and modify code in special places.

👤alTus

1👍

django-pgtransaction offers an extension of the django.db.transaction.atomic context manager/decorator which allows one to dynamically set the isolation level when opening a transaction, as well as specifying a retry policy for when an operation in that transaction results in a Postgres locking exception:

@pgtransaction.atomic(isolation_level=pgtransaction.SERIALIZABLE, retry=3)
def do_queries():
    # Do queries...
👤Paul Gilmartin

0👍

There are 2 ways to set REPEATABLE READ in Django.

<The 1st way (My recommendation)>

You can run the raw query to set isolation level after database settings in settings.py as shown below. *If running the raw query before database settings, error occurs:

# "settings.py"

from django.db import connection

# ...

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

# ↓ ↓ ↓ Set isolation level ↓ ↓ ↓

cursor = connection.cursor()
query = """
        ALTER DATABASE postgres 
        SET DEFAULT_TRANSACTION_ISOLATION 
        TO 'REPEATABLE READ';
        """
cursor.execute(query)

# ↓ ↓ ↓ Check isolation level ↓ ↓ ↓

cursor.execute('SHOW default_transaction_isolation;')
print(cursor.fetchone()) # ('repeatable read',)

*settings.py is run every time Django Server is run with the command below or every time Django Server is reloaded by writing code so transaction is set every time Django Server is run with the command below or every time Django Server is reloaded by writing code:

python manage.py runserver 0.0.0.0:8000

<The 2nd way>

You can directly set REPEATABLE READ with psql as shown below:

postgres=# ALTER DATABASE postgres SET DEFAULT_TRANSACTION_ISOLATION TO 'REPEATABLE READ';

Actually, what the documentation explains as shown below doesn’t work for me with Django 3.2.16 on Windows 11. That’s why I show the 2 ways above:

# "settings.py"

import psycopg2.extensions

DATABASES = {
    'default':{
        'ENGINE':'django.db.backends.postgresql',
        'NAME':'postgres',
        'USER':'postgres',
        'PASSWORD':'admin',
        'HOST':'localhost',
        'PORT':'5432',
    },
    'OPTIONS': { # ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ Doesn't work ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓
        'isolation_level': psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE,
    },
}
👤Super Kai – Kazuya Ito

Leave a comment