Django transaction isolation level in mysql & postgresql

Question

Do you know the default isolation level of the transactions used in Django? Is it possible to set the isolation level in the database independent way?

I'm mainly interested in mysql and postgres.

Disadvantage

Answer 1

The isolation level isn't changed by mysql drivers so it depends on the server's default isolation level.

Disadvantage

Answer 2

You can also change this per client / session using the django database options, like this:

DATABASE_OPTIONS = { "init_command": "SET storage_engine=INNODB, SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED", }

Macrogamete

Someone can confirm that this runs? I have problems with repeatable read: #2235818 – Impart

@danihp definitely runs, although the syntax is is off. I'm using SET storage_engine=INNODB; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; instead – Pilchard

Answer 3

At the moment django does not set the isolation level. This is a bug, because of django does not work properly with any higher isolation level than READ COMMITTED. But MySQL is using REPEATABLE READ by default. There are plans to add a setting to set the isolation level (#14026) and a discussion about making READ COMMITTED the default (#13906). I have also written a detailed article about MySQL transactions and django.

Muddy

Thanks, but the blog you've linked to seems to be private. Could you open that article, or copy/paste here? – Anatola

Can you pls cite some info or elaborate about "django does not work properly with any higher isolation level than READ COMMITTED". – Anatola

@Sebastian Noack The article says 'Permission Needed'? – Curtis

I found this article that refers to this private article and actually has content you can read: ewencp.org/blog/django-and-mysql-isolation-levels – Mickens

I believe it has to do with how Django used to emulate auto-commit instead of using database level auto-commit. This has been fixed in Django 1.6: docs.djangoproject.com/en/1.6/topics/db/transactions/… – Selfassurance

Answer 4

According to my test, Django's default isolation level depends on the isolation level which you set for your database. In other words, if you set SERIALIZABLE for your PostgreSQL or MySQL, Django's default isolation level is SERIALIZABLE.

And, to set SERIALIZABLE for PostgreSQL, I tried what the documentation explains in settings.py in Django 3.2.16 on Windows 11 as shown below but it didn't work so the isolation level is still READ COMMITTED:

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

But, I could set and check SERIALIZABLE by running the raw queries in settings.py for PostgreSQL as shown below. *Run the raw queries after DATABASES in settings.py:

# "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 'SERIALIZABLE';
        """
cursor.execute(query)

# ↓ ↓ ↓ Check isolation level ↓ ↓ ↓

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

*Or, you can directly set SERIALIZABLE with psql as shown below:

postgres=# ALTER DATABASE postgres SET DEFAULT_TRANSACTION_ISOLATION TO 'SERIALIZABLE';

And for MySQL, you will be able to set and check SERIALIZABLE by running the raw queries in settings.py as shown below:

# "settings.py"

from django.db import connection

# ...

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql', 
        'NAME': 'DB_NAME',
        'USER': 'DB_USER',
        'PASSWORD': 'DB_PASSWORD',
        'HOST': 'localhost',
        'PORT': '3306',
    }
}

# ↓ ↓ ↓ Set isolation level ↓ ↓ ↓

cursor = connection.cursor()
query = """
        SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
        """
cursor.execute(query)

# ↓ ↓ ↓ Check isolation level ↓ ↓ ↓

cursor.execute('SELECT @@GLOBAL.transaction_isolation;')
print(cursor.fetchone()) # ('serializable',)

*Or, you can directly set SERIALIZABLE with MySQL client as shown below:

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Bielefeld

Answer 5

(Sorry, i can't comment for Danhip) This solution wotked for me (mySQL), I added Peter's code in the DATABASE field:

DATABASES = {
    'default': {
        (...)
        'OPTIONS': {
            (...),
            "init_command": "SET storage_engine=INNODB, SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED"
        },
     }
}

Ge

Answer 6

if you want to use read uncommited isolation level.

you add the 'isolation_level': 'read uncommitted' in 'OPTIONS on database connection configuration.

DATABASES = {
    'read-uncommited': {
        'OPTIONS': {
            'isolation_level': 'read uncommitted'
        },
    },
}

you can find from https://docs.djangoproject.com/en/2.1/ref/databases/

Balata