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
