"for update" is not a behavior SA's mapper was really designed to support.
if you do not use an explicit engine transaction, then the connection
object used for each operation will possibly be different each time, and
also a new cursor is used. its not like it will always be this way, but
ive never had an occasion to use FOR UPDATE myself....is there any reason
why you cant just use a regular transaction ?
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
>
-------------------------------------------------------
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
