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?

Spectacles

You do know this only works for InnoDB tables (since they support transactions contrary to MyISAM tables i think) in MySQL, but I guess you've read that in docs already. – Pollak

@rebus, yes, it's a InnoDB table. – Spectacles

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

Supereminent

Answer 2

If you're only doing hundreds of updates and not tens of thousands, maybe the speed issue isn't due to writing the data, but due to finding it rather. i.e. perhaps there's a where clause in the update statement and it takes a while to find the correct row to update. If that's the case then turning off autocommit wouldn't help - you'd need an index on the field in your where clause.

How many rows are in the table? What does the update statement look like?

You could also try prepared statements, but for under a thousand updates it shouldn't make much of a difference.

Ballon