So from what you said below: trans = engine.begin() # do some explicit SQL trans.commit() # Commits all the explicit SQL, as we'd expect
objectstore.push_session(objectstore.Session(import_imap=True)) trans = session.begin_transaction() foo = Foo() bar = Bar() # other UOW behaviors session.flush() # Cleans out the UOW but does NOT commit to DB baz = Baz() trans.commit() # Cleans out the UOW, incl. baz, AND commits to DB If this is accurate, a couple questions spring to mind: First, when you do session.flush() without a surrounding transaction, does all the UOW actual commits occur within it's own transaction? (I sorta assumed this is yes, but just want to make sure) Second, I assume that foo, bar, and baz above all "belong" to the new session. What about other instances, created before or after? I think I'm trying to determine if the concept of "ownership" applies to session objects, or if they're just container/trackers for the UOW concept, and as such any changes that occur within their scope of authority are managed by them. Third, it seems like we're migrating away from using the objectstore for anything other then Session management. Not trying to break too many api's here, but maybe we should change our references to sqlalchemy.current_session instead of sqlalchemy.objectstore? Forth, and finally, I'm not sure that session.begin_transaction() should create a SQLTransaction as well (which, if I understand correctly, is what a engine.begin() does -- an honest to goodness transaction). Isn't the idea of the session.begin_transaction() something to establish UOW transactions, not DB transactions? Or am I just completely reading that wrong? Cheers :) -G On Tuesday, April 4, 2006, 2:04:57 AM, you 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
