The transaction is inactive due to a rollback in a subtransaction
Antoine Pitrou
I'm getting the following error in one of my unit tests, while I have
never opened a subtransaction. The only thing that happens in that
test is that a first db_session.flush() raises an IntegrityError (this
is deliberate, because I test the generation of an unique number).
Then a second db_session.flush() succeeds, but for some reason the
subsequent commit raises this weird error.
Could someone clarify why sqlalchemy thinks I've got a subtransaction
in there?
======================================================================
ERROR: XXX.tests.test_logement.TestLogement.test_generer_decharge
----------------------------------------------------------------------
Traceback (most recent call last):
File "/usr/lib/python2.6/site-packages/nose-0.10.4-py2.6.egg/nose/
case.py", line 182, in runTest
self.test(*self.arg)
File "/home/antoine/ftth/XXX/XXX/tests/test_logement.py", line 92,
in test_generer_decharge
db_session.commit()
File "/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/
sqlalchemy/orm/scoping.py", line 121, in do
return getattr(self.registry(), name)(*args, **kwargs)
File "/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/
sqlalchemy/orm/session.py", line 673, in commit
self.transaction.commit()
File "/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/
sqlalchemy/orm/session.py", line 378, in commit
self._prepare_impl()
File "/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/
sqlalchemy/orm/session.py", line 351, in _prepare_impl
self._assert_is_active()
File "/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/
sqlalchemy/orm/session.py", line 247, in _assert_is_active
"The transaction is inactive due to a rollback in a "
InvalidRequestError: The transaction is inactive due to a rollback in
a subtransaction. Issue rollback() to cancel the transaction.
----------------------------------------------------------------------
Michael Bayer
On Jul 12, 2009, at 3:19 PM, Antoine Pitrou wrote:
>
> Hi,
>
> I'm getting the following error in one of my unit tests, while I have
> never opened a subtransaction. The only thing that happens in that
> test is that a first db_session.flush() raises an IntegrityError (this
> is deliberate, because I test the generation of an unique number).
> Then a second db_session.flush() succeeds, but for some reason the
> subsequent commit raises this weird error.
>
> Could someone clarify why sqlalchemy thinks I've got a subtransaction
> in there?
the session always does things in a transaction , as does any DBAPI
connection running in the default mode of operation as according to
spec. Whether or not the Session leaves the transaction open after
an individual execute() or flush() operation is dependent on the
"autocommit" setting, which defaults to False - meaning the Session
remains open within a transaction as soon as its used, which remains
until either rollback(), commit(), or close() is called. After any
flush() which fails, session.rollback() must be called. the
subtransaction is specifically the internal begin/rollback() pair
issued by the failed flush() call. Start reading from http://www.sqlalchemy.org/docs/05/session.html#flushing
on downwards to get more detail about how this works.
Antoine Pitrou
>
> the session always does things in a transaction , as does any DBAPI
> connection running in the default mode of operation as according to
> spec. Whether or not the Session leaves the transaction open after
> an individual execute() or flush() operation is dependent on the
> "autocommit" setting, which defaults to False - meaning the Session
> remains open within a transaction as soon as its used, which remains
> until either rollback(), commit(), or close() is called. After any
> flush() which fails, session.rollback() must be called. the
> subtransaction is specifically the internal begin/rollback() pair
weird and difficult to grasp behaviour. Why doesn't a failed flush()
leave the session in a consistent state (either rolled back or not,
but not something in-between)?
By the way, I solved my problem by doing the UPDATE with an ORM-less
query, so that I can catch the failure without rolling back the
transaction. Perhaps flush() should have an optional flag to avoid
rolling back on errors? This would help the cases where potential
errors are expected, and the developer knows how to correct them.
Michael Bayer
On Jul 12, 2009, at 5:57 PM, Antoine Pitrou wrote:
>
> Ok, thanks. Then I want to ask: why the complication? It looks like a
> weird and difficult to grasp behaviour. Why doesn't a failed flush()
> leave the session in a consistent state (either rolled back or not,
> but not something in-between)?
here is why.
the usage of the session should look like this:
try:
<use session>
session.commit()
except:
session.rollback()
finally:
session.remove() # optional, depends on use case
many things can cause a failure within the try/except besides
flushes. You should always have some kind of "framing" of your
session operations so that connection and transaction resources have a
definitive boundary, otherwise your application doesn't really have
its usage of resources under control. This is not to say that you
need to put try/except blocks all throughout your application - on the
contrary this would be a terrible idea. You should architect your
application such that there is one (or few) point(s) of "framing"
around session operations. The common example is a web framework
where the above framing takes place in the BaseController class.
Now the second part. The Session supports "framing" above within
multiple levels. Such as, suppose you had a decorator
@with_session(), which did this:
def with_session(fn):
def go(*args, **kw):
session.begin(subtransactions=True)
try:
ret = fn(*args, **kw)
session.commit()
return ret
except:
session.rollback()
return go
the above decorator begins a transaction if one does not exist
already, and then commits it, if it were the creator. The
"subtransactions" flag means that if begin() were already called by an
enclosing funciton, nothing happens except a counter is incremented -
this counter is decremented when commit() is called and only when it
goes back to zero does the actual COMMIT happen. It allows this usage
pattern:
@with_session
def one():
# do stuff
two()
@with_session
def two():
# etc.
one() can call two(), or two() can be called by itself, and the
@with_session decorator ensures the appropriate "framing" - the
transaction boundaries stay on the outermost call level. As you can
see, if two() calls flush() which throws an exception and then issues
a rollback(), there will *always* be a second rollback() performed by
the decorator, and possibly a third corresponding to two levels of
decorator. If the flush() pushed the rollback() all the way out to
the top of the stack, and then we said that all remaining rollback()
calls are moot, there is some silent behavior going on there. A
poorly written enclosing method might suppress the exception, and then
call commit() assuming nothing is wrong, and then you have a silent
failure condition. The main reason people get this error in fact is
because they didn't write clean "framing" code and they would have had
other problems down the road.
If you think the above use case is a little exotic, the same kind of
thing comes into play if you want to SAVEPOINT- you might call
begin_nested() several times, and the commit()/rollback() calls each
resolve the most recent begin_nested(). The meaning of rollback()
or commit() is dependent upon which enclosing block it is called, and
you might have any sequence of rollback()/commit() in any order, and
its the level of nesting that determines their behavior.
In both of the above cases, if flush() broke the nesting of
transaction blocks, the behavior is, depending on scenario, anywhere
from "magic" to silent failure to blatant interruption of code flow.
flush() makes its own "subtransaction", so that a transaction is
started up regardless of the external transactional state, and when
complete it calls commit(), or rollback() upon failure - but that
rollback() corresponds to its own subtransaction - it doesn't want to
guess how you'd like to handle the external "framing" of the
transaction, which could be nested many levels with any combination of
subtransactions and real SAVEPOINTs. The job of starting/ending the
"frame" is kept consistently with the code external to the flush(),
and we made a decision that this was the most consistent approach.
>
> By the way, I solved my problem by doing the UPDATE with an ORM-less
> query, so that I can catch the failure without rolling back the
> transaction. Perhaps flush() should have an optional flag to avoid
> rolling back on errors?
It would be great if flush() could partially complete and then not
roll back, however this is beyond its current capabilities since its
internal bookkeeping would have to be modified such that it can be
halted at any time and be exactly consistent with what's been flushed
to the database. While this is theoretically possible, the
usefulness of the enhancement is greatly decreased by the fact that
many database operations require a ROLLBACK in any case. Postgres in
particular has operations which, once failed, the transaction is not
allowed to continue:
test=> create table foo(id integer primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
test=> begin;
BEGIN
test=> insert into foo values(1);
INSERT 0 1
test=> commit;
COMMIT
test=> begin;
BEGIN
test=> insert into foo values(1);
ERROR: duplicate key value violates unique constraint "foo_pkey"
test=> insert into foo values(2);
ERROR: current transaction is aborted, commands ignored until end of
transaction block
What SQLAlchemy offers that solves both issues is support of
SAVEPOINT, via begin_nested(). Using begin_nested(), you can frame
an operation that may potentially fail within a transaction, and then
"roll back" to the point before its failure while maintaining the
enclosing transaction.
Antoine Pitrou
Thanks for the lengthy (!) explanation.
On Jul 13, 1:05 am, Michael Bayer <mike...@zzzcomputing.com> wrote:
> Postgres in
> particular has operations which, once failed, the transaction is not
> allowed to continue:
SQLite but the production backend is PostgreSQL-based).
> What SQLAlchemy offers that solves both issues is support of
> SAVEPOINT, via begin_nested(). Using begin_nested(), you can frame
> an operation that may potentially fail within a transaction, and then
> "roll back" to the point before its failure while maintaining the
> enclosing transaction.
doesn't work (with SQLite):
[...]
File "/home/antoine/ftth/XXX/XXX/model/logement.py", line 154, in
db_session.commit()
File "/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/
sqlalchemy/orm/scoping.py", line 121, in do
return getattr(self.registry(), name)(*args, **kwargs)
File "/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/
sqlalchemy/orm/session.py", line 673, in commit
self.transaction.commit()
File "/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/
t[1].commit()
File "/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/
sqlalchemy/engine/base.py", line 1036, in commit
self._do_commit()
File "/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/
sqlalchemy/engine/base.py", line 1071, in _do_commit
self.connection._release_savepoint_impl(self._savepoint,
self._parent)
File "/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/
sqlalchemy/engine/base.py", line 768, in _release_savepoint_impl
self.engine.dialect.do_release_savepoint(self, name)
File "/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/
sqlalchemy/engine/default.py", line 117, in do_release_savepoint
connection.execute(expression.ReleaseSavepointClause(name))
File "/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/
sqlalchemy/engine/base.py", line 824, in execute
return Connection.executors[c](self, object, multiparams, params)
File "/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/
sqlalchemy/engine/base.py", line 874, in _execute_clauseelement
return self.__execute_context(context)
File "/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/
sqlalchemy/engine/base.py", line 896, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File "/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/
sqlalchemy/engine/base.py", line 950, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor,
context)
File "/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/
sqlalchemy/engine/base.py", line 931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
OperationalError: (OperationalError) no such savepoint: sa_savepoint_1
u'RELEASE SAVEPOINT sa_savepoint_1' []
Michael Bayer
On Jul 12, 2009, at 7:25 PM, Antoine Pitrou wrote:
>
> Hello,
>
> Thanks for the lengthy (!) explanation.
>
> On Jul 13, 1:05 am, Michael Bayer <mike...@zzzcomputing.com> wrote:
>> Postgres in
>> particular has operations which, once failed, the transaction is not
>> allowed to continue:
>
> Ouch. I'll have to change my strategy, then (I run unit tests with
> SQLite but the production backend is PostgreSQL-based).
>
>> What SQLAlchemy offers that solves both issues is support of
>> SAVEPOINT, via begin_nested(). Using begin_nested(), you can frame
>> an operation that may potentially fail within a transaction, and then
>> "roll back" to the point before its failure while maintaining the
>> enclosing transaction.
>
> For some reason, calling rollback() or commit() after begin_nested()
> doesn't work (with SQLite):
pysqlite doesn't seem to support "SAVEPOINT" out of the box. Jason
Kirtland found out a little bit about it here: http://itsystementwicklung.de/pipermail/list-pysqlite/2009-June/000412.html
. But I'm not sure how that can be integrated with SQLA unless the
pysqlite dialect issued an explicit BEGIN (and I thought perhaps Jason
was going to look into integrating the correct sequences into the
sqlite dialect).
Antoine Pitrou
> pysqlite doesn't seem to support "SAVEPOINT" out of the box. Jason
> pysqlite dialect issued an explicit BEGIN (and I thought perhaps Jason
> was going to look into integrating the correct sequences into the
> sqlite dialect).
Given all those compatibility problems, I'll fall back on the dirty
(MyISAM-ish :-)) way: first do a SELECT to see if the value already
exists, otherwise UPDATE it.
Regards
Antoine.