SQLObject transaction rollback not working

[jacob.miles]

Hello. I'm trying to wrap a function call in a transaction, but when I
intentionally throw an exception in the middle of the function it
doesn't actually roll back the transaction. The debug output says
1/ROLLBACK, without any 1/COMMITs in there, but when I view the data in
the command-line mysql utility the changes have been made.

This is the code I'm using to connect to the mysql database and to wrap
the function call in a transaction. After that I've invluded the
testUpdate method I'm using, and after that the python conversation
that ensued. Does anyone see what I'm doing wrong?

--- sqlutil.py:

from sqlobject import *

def connect():
""" Connects SQLObject to the dev database on localhost.
"""
connectionString =
"mysql://admin@localhost/mc_image_library_dev?debug=1"
connection = connectionForURI (connectionString)
sqlhub.processConnection = connection


def wrapInTransaction (func, *args, **kw):
""" Got this from the SQLObject mailing list.
Calls the given func with the given args and keyword assignments
within a db transaction. Rolls back if an exception is thrown,
otherwise commits.
"""
old_conn = sqlhub.getConnection()
conn = old_conn.transaction()
sqlhub.processConnection = conn
try:
try:
value = func(*args, **kw)
except:
conn.rollback()
raise
else:
conn.commit()
return value
finally:
sqlhub.processConnection = old_conn

------------------
----- test.py:

from ImageCategory import *

def testUpdate (newName, username, fail):
category = ImageCategory.get(1)
category.name = newName
category.updateLastChanged (username)
if fail:
raise Exception ('spam', 'eggs')

-----------------
------ The python conversation:
1/QueryOne: SELECT last_changed_by, last_changed_date, name FROM
image_category WHERE id = 1
1/Query : UPDATE image_category SET name = 'Animals' WHERE id = 1
1/Query : UPDATE image_category SET last_changed_by = 'jake' WHERE
id = 1
1/Query : UPDATE image_category SET last_changed_date = '2005-11-29
00:36:22' WHERE id = 1
1/ROLLBACK:
Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "sqlutil.py", line 22, in wrapInTransaction
value = func(*args, **kw)
File "test.py", line 8, in testUpdate
raise Exception ('spam', 'eggs')
Exception: ('spam', 'eggs')

------------

After all this, the mysql utility shows that the update did take
effect.

Any thoughts?

- Jake

 

[Dennis Lee Bieber]

This is the code I'm using to connect to the mysql database and to wrap
the function call in a transaction. After that I've invluded the

Is there any possibility that your MySQL is running in "auto-commit"
mode?
--

 

[Magnus Lycka]

Does anyone see what I'm doing wrong?

Using MySQL? Are you aware that MySQL doesn't support transaction
handling with COMMIT and ROLLBACK in all configurations. It depends
on your MySQL version and what table backend you are using.

The Python DB-API states that autocommit should be turned off by
default, so if your tables support that, it should work right in
MySQLdb. SQLObject on the other hand, turns autocommit on by
default, so that could be the cuplrit if the problem is that you
didn't read the SQLObject docs...

See
http://www.sqlobject.org/SQLObject.html#declaring-the-class
"Parameters are: debug (default: False), debugOutput (default: False),
cache (default: True), autoCommit (default: True), debugThreading
(default: False)."

You might need to turn off both cache and autocommit to get things
to work right.