CODEWITHSUNDEEP
pythondjangopostgresqlpsycopg2
Genarito
Genarito

Reputation: 3433

Django transaction doesn't work with raw SQL queries

Because I'm tired of Django DB driver implicit behaviour I've the following code with a plain Postgres transaction:

from django.db import transaction, connection
from concurrent.futures.thread import ThreadPoolExecutor

def commit_or_rollback(commit: bool):
    query = "COMMIT" if commit else "ROLLBACK"
    with connection.cursor() as cursor:
        cursor.execute(query)

def insert(query, parameter):
    with connection.cursor() as cursor:
        cursor.execute(query, parameter)

def insert_with_threads():
    insert_pool = ThreadPoolExecutor(max_workers=4)
    query = 'INSERT INTO a_table VALUES (%s)'
    parameters = [...]
    for parameter in parameters:
        insert_pool.submit(insert, query, parameter)

# "Main" program
try:
    with connection.cursor() as cursor:
        cursor.execute("BEGIN")

    insert_with_threads()  # Could raise any of the caught exceptions

except CustomException1:
    commit_or_rollback(False)
except CustomException2:
    commit_or_rollback(False)
except Exception as e:
    commit_or_rollback(False)
    # Some general stuff
else:
    commit_or_rollback(True)

But nothing is rolled back, I've tried with transaction.atomic(), set_autocommit(), set_autocommit() and raw BEGIN but nothing. Is threading breaking things? Is a problem with opening multiple cursors?

Any kind of help would be really appreciated

Upvotes: 1

Views: 1029

Answers (1)

Genarito
Genarito

Reputation: 3433

Definitely it was a problem with multithreading (I was using ThreadPoolExecutor). Now I'm using a ProcessPoolExecutor and works as expected

Upvotes: 1

Related Questions