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 if
I am wrong.
So I changed behaviour of my program to following:
When tab with object opened for editing is created I do something like
this:
# 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 to
do it using SQLAlchemy?
And also it looks like I discovered a bug with connection pooling in
sqlalchemy.engine.py (I created a ticket in trac).
MB> for any kind of transactional locking to occur, you have to use explicit
MB> sessions with the engine. SQLAlchemy has two different levels of
MB> operation; the "engine" level, which deals with SQL statements and
MB> 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 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
>>
MB> -------------------------------------------------------
MB> This SF.Net email is sponsored by xPML, a groundbreaking scripting language
MB> that extends applications into web and mobile media. Attend the live 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
