HD - yah, youre good, this is how it works right now.
On Apr 3, 2006, at 8:53 PM, HD Mail wrote:
Michael,This thread is making me think that I am using transactions in SA incorrectly. I use a transaction decorator like belowdef transaction(func): def trans_func(*args, **kws): log.warn('***** TRANS BEGIN *******') engine.begin() try: f = func(*args, **kws) engine.commit() log.warn('***** TRANS COMMIT *******') return f except: log.warn('***** TRANS ROLLBACK *******') engine.rollback() raise return trans_func @transaction def update_stock(): update_stock_line() @transaction def update_stock_line(): .....is this correct usage of transactions ? as I was under the impression that the engine would keep it's own count of nested transactions and only commit on the top level. So update_stock_line will only commit if update_stock is committed, but if update_stock_line failed, then update_stock would also be rolled back.Is this right ? Thanks Huygambit - 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 objecttrans.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_nestedobjectstore.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 sessions = 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 Sessions = 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 sessionthe 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-uowOn 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 tohave engine.begin() and engine.push_session() turn into one method?Does it make sense to create multiple sessions if you're not going to bedoing simultaneous transactions? I think I'm looking for a way to somehow reduce the number ofbegin()/commit()/push()/pop() steps involved to maybe eliminate some of the tendency users, including myself, will have towards confusion on thisissue.Also, what happens if you pop a session without commiting transactions madewithin 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 addto 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 fewweeks ago, if you want simultaneous transactions, theres a feature onengine 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_nestedWill 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 ifI am wrong. So I changed behaviour of my program to following:When tab with object opened for editing is created I do something likethis: # 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 todo it using SQLAlchemy?And also it looks like I discovered a bug with connection pooling insqlalchemy.engine.py (I created a ticket in trac).MB> for any kind of transactional locking to occur, you have to useexplicitMB> 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 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 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 changesmade 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 buttellthe operator something like "We are sorry but the object you spent editing for a last 30 minutes is changed in database, so all yourchanges lost, try again". This is acceptable behaviour, but IMHOlocking objects open for editing is a better solution. It ensures nodata 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 forobjects open for all read-only operations and when user wants toedit 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 paragraphcorrectly it will open new database connection and objects selectedfrom 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. yousend SELECT ... FOR UPDATE; command but it automatically commits andlock 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 scriptand it immediately prints selected object too. That's wrong, itshould 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 fetchedcolumns and sleeps for 20 seconds. Then I launch second copy ofthescript 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 scriptinglanguagethat extends applications into web and mobile media. Attend thelive webcastand join the prime developer group breaking into this new codingterritory! 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 scriptinglanguagethat 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-usersMB> ------------------------------------------------------- MB> This SF.Net email is sponsored by xPML, a groundbreaking scripting languageMB> that extends applications into web and mobile media. Attend thelive 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
