Disable autocommit during hundreds of MySQL UPDATE statements in Django program

Question

In a Django program, how to explicitly disable auto transaction management before hundreds of UPDATEs and enable it after the UPDATEs finish?

I looked into http://docs.djangoproject.com/en/dev/topics/db/transactions/ but didn't find any clue.

I tried to put the following code at the beginning

settings.DISABLE_TRANSACTION_MANAGEMENT = True

I also tried

cursor = connection.cursor()
cursor.execute('SET SESSION autocommit = 0;')
...
UPDATE
...
cursor.execute('SET SESSION autocommit = 1;')

Neither methods above improved the updating speed. Is there anything wrong with above codes?

Answer 1

from django.db import transaction

@transaction.commit_on_success
def my_function_that_does_thousands_of_updates():

    # Do whatever you want here
    transaction.set_dirty()

This will let you run whatever SQL you want to run, and then only commit if there are no exceptions. The set_dirty() call is required if you're using a manual cursor as in your example, but won't be required if you just use the Django ORM (if I'm not mistaken; this is 1.2 behaviour at least).

To have full control over the transactions, you can use the transaction.commit_manually decorator. As for your speed issue, I can't comment.

The django docs explain this pretty well: http://docs.djangoproject.com/en/dev/topics/db/transactions/#django.db.transaction.commit_on_success