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: Are your tables MyISAM or InnoDB? Remember that MyISAM isn't transactional, so
can't be rolled back. See for example [this
page](http://dev.mysql.com/doc/refman/5.1/en/ansi-diff-transactions.html) in
the MySQL documentation:

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

