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
>> 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-users
