CODEWITHSUNDEEP
djangopostgresql
Patrik Beck
Patrik Beck

Reputation: 2505

Django/postgres transaction isolation level setting ignored

I have an issue setting transaction isolation level. I want the most strict serializable, while the default is read committed.

I am using:

Django==1.10.6
psycopg2==2.5.1

Running on heroku.

Based on the documentation: https://docs.djangoproject.com/en/1.10/ref/databases/#isolation-level

I have the following settings.py:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    },
    'OPTIONS': {
        'isolation_level': psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE,
    }
}

if on_heroku:
    DATABASES['default'] = dj_database_url.config()

Here's the view code:

@require_http_methods(["POST"])
@transaction.atomic
@login_required()
def api_test_add_one(request):
    cursor = connection.cursor()

    cursor.execute('SHOW default_transaction_isolation')

    logger.info("aa: " + str(cursor.fetchone()))

    return HttpResponse("{}", content_type="application/json")

The output is:

aa: (u'read committed',)

I have run different tests simultaneously accessing the same endpoint, incrementing an integer in DB and confirmed that transactions were not isolated.

Upvotes: 5

Views: 1072

Answers (1)

Alasdair
Alasdair

Reputation: 308849

The OPTIONS belongs inside the database's settings, e.g.

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgres',
        ...
        'OPTIONS': {
            'isolation_level': psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE,
        },
    },
}

In your case, you replace DATABASES['default'], so your OPTIONS set there would be lost:

if on_heroku:
    DATABASES['default'] = dj_database_url.config()

Instead, you can set OPTIONS after setting DATABASES['default'].

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    },
}

if on_heroku:
    DATABASES['default'] = dj_database_url.config()
    DATABASES['default']['OPTIONS'] = {
        'isolation_level': psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE,
    }

Upvotes: 6

Related Questions