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
