MySql autocommit and Django
Amit Ramon
I'm writing a custom-sql method that uses SQL "select for update" for updating a table row. The table engine is InnoDB. I'm using Django svn and MySQL 5.0. The MySql default for autocommit is 1, i.e. enabled.
The method I'm using looks somewaht like this:
def get_next_index(id):
from django.db import connection
cursor = connection.cursor()
# following 3 lines are only for testing autocommit's value
cursor.execute("select @@autocommit")
row = cursor.fetchone()
print "value of autocommit: ", row[0]
sql_sel = "SELECT last_index FROM my_table WHERE id = %s FOR UPDATE"
cursor.execute(sql_sel, [id])
row = cursor.fetchone()
# more processing....
cursor.execute("commit")
The table (or row) should be locked by the above code until calling "commit", which is what I want, but *only* when autocommit is 0 (disabled). My database uses default settings for autocommit, i.e. it is enabled. However, it seems that Django itself disables autocommit. (the three lines in the code above that retrive the value of autocommit are their only to approve this.) I couldn't find anywhere in the documentation that autocommit is disabled. I also looked at Django's db code but could'nt easily find a hint. Again, this is, for me, a desired behaviour, but I want to know whether this is the official behaviour, to know if can count on it (otherwise I'll have to disable autocommit myself).
I would appreciate if anyobe here could approve or disprove this behaviour, or give any explanation: does Django disable autocommit when the above code is executed? or does it always disable it?
Thanks,
Amit
Malcolm Tredinnick
On Thu, 2008-03-06 at 13:50 +0200, Amit Ramon wrote:
[...]
> I would appreciate if anyobe here could approve or disprove this behaviour, or give any explanation:
It's a requirement of the Python DB-API 2.0 (PEP 249) that auto-commit
behaviour is turned off when a connection is opened. This is done by (in
this case) the MySQLdb Python wrapper.
malcolm
--
Save the whales. Collect the whole set.
http://www.pointy-stick.com/blog/