Connection & Transaction problem

[Jeff Watkins]

I'm not certain whether the problem I'm about to describe is in
SQLObject or in TurboGears. So I've sent this email to both lists.

I have the following model object:


hub = PackageHub("cms")
__connection__ = hub

class Article(SQLObject,ModelHelper):
slug= StringCol( alternateID=True, length=255 )
sourceFile= StringCol( alternateID=True )
# more stuff

I've created a single Article so far. When I load a fresh tg-admin
shell, and attempt to create a new transaction to manipulate the
article, the following happens:


Python 2.4.1 (#2, Mar 31 2005, 00:05:10)
[GCC 3.3 20030304 (Apple Computer, Inc. build 1666)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from cms.model import *
>>> a= Article.get(1)
>>> a._connection.transaction().begin()
Traceback (most recent call last):
File "<console>", line 1, in ?
File "/Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/SQLObject-0.7.0-py2.4.egg/sqlobject/
dbconnection.py", line 809, in begin
assert self._obsolete, "You cannot begin a new transaction
session without rolling back this one"
AssertionError: You cannot begin a new transaction session without
rolling back this one


You may well ask why I'm going through the weird hoops to get a
transaction. The answer is I'm working on a generic function which
needs to create a transaction when modifying a model object. The
above is just an example of me trying to puzzle out how SQLObject works.

Can anyone explain to me how a transaction was begun in the snippet
above?


--
Jeff Watkins
http://metrocat.org/

'I know about people who talk about suffering for the common good.
It's never bloody them! When you hear a man shouting "Forward, brave
comrades!" you'll see he's the one behind the bloody big rock and the
one wearing the only really arrow-proof helmet!'
-- Rincewind gives a speech on politics. (Terry Pratchett,
Interesting Times)

[william]

Just to be able to reproduce it, what are your declaration parameters
and which DB/connector are you using ?

Have you see that by default SQLObject enable the autoCommit?

http://www.sqlobject.org/SQLObject.html#declaring-the-class

[Krys Wilken]

Hi,

I've run into this before too.

The quick fix is to restart the cherrypy server. The longer term fix is
to have that chunk of code do a hub.rollback() if there is an exception.

It only happened to me once, though, so I just restarted and forgot
about it.

Hope this helps,
Krys

[Jeff Watkins]

I'm using SQLite3, with pretty much the default setup. In fact, I
just created a new project using quickstart, my dbiurl is:

> sqlobject.dburi="sqlite:///Users/jeff/Sites/turbo/transaction/test.db"

My entire model.py file is:

> from sqlobject import *
> from turbogears.database import PackageHub
>
> hub = PackageHub("transaction")
> __connection__ = hub
>
> class Test(SQLObject):
> s = StringCol( length=100 )
>

I then created an instance:

> miles:~/Sites/turbo/transaction> tg-admin shell

> Python 2.4.1 (#2, Mar 31 2005, 00:05:10)
> [GCC 3.3 20030304 (Apple Computer, Inc. build 1666)] on darwin
> Type "help", "copyright", "credits" or "license" for more information.
> (InteractiveConsole)

> >>> t= Test(s="foo")
> >>>

And tried to create a connection using an instance

> miles:~/Sites/turbo/transaction> tg-admin shell

> Python 2.4.1 (#2, Mar 31 2005, 00:05:10)
> [GCC 3.3 20030304 (Apple Computer, Inc. build 1666)] on darwin
> Type "help", "copyright", "credits" or "license" for more information.
> (InteractiveConsole)

> >>> t= Test.get(1)
> >>> t._connection.transaction().begin()

> Traceback (most recent call last):
> File "<console>", line 1, in ?
> File "/Library/Frameworks/Python.framework/Versions/2.4/lib/
> python2.4/site-packages/SQLObject-0.7.0-py2.4.egg/sqlobject/
> dbconnection.py", line 809, in begin
> assert self._obsolete, "You cannot begin a new transaction
> session without rolling back this one"
> AssertionError: You cannot begin a new transaction session without
> rolling back this one

On 30 Oct, 2005, at 11:45 pm, william wrote:

> Just to be able to reproduce it, what are your declaration parameters
> and which DB/connector are you using ?
>
> Have you see that by default SQLObject enable the autoCommit?
>
> http://www.sqlobject.org/SQLObject.html#declaring-the-class
>

[Ian Bicking]

Calling .transaction() gives you a transaction connection, that has to
later be used to be of any use. It's already begun, only if you
rollback or commit must you call .begin().

Here's the routine I generally use for transactions:

def do_in_transaction(func, *args, **kw):
old_conn = sqlhub.getConnection()
conn = old_conn.transaction()
sqlhub.threadConnection = conn
try:
try:
value = func(*args, **kw)
except:
conn.rollback()
raise
else:
conn.commit()
return value
finally:
sqlhub.threadConnection = old_conn


This might be a useful addition to the expose decorator. Actually, I
could probably attach the function to ConnectionHub too -- it ought to
exist somewhere in SQLObject.


--
Ian Bicking / ia...@colorstudy.com / http://blog.ianbicking.org

[Jeff Watkins]

Ian Bicking wrote:
> Calling .transaction() gives you a transaction connection, that has to
> later be used to be of any use. It's already begun, only if you
> rollback or commit must you call .begin().

That explains it. I would tend to agree with your question on lines
807-808 of dbconnection.py: I think begin should be a nop at least once.
I'd agree that multiple calls to begin should perhaps assert, but it
isn't obvious that creating a transaction automatically begins it.
Especially when there is an explicit begin method.