Inactive transactions and commit/rollback from Session

[Hans Lellelid]

Hi -

I'm using SA in conjunction w/ Pylons, in their default (0.9.7)
approach -- i.e. using scoped session classes created by calling
something like:

sm = orm.sessionmaker(autoflush=True, autocommit=False, bind=engine)
meta.Session = orm.scoped_session(sm)

I have a base controller that is designed to handle any session
"cleanup" -- i.e. do any pending commits and rollback and then remove
the session.

This is mostly working; however, I'm getting errors about "The
transaction is invalid" when attempting to perform commits. I'm not
sure that this is actually resulting in an error -- and while I think
a refactor is in order here anyway, I would like to better understand
what I'm doing wrong. :)

Here's my base controller:

class CommitDisposeBaseController(WSGIController):
""" A base controller class that automatically commits any
outstanding SA changes, closes (removes) the SA Session,
and disposes of the engine (or pool, if applicable). """
def __call__(self, environ, start_response):
try:
result = WSGIController.__call__(self, environ,
start_response)
if meta.Session.is_active:
meta.Session.commit()
return result
except Exception, e:
if meta.Session.is_active:
meta.Session.rollback()
_log.exception(e)
raise
finally:
meta.Session.remove()
meta.engine.dispose()

The errors ("The transaction is inactive.") are coming from my
meta.Session.commit() line. Two questions:

1) Isn't this what meta.Session.is_active should be testing for?

2) Is there something special about using these methods as "class
methods" instead of instance methods? My assumption is that this
transparently grabs the current/active Session instance and executes
on that. I do know, though, that some methods such as remove() are
not available on instances -- only on the class. This is a bit
confusing.

Any help would be appreciated.

Thanks,
Hans

[Michael Bayer]

Hans Lellelid wrote:
>
>
> The errors ("The transaction is inactive.") are coming from my
> meta.Session.commit() line. Two questions:
>
> 1) Isn't this what meta.Session.is_active should be testing for?

it is. I'm not familiar with a codepath which can cause that to happen,
and in fact even if the session isn't active, it still should be totally
fine to call commit(). Try this as an example:

from sqlalchemy import *
from sqlalchemy.orm import *

engine = create_engine("mysql://scott:tiger@localhost/test", echo=True)

s = scoped_session(sessionmaker(bind=engine))

s.commit()
s.rollback()
s.commit()
s.commit()
s.rollback()
s.rollback()
s.rollback()
s.commit()
s.commit()

It seems like some unhandled exception on the connection itself might have
occurred, i.e. after acquiring it via session.connection(), and the
session wasn't notified of this failure (i.e. via rollback()).

I don't ever use is_active myself, its something we put there for the
benefit of TurboGears. To my mind its really not needed for anything.
My recommendation would be to not call is_active() and to only call
commit() within controller methods as needed. you also can call
rollback() any number of times so the is_active() isnt needed there.

>
> 2) Is there something special about using these methods as "class
> methods" instead of instance methods? My assumption is that this
> transparently grabs the current/active Session instance and executes
> on that.

this is the expected usage if the session is a scoped_session() which is
the case in a pylons setup.

> I do know, though, that some methods such as remove() are
> not available on instances -- only on the class. This is a bit
> confusing.

remove() is a method on scoped_session() which is not a session. It is a
thread local container object with proxying behavior. If it makes it
less confusing, you can use scoped_session like this:

# create session registry
my_scoped_session = scoped_session(sessionmaker())

# acquire the session from the registry
session = my_scoped_session()

# use the session
session.commit()

# remove "session" from the current scope
my_scoped_session.remove()

After using the above pattern for about five minutes, you'll probably see
the utility of scoped_session() proxying common methods to the underlying
session.

[Hans Lellelid]

Hi Michael -

Thanks for the response!

> > 1) Isn't this what meta.Session.is_active should be testing for?
>
> it is.   I'm not familiar with a codepath which can cause that to happen,
> and in fact even if the session isn't active, it still should be totally
> fine to call commit().  Try this as an example:

> <snip>

Ok -- I'm glad that this is the case; this does conform with what I'd
seen in other cases in the past. Indeed -- I also noticed that
is_active seemed to be unnecessary.

> It seems like some unhandled exception on the connection itself might have
> occurred, i.e. after acquiring it via session.connection(), and the
> session wasn't notified of this failure (i.e. via rollback()).

Thank you; that's a helpful direction. Indeed in this particular
case, I had passed the exception (from session.connection()) into a
class which performed a number of operations. While that was supposed
to be an atomic unit, perhaps an exception is not being properly
handled in there.

> > I do know, though, that some methods such as remove() are
> > not available on instances -- only on the class.  This is a bit
> > confusing.
>
> remove() is a method on scoped_session() which is not a session.  It is a
> thread local container object with proxying behavior.   If it makes it
> less confusing, you can use scoped_session like this:
>
> # create session registry
> my_scoped_session = scoped_session(sessionmaker())
>
> # acquire the session from the registry
> session = my_scoped_session()
>
> # use the session
> session.commit()
>
> # remove "session" from the current scope
> my_scoped_session.remove()

Yeah, actually, I think that is a clearer convention. Since we
typically fetch instances and work with them (rather than using
meta.Session.query(), for example), I think I will adopt this naming
convention.

Thanks again!
Hans