# 3. the connection. this is "new" (its currenly called a
SQLSession...but that name changes to ConnectionProxy) generally used
to say "do this with this explicit resource". this is to make
everyones needs for what I was calling "nested" stuff possible, but
youre right its not really "nested", so now theres a tangible thing
to hold onto (well there has been for several weeks now, but now its
the primary way to do it).
# this is a ConnectionProxy object. it represents a single
unique connection from the
# pool, which it grabs when needed, or when a transaction is
started it holds onto it.
# this object is used automatically with the implicit stuff in
#1, #2
conn = e.connection()
# 3a. transactions on the connection. same deal, same SQLTransaction
object:
trans = conn.begin ()
trans.commit()
Good as long as this connection is now accessible only to it's caller. Once I get connection it is *mine* and there are no surprise operations carried out over this connection by some other parts of the system. This connection must be used only in places where I explicitly use it be it via using(), set_current() or some other ways.
Once I'm done with it, how do I release such connection back to pool so it can be used by other parts of the system?
# 4. natural nesting behavior
As always, begin/commit/rollback uses the "outermost nested" idea,
where "nested" calls to begin/commit dont affect the single
"outermost" transaction.
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.
So to sum up Part I, heres the full vocabulary to learn regarding
connections, engines, transactions: SQLEngine, ConnectionProxy,
SQLTransaction. begin(), connection(), using(), commit(), rollback
(), set_current(), restore_previous().
Seems reasonable so far.
transactions + objectstore stuff
# 9. the Session is just an object that knows how to spit out a bunch
of UPDATE etc. statements. Transactions are used via the engine as
from Part I:
e = create_engine('foo')
trans = e.begin()
try:
obj1 = mapper.get(1)
obj1.foo = 'bar'
objectstore.flush()
trans.commit()
except:
trans.rollback()
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()
And this is if I know what is going to be used (I might be calling procedures several layers deep).
Another issue is with web frameworks which try to call your handler/controller within transaction so as to commit when there are no errors, and to rollback when there are errors. It is unreasonable to call begin/commit/rollback on all database connections that my application is using because not all connections are used always. This is decorator example from TurboGears:
def run_with_transaction(func, *args, **kw):
try:
try:
if sqlalchemy:
# flush any cached objects
sqlalchemy.objectstore.clear()
retval = func(*args, **kw)
if sqlalchemy:
sqlalchemy.objectstore.commit()
commit_all()
return retval
except cherrypy.HTTPRedirect:
commit_all()
if sqlalchemy:
sqlalchemy.objectstore.commit()
raise
except cherrypy.InternalRedirect:
commit_all()
if sqlalchemy:
sqlalchemy.objectstore.commit ()
raise
except:
# No need to "rollback" the sqlalchemy unit of work, because nothing
# has hit the db yet.
rollback_all()
raise
finally:
end_all()
I don't find this perfect because it looks like it expects only ORM part to be used. It would be nice it there was some better pattern that could be used by such decorator. One that uses into account possibility of direct code execution through engine/table and not only via ORM.
Tvrtko Sokolovski
