for any kind of transactional locking to occur, you have to use explicit sessions with the engine. SQLAlchemy has two different levels of operation; the "engine" level, which deals with SQL statements and connections, and the "object relational mapper" level, which deals with the state of objects in memory.
so the "Session" you use from the "objectstore" does *not* represent a SQL-level transaction. it will use one internally within its commit() statement but that one is opened and closed all inside that function. the session you are looking for looks like this: trans = engine.begin() ...do stuff trans.commit() you can use the objectstore.commit() within that as well, as described here: http://www.sqlalchemy.org/docs/unitofwork.myt#unitofwork_advscope_transactionnesting 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 >>> [email protected] >>> 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 > [email protected] > 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 [email protected] https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
