First, let me state that engine.begin/commit has always confused the heck out
of me so I just acted like it didn't exist. Why do we need it? If transactions
are needed at the connection level, then use the connection.begin/commit for
that (maybe connection.begin() should return a SQLTransaction and
connection.commit() can be eliminated too). Engines should not care about
transactions other than to construct transaction commands for a given flavor of
database.
I propose that we keep session.begin() and connection.begin() (both should return a
SQLTransaction object or subclass thereof) and eliminate the engine.begin/commit/etc.
What will we lose? Does Hibernate have a concept of "engine transactions"?
This greatly simplifies the API:
cn = SQLEngine.connection(...) # get pooled connection
trans = Connection.begin() # return a SQLTransaction (allow nested)
trans.commit() # commit unless nested
trans.rollback() # unconditionally rollback pending transaction
s = Session(...) # variants suggested by Mike are fine
Session.flush() # flush pending CRUD operations
# use transaction if none is present
trans = Session.begin() # return a SessionTransaction (allow nested)
trans.commit() # commit unless nested
trans.rollback() # unconditionally rollback pending transaction
# the following should be deprecated or are not needed (API clutter)
SQLEngine.begin() # deprecated
SQLEngine.commit() # deprecated
SQLEngine.rollback() # deprecated
trans = SQLEngine.nest_connection() # yuck!!
trans = SQLEngine.begin_nested() # don't do this either
trans.close() # where did this come from?? don't do it
Session.commit() # deprecated
Session.rollback() # deprecated
objectstore.commit() # deprecated
sess = nest_session(*args, **kwargs) # do we really need this?
It's easier to learn to use a few simple tools well than to have a different
tool for every little task. A lot of these extra variants feel that way to me.
We should keep the API as simple as possible.
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?
It should also be documented that Connection.begin() is only necessary when not
using a Session since SessionTransaction works just like SQLTransaction (even
for ad-hock SQL statements).
Session pattern examples:
# Configure engine in suitably remote place...
# an engine is only a database abstraction layer, nothing more
s = objectstore.get_session() # get the current session
# Implicit transaction pattern (transaction on flush only)
do_stuff_with_SA_objects(...)
s.flush() # use transaction if none is present
# Explicit transaction pattern (flush without commit)
# use this model to postpone commit to outermost transaction commit
# allows transactional components to call other such components
# with a single transaction wrapping the outermost call
t = s.begin() # NOT begin_transaction (too verbose)
do_stuff_with_SA_objects(...)
s.flush() # flush but don't commit
t2 = session.begin() # start a nested transaction
execute_some_raw_SQL(...)
do_other_stuff_with_SA_objects(...)
s.flush() # flush again...(no commit yet)
t2.commit() # end nested trans; does nothing in database
t.commit() # flush all pending CRUD ops; commit transaction
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.
SQLTransactions and SessionTransactions should allow arbitrarily nesting within
eachother. Only the outermost transaction associated with a given connection will cause a
database-level commit to occur. The "stack" of transactions for a given
connection should be maintained by that connection since there can only be a single
transaction on a given connection at any give time.
Also a slightly more radical change (don't group it with the transaction
handling changes) would be to associate a connection with a session rather than
with an engine. The engine only knows how to make new connections, it does not
maintain a reference to the connection (the Session does that), and it loses
it's execute() method (that's the Connection's job). The engine then becomes a
stateless object to assist the mapper in constructing database-specific SQL
statements, which is what Ian Bicking wanted. This is how I see that API:
conn = engine.connect()
trans = conn.begin()
conn.execute("<raw sql>")
conn.execute(engine.create(mytable))
trans.commit()
That's how I think it should work. Just my $0.02
~ Daniel
Michael Bayer wrote:
gambit -
the various push-based interfaces have some room for streamlining.
but the more immediate issue is that everyone is confusing
engine.begin()/commit() with objectstore.begin()/commit(), as youve done
below.
I am beginning to think a somewhat radical API change might be the only
way to forever distinguish the SQLEngine from the Session, before it
gets too late.
So how about this:
SQLEngine:
trans = SQLEngine.begin() - begins a SQL transaction,
returns SQLTransaction object
trans.commit() - commits a SQL transaction
trans.rollback() - rolls back a transaction
commit()/rollback() directly on SQLEngine go away.
trans = SQLEngine.nest_connection() - opens a new connection
and makes that the current one. you can call begin/commit on this.
returns a SQLTransaction that is not begun.
trans = SQLEngine.begin_nested() - same as nest_connection()
+ begin()
trans.close() - restores the connection to the previous one
before get_nested
objectstore.Session:
trans = session.begin_transaction() # returns a
SessionTransaction, subclass of SQLTransaction. as objects are
registered with this session, the underlying engine represented by that
object's Mapper will have a begin() issued on them which is tracked by
this SessionTransaction.
session.flush() # issues SQL to the database(s) to persist
current changes. if no transaction is open, will use its own
transaction based on the engines present in the currently stored objects.
trans.commit() # commits a *real* database transaction with
the engines that are opened.. will also call session.flush().
trans.rollback() # rolls back a *real* database transaction
with the engines that are opened.
s = Session() # makes a new session
s = Session(import_imap=True) # makes a new session using the
current identity map
s = Session(use_imap=s2) # makes a new session using the
identity map of the given Session
s = Session(trans=[trans1, trans2, trans3...]) # makes a new
Session using the given SQLTransactions (remember the objectstore can
commit across multiple engines....). this lets you start a session on a
nested transaction.
sess = nest_session(*args, **kwargs) # the equivalent of
push_session(Session(*args, **kwargs)). (nest a connection on this? not
sure)
session.close() # restores the previous session
the existing begin()/commit() within the objectstore package and the
Session object just go away; they are too confusing to nearly everyone.
I just came up with this in like 10 minutes, so nothing is decided
here...but something has to change with the current API. everyone
please +1, -1, comment, etc., how bad will this ruin your lives, etc.
also, read where im getting most of this from:
http://www.hibernate.org/hib_docs/v3/reference/en/html/transactions.html#transactions-basics-uow
On Apr 3, 2006, at 7:10 PM, Gambit wrote:
Hey Michael,
Just looking at your usage there, but would it make any sense at all to
have engine.begin() and engine.push_session() turn into one method?
Does it make sense to create multiple sessions if you're not going to be
doing simultaneous transactions?
I think I'm looking for a way to somehow reduce the number of
begin()/commit()/push()/pop() steps involved to maybe eliminate some
of the
tendency users, including myself, will have towards confusion on this
issue.
Also, what happens if you pop a session without commiting transactions
made
within it?
And then try to commit the transaction afterwards? What about the
following case:
sess1 = objectstore.Session()
sess2 = objectstore.Session()
objectstore.push_session(sess1)
foo = MyObj()
objectstore.push_session(sess2)
bar = MyObj()
bar.refers_to = foo
objectstore.commit() # What, exactly, will this commit?
objectstore.pop_session()
objectstore.commit() # Or this?
objectstore.pop_session()
Mind you, I'm not insisting that this is valid behavior -- the right
answer
may be "it throws an exception somewhere around >< here". Something
to add
to the docs, at any rate.
-G
On Monday, April 3, 2006, 7:13:13 PM, you wrote:
Yah, ok , youre getting into features that were just written a few
weeks ago, if you want simultaneous transactions, theres a feature on
engine called "push_session"/"pop_session". you should not be
creating multiple engines for the same connection (i mean, you can,
but the experience will be very painful), since an engine doesnt
really represent a "connection", it represents "a database". So
your example isnt "wrong" but its possible that it wont go very far
since it wasnt designed to work that way.
To use engines with "nested" transactions, looks like this (ack,
havent documented on the site yet...)
# outer transaction
engine.begin()
sqlsess = engine.push_session()
try:
# inner transaction
engine.begin()
#commit inner transaction
engine.commit()
finally:
sqlsess.pop()
# commit outer transaction
trans.commit()
Now, you can do your transactions just like that above. there is a
set of unit tests that illustrate this in the file test/engine.py .
*Alternatively*, you can let the ORM do more of the work for you, by
using the "nest_on" argument to Session. An example of this is here:
http://www.sqlalchemy.org/docs/
unitofwork.myt#unitofwork_advscope_object_nested
Will try to document some more this week since your confusion is
entirely reasonable.
On Apr 3, 2006, at 4:08 AM, Vasily Sulatskov wrote:
Hello Michael,
Monday, April 03, 2006, 11:33:13 AM, you wrote:
So if I understand correctly if I want several simultaneously opened
transactions I have to construct several engines? Please correct me if
I am wrong.
So I changed behaviour of my program to following:
When tab with object opened for editing is created I do something like
this:
# Create a new engine using manually constructed connection pool
self.engine = tables.new_engine()
# Create new table corresponding to new engine
new_table = tables.contragents.toengine(self.engine)
# Create a copy of a class of object we edit
new_class = copy.copy(contragent.Contragent)
# Attach mapper to a new class
sqlalchemy.assign_mapper(new_class, new_table)
# Begin SQL level transaction
self.engine.begin()
# Select object from database using new engine, mapper and class
new_obj = new_class.mapper.select( \
self.obj.__class__.c.id==id, for_update=True)[0]
print new_obj
And when object is saved to database, I do someting like that:
sqlalchemy.objectstore.commit(self.obj)
self.engine.commit()
And it works as I expect. Hurah!!! Thank's a lot.
How do you think is it a good solution, or there is a better way to
do it using SQLAlchemy?
And also it looks like I discovered a bug with connection pooling in
sqlalchemy.engine.py (I created a ticket in trac).
MB> for any kind of transactional locking to occur, you have to use
explicit
MB> sessions with the engine. SQLAlchemy has two different levels of
MB> operation; the "engine" level, which deals with SQL statements and
MB> connections, and the "object relational mapper" level, which
deals with
MB> the state of objects in memory.
MB> so the "Session" you use from the "objectstore" does *not*
represent a
MB> SQL-level transaction. it will use one internally within its
commit()
MB> statement but that one is opened and closed all inside that
function.
MB> the session you are looking for looks like this:
MB> trans = engine.begin()
MB> ....do stuff
MB> trans.commit()
MB> you can use the objectstore.commit() within that as well, as
described here:
MB> http://www.sqlalchemy.org/docs/
unitofwork.myt#unitofwork_advscope_transactionnesting
MB> Vasily Sulatskov wrote:
Hello Michael,
Monday, April 03, 2006, 1:05:27 AM, you wrote:
I am building a GUI program, where opertators will modify
database by
hand. So if two operators open one row of table for edition at the
same time and then one commits and then second commits then changes
made by operator who commits first will be lost.
I googled for a while and found a suggestion to compare state of the
row in database before commit and if it changed do not commit but
tell
the operator something like "We are sorry but the object you spent
editing for a last 30 minutes is changed in database, so all your
changes lost, try again". This is acceptable behaviour, but IMHO
locking objects open for editing is a better solution. It ensures no
data loss on database level. Perhaps there is a better solution
but I
don't know it and can not find.
Actually SQLAlchemy works pretty good for me I use one
connection for
objects open for all read-only operations and when user wants to
edit
object I create another connection
(using
sqlalchemy.objectstore.Session() ).
Documentation says that "Sessions can be created on an ad-hoc
basis
and used for individual groups of objects and operations. This
has the
effect of bypassing the normal thread-local Session and
explicitly
using a particular Session:". So if I understand this paragraph
correctly it will open new database connection and objects selected
from mapper using this section will use different database
connection
from default "thread-local" objects.
But I can't understand how can it be so that SQLAlchemy sends
correct
SQL (like SELECT ... FOR UPDATE;) but the row is not locked. It
should
be locked until commit in this connection, but it doesn't lock.
MySQLdb behaves itself in similar way when it in autocommit mode,
i.e. you
send SELECT ... FOR UPDATE; command but it automatically commits and
lock you made instantly released.
As far as I understand SQLAlchemy should send transaction commit
when
i command session.commit(), but the lock is released instantly.
Can someone explain what's happening?
MB> "for update" is not a behavior SA's mapper was really
designed to
support.
MB> if you do not use an explicit engine transaction, then the
connection
MB> object used for each operation will possibly be different
each time,
and
MB> also a new cursor is used. its not like it will always be
this way,
but
MB> ive never had an occasion to use FOR UPDATE myself....is
there any
reason
MB> why you cant just use a regular transaction ?
MB> Vasily Sulatskov wrote:
Hello,
I have a problem with "SELECT ... FOR UPDATE;" command.
I have a MySQL database, table created with TYPE=INNODB engine
specification with proper transaction isolation level set.
I want to issue "SELECT ... FOR UPDATE;" command to lock
specific row
of table for updates.
Here's a sample script:
# -*- coding: cp1251 -*-
import sqlalchemy
import time
import sys
databaseParams = { \
'echo': True,
'echo_uow': True,
'logger': file( 'sql.log', 'w' ),
'convert_unicode': True,}
engine = sqlalchemy.create_engine(
'mysql',
{
'db':'vasilytest',
'user':'root',
'passwd':'',
'host':'127.0.0.1'
},
**databaseParams )
contragents = sqlalchemy.Table( 'contragents', engine,
sqlalchemy.Column( 'id', sqlalchemy.Integer,
primary_key=True ),
sqlalchemy.Column( 'first_name', sqlalchemy.String(50),
default='',
key='firstName' ),
sqlalchemy.Column( 'last_name', sqlalchemy.String(50),
default='',
key='lastName' ),
sqlalchemy.Column( 'patronymic', sqlalchemy.String(50) ),
default='',
mysql_engine='INNODB' )
if 'create' in sys.argv:
contragents.create()
class SqlStrMixing( object ):
def __str__( self ):
s = [ self.__class__.__name__ + ': ' ]
for c in self.c:
s.append( '%s=%s ' % ( c.key, getattr(self, c.key) ) )
return ''.join(s).encode('cp866')
class Contragent(SqlStrMixing):
pass
sqlalchemy.assign_mapper( Contragent, contragents )
session = sqlalchemy.objectstore.Session()
session.begin()
obj = Contragent.mapper.using(session).select(Contragent.c.id==17,
for_update=True)[0]
print obj
time.sleep(20)
session.commit()
sqlalchemy.objectstore.commit()
I launch first copy of this script and it immediatlely prints
selected
object and sleeps for 20 seconds. Then I launch second copy of
script
and it immediately prints selected object too. That's wrong, it
should
block and wait for first script to commit transaction.
When I inspect sql.log I see following:
SELECT contragents.patronymic [skiped for clarity]
contragents.first_name
AS contragents_first_name
FROM contragents
WHERE contragents.id = %s FOR UPDATE[17]
i.e. right SQL command, but selected row doesn't lock.
Perhaps I am doing something wrong with transactions.
Can anyone explain what's going on?
And how to achieve desired behaviour?
And maybee there's another way to lock row of table for update?
I did the same using regular DB-API and it worked as expected.
Here's source code:
# -*- coding: cp1251 -*-
import MySQLdb as dbms
import time
params = { \
'host': '127.0.0.1',
'user': 'root',
'db' : 'vasilytest',
'passwd': '' }
db = dbms.Connect( **params )
cursor = db.cursor()
cursor.execute( """select * from contragents where id=17 for
update;"""
)
print cursor.fetchall()
time.sleep(20)
db.commit()
I launch first copy of this script and it immediately prints
fetched
columns and sleeps for 20 seconds. Then I launch second copy of
the
script and it blocks untill first script commits or interrupted
(using
Ctrl-C or something), i.e. desired behaviour.
--
Best regards,
Vasily
-------------------------------------------------------
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
--
Best regards,
Vasily mailto:[EMAIL PROTECTED]
-------------------------------------------------------
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
MB> -------------------------------------------------------
MB> This SF.Net email is sponsored by xPML, a groundbreaking
scripting language
MB> that extends applications into web and mobile media. Attend the
live webcast
MB> and join the prime developer group breaking into this new
coding territory!
MB> http://sel.as-us.falkag.net/sel?
cmd=lnk&kid=110944&bid=241720&dat=121642
MB> _______________________________________________
MB> Sqlalchemy-users mailing list
MB> Sqlalchemy-users@lists.sourceforge.net
MB> https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
--Best regards,
Vasily mailto:[EMAIL PROTECTED]
-------------------------------------------------------
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
-------------------------------------------------------
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
-------------------------------------------------------
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
-------------------------------------------------------
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
