Michael Bayer wrote:
cn = SQLEngine.connection(...) # get pooled connection
what is cn ? since it has begin/commit stuff that returns
SQLTransactions, it cant be just a striaght DBAPI connection. Right
now, the SQLSession object is most analgous to this, it is a
connection-holding object. The SQLEngine has already been detached from
its connections to a large degree, but it remains a "factory" for new
connections, as well as SQL compilers which are the "connection-less"
component to a particular type of database.
I see. That sounds good though. I think we're getting closer to how Hibernate
does it. How does one obtain a SQLSession? I would guess it's something like
this:
session = engine.get_session()
which looks a lot like
session = objectstore.get_session()
Aaahh!! Now we've got two different types of sessions. I propose that we name
it SQLConnection rather than SQLSession. Then we have this:
conn = engine.connect() # get a new SQLConnection
Note: the "explicit transaction model" does not perform database-level
nested transactions, which is a feature of some databases that allows
sub-units-of-work to be performed and committed/rollbacked without
affecting the outer transaction(s). Rather, it is a pattern that uses
a single database transaction while allowing multiple levels of SA
transactions to be started/committed with only the outermost commit
actually causing the database transaction to be committed.
well, we need both kinds of nesting. this is one of the key challenges
here, in that there is nesting of transactions at the lexical level with
a depth counter, there is nesting of objectstore "Sessions" that track
lists of objects to be committed,
Hmm, I always thought those two were doing essentially the same thing. I was
always a little confused because there seemed to be two separate
implementations that achieve the same result. The API I proposed is capable of
both of these types of nesting:
# EXAMPLE: depth counter nesting
conn = engine.connect() # get a SQLSession (SQLConnection?)
t_outer = conn.begin() # begin real database transaction
t_inner = conn.begin() # increment depth counter
# do stuff with conn...
t_inner.commit() # decrement depth counter
t_outer.commit() # perform real commit
# EXAMPLE: sessions with nested UOW's
s = objectstore.get_session()
t_outer = s.begin() # begin real database transaction
t_inner = s.begin() # start nested UOW, push outer UOW
# do stuff with session...
t_inner.commit() # commit nested UOW, pop outer UOW
t_outer.commit() # commit outer UOW, commit db trans
I'm not clear on how these two types of "transactions" are different. In the
session example, does the t_inner.commit() actually cause changes in the nested UOW to be
flushed to the database or does it merge the nested UOW with the outer UOW?
What are the use cases for these two transaction strategies?
and there is real "nesting" that
occurs via opening a second database connection.
That's not what I'm talking about when I say "nested transactions".
Database-level nested transactions do not require a second connection. They're all done
on the same connection sort of like your depth counter except that you can do a real
commit or rollback on the nested transaction because it's a real database transaction. To
be honest I've never done anything like this (I just read about it somewhere, I think
when I was reading about Hibernate transaction management).
In fact, I wouldn't call what you're describing "real nesting". I would call it
two separate (concurrent) database connections/transactions in the same (Python) process.
It can be done using the API I described:
# EXAMPLE: concurrent connections/transactions
c1 = engine.connect() # get a connection
c2 = engine.connect() # ...and another one
t1 = c1.begin()
t2 = c2.begin()
# do some stuff with c1/t1 (isolated from t2)
# do some stuff with c2/t2 (isolated from t1)
t1.commit()
t2.commit()
The only difference I see between my API and yours is that mine requires the
developer to keep track of all connection objects that have been obtained with
engine.connect() while yours seems to maintain an internal stack of open
connections (which still requires the developer to match each begin with a
corresponding commit). The internal connection stack would be necessary if the
commit method belonged to the connection object, but since it belongs to the
transaction object there is no reason for SA to maintain a stack of open
connections (since it it will never close them implicitly). It is the
developer's responsibility to keep track of- and close all connections that
have been obtained using engine.connect()--just like using normal DBAPI
connections.
people definitely need
all three
Why? Specifically, how is the connection.begin/commit depth counter different
from nested UnitOfWork in objectstore.Session? This is a key source of
confusion: there are three different types of transactions and they all do
things a bit differently.
I have complicated the API to provide all of these
features. So how would this proposed API handle opening a second
connection ?
See above EXAMPLE: concurrent connections/transactions
SQLTransaction and SessionTransaction should strive to maintain a
simple, uniform interface. That will reduce confusion. In fact, could
they be combined into a single type? If not, why and what is the
difference?
One of the up-until-now cherished design goals here, which it seems like
you are looking to dilute, is that the objectstore/Session API is 100%
specific to SA's object relational mapper and is not required in any way
to just deal with SQL statements, SQL transactions, and engines. I
still think this separation is essential, SA must be a "complete
product" without the ORM/Session used at all; that way other folks can
build other kinds of ORMs or whatever on top of it.
If I was diluting your design goals it was not intentional. What I was saying
is the _interface_ (meaning the methods exposed to the SA user) should be
uniform across all transaction implementations. Then the developer only needs
to learn a single API that will work in all cases. What you're talking about is
the implementation. It sounds like the SQLTransaction and the
SessionTransaction objects need to be implemented differently/separately, which
is fine. All I'm asking is that the interface is the same no matter which one
I'm using. This is the interface I'm talking about:
# *Transaction interface
trans.commit()
trans.rollback()
That's it.
Another major difference between Session/Engine is that the Engine knows
about a specific database; the Session does not. a Session can commit
objects across any number of databases in one shot.
That sounds like a cool feature.
that was why I
proposed that session.begin_transaction() (named to avoid conflicts with
the old one, but if people dont mind the switch i can go with begin()),
+1 for session.begin()
-1 for session.begin_transaction()
actually sets up a "global transaction" that all the various SQLEngines
would join into as they are called within that thread. this is more of
a J2EE type of feature which can have a lot of value.
Will session.begin() start a database transaction? If so, will it start a
database transaction for each engine participating in the session?
I agree that this type of feature can have a lot of value, but I don't think it
will have much value unless you can guarantee how it's going to work with
regard to commit/rollback; i.e. will all transactions be rolled back if a
single commit fails? How will you handle the situation where you commit a bunch
of transactions in separate databases and the first few commit successfully and
then one fails? Is it possible to rollback a transaction that's already been
committed? These things need to be spelled out to make a feature like that very
useful. This type of transaction management should be a plugin since the level
of complexity is fairly high and it's not needed in most situations (that's how
Hibernate handles it--for example, JTA is a transaction manager that can be
plugged into Hibernate).
i think the two things that are important to me right now are:
- changing objectstore.commit() to be a different name, probably
flush()
+1 I assume this means Session.commit() will become Session.flush() as well
- either removing the words "begin"/"commit" from the objectstore
package totally, or if they are there, then they will correspond to a
real database transaction in some way.
+1 for removing them both from objectstore. Session.begin() starts a new
SessionTransaction. Session.commit is removed.
If a database transaction is needed via the session, provide a way to get
connection(s) from the session.
Also a slightly more radical change
would be to associate a connection with
a session rather than with an engine...
I think that last thing (getting the connection from the session) ties into
this. However, we better start a new thread for that. I'll do that now.
~ Daniel
-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting language
that extends applications into web and mobile media. Attend the live webcast
and join the prime developer group breaking into this new coding territory!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
