On Apr 5, 2006, at 8:12 AM, Qvx wrote:
> e = create_engine('foo')
> trans = e.begin()
> try:
> mytable.update().execute()
>
> # then in some other function..
> trans2 = e.begin()
> try:
> myothertable.update.execute()
> trans2.commit() # doesnt commit
> except:
> trans2.rollback() # rolls trans2, trans
>
> trans.commit() # commits mytable, myothertable
> except:
> trans.rollback() # rolls back trans
>
> "trans2.rollback()" will also roll back "mytable.update().execute
> ()". On Oracle you would have to issue savepoint and then rollback
> to this savepoint in order to achieve partial rollback. On other
> databases this is perhaps handled differently.
>
the point is, there is only one real transaction occuring above. the
second call to begin() returns a no-op, as does the commit() on trans2.
Of course. But I was talking about *rollback* inside the middle precisely because this is single transaction and such rollback would undo everything since the *outermost* begin(). My hint was that if this is not what you won't (rollback everything) you need an explicit SAVEPOINT in Oracle and something else in other databases. Or you have to make users understand what is going to happen if they issue a rollbeck in some deeply nested procedure which despite its begin() can rollback more than it expects.
>
> Yes, but in my case it is more like this:
>
> trans_mssql = engine_mssql.begin()
> trans_oracle = engine_oracle.begin()
> trans_mysql = engine_mysql.begin()
> try:
> #...
> trans_mssql.commit()
> trans_oracle.commit()
> trans_mysql.commit()
> except:
> trans_mssql.rollback()
> trans_oracle.rollback()
> trans_mysql.rollback()
>
you need something else, which I havent gotten into with this
proposal, which is a TransactionManager. youd gather up all your
ConnectionProxy objects and register them with a TransactionManager
that does a pattern like the above. This is something I could take
a stab at, but its not something I have deep experience with (also
the JTA docs, which describe the J2EE standard for this sort of
thing, are somewhat daunting)...it would be a little simplistic to
start out. Or folks could contribute such a thing, theyre likely to
put more thought into it than me.
It would work to some degree. My concern was that maybe I don't know upfront what "connections" (databases) would be used. To be hones, currently I know what will be used, but it is maybe not something that I will be able as aplication grows.
The biggest riddle for me is how to write a decorator called transaction() which will work correctly no matter how many databases my application has to connect to, and no matter haw few I actually used inside decorated function *and* which works even if I mix ORM and non-ORM style. There is one such decorator inside TurboGears which by my opinion is not correct. You can find it in my previous post. Shortened version is this:
def run_with_transaction(func, *args, **kw):
try:
# flush any cached objects
sqlalchemy.objectstore.clear()
retval = func(*args, **kw)
sqlalchemy.objectstore.commit()
return retval
except:
# No need to "rollback" the sqlalchemy unit of work, because nothing
# has hit the db yet.
There are two things, one which I like and other that I don't:
1. I like the fact that you can say objectstore.commit() and it will issue INSERT/UPDATE/DELETE to only those databases that have been used by the decorated controller function (limited to ORM of course).
2. I don't like the fact that it doesn't work if I use anything besides ORM functionality. It galantly assumes that if there was an exception you don't need to do anything because nothing has hit the database yet.
I can work around all those issues (allready have) by explicitely managing my transactions, but it would be nice if we could make it work. Something like this:
# called from decorator
def run_with_transaction(func, *arg, **kw):
try:
# "begin" but don't necessary touch the database yet
sqlalchemy.something.implicitly_begin_all()
sqlalchemy.objectstore.clear()
retval = func(*args, **kw)
# now commit everything that was used in this thread
sqlalchemy.something.commit_all_used_dbs()
return retval
sqlalchemy.something.rollback_all_used_dbs()
In this case I'm not sure haw would SA respond to begin/commit/rollback inside decorated function. Ignore commits because there was implicitly_begin_all() or allow me to explicitly manage my affairs if i want.
