Cannot turn off autocommit in a script using the Django ORM

Question

I have a command line script that uses the Django ORM and MySQL backend. I want to turn off autocommit and commit manually. For the life of me, I cannot get this to work. Here is a pared down version of the script. A row is inserted into testtable every time I run this and I get this warning from MySQL: "Some non-transactional changed tables couldn't be rolled back".

#!/usr/bin/python

import os
import sys

django_dir = os.path.abspath(os.path.normpath(os.path.join(os.path.dirname(__file__), '..')))
sys.path.append(django_dir)

os.environ['DJANGO_DIR'] = django_dir
os.environ['DJANGO_SETTINGS_MODULE'] = 'myproject.settings'

from django.core.management import setup_environ
from myproject import settings

setup_environ(settings)

from django.db import transaction, connection
cursor = connection.cursor()
cursor.execute('SET autocommit = 0')
cursor.execute('insert into testtable values (\'X\')')
cursor.execute('rollback')

I also tried placing the insert in a function and adding Django's commit_manually wrapper, like so:

@transaction.commit_manually
def myfunction():
    cursor = connection.cursor()
    cursor.execute('SET autocommit = 0')
    cursor.execute('insert into westest values (\'X\')')
    cursor.execute('rollback')

myfunction()

I also tried setting DISABLE_TRANSACTION_MANAGEMENT = True in settings.py, with no further luck. I feel like I am missing something obvious. Any help you can give me is greatly appreciated. Thanks!

Answer 1

Are your tables MyISAM or InnoDB? Remember that MyISAM isn't transactional, so can't be rolled back. See for example this page in the MySQL documentation:

In transactional terms, MyISAM tables effectively always operate in autocommit = 1 mode.