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 explicitMB> sessions with the engine. SQLAlchemy has two different levels of MB> operation; the "engine" level, which deals with SQL statements andMB> connections, and the "object relational mapper" level, which deals withMB> 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_transactionnestingMB> 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 byhand. 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 therow in database before commit and if it changed do not commit but tellthe 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 nodata loss on database level. Perhaps there is a better solution but Idon'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 (usingsqlalchemy.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 explicitlyusing a particular Session:". So if I understand this paragraph correctly it will open new database connection and objects selectedfrom mapper using this section will use different database connectionfrom 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 shouldbe locked until commit in this connection, but it doesn't lock.MySQLdb behaves itself in similar way when it in autocommit mode, i.e. yousend SELECT ... FOR UPDATE; command but it automatically commits and lock you made instantly released.As far as I understand SQLAlchemy should send transaction commit wheni 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 tosupport.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,andMB> also a new cursor is used. its not like it will always be this way,butMB> ive never had an occasion to use FOR UPDATE myself....is there anyreason 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 rowof 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 shouldblock 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 (usingCtrl-C or something), i.e. desired behaviour. -- Best regards, Vasily ------------------------------------------------------- This SF.Net email is sponsored by xPML, a groundbreaking scripting languagethat extends applications into web and mobile media. Attend the livewebcast 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-usersMB> -------------------------------------------------------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=121642MB> _______________________________________________ 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
