gambit -
the various push-based interfaces have some room for streamlining.
but the more immediate issue is that everyone is confusing
engine.begin()/commit() with objectstore.begin()/commit(), as youve
done below.
I am beginning to think a somewhat radical API change might be the
only way to forever distinguish the SQLEngine from the Session,
before it gets too late.
So how about this:
SQLEngine:
trans = SQLEngine.begin() - begins a SQL transaction, returns
SQLTransaction object
trans.commit() - commits a SQL transaction
trans.rollback() - rolls back a transaction
commit()/rollback() directly on SQLEngine go away.
trans = SQLEngine.nest_connection() - opens a new connection and
makes that the current one. you can call begin/commit on this.
returns a SQLTransaction that is not begun.
trans = SQLEngine.begin_nested() - same as nest_connection() +
begin()
trans.close() - restores the connection to the previous one before
get_nested
objectstore.Session:
trans = session.begin_transaction() # returns a
SessionTransaction, subclass of SQLTransaction. as objects are
registered with this session, the underlying engine represented by
that object's Mapper will have a begin() issued on them which is
tracked by this SessionTransaction.
session.flush() # issues SQL to the database(s) to persist
current changes. if no transaction is open, will use its own
transaction based on the engines present in the currently stored
objects.
trans.commit() # commits a *real* database transaction
with the engines that are opened.. will also call session.flush().
trans.rollback() # rolls back a *real* database
transaction with the engines that are opened.
s = Session() # makes a new session
s = Session(import_imap=True) # makes a new session using the
current identity map
s = Session(use_imap=s2) # makes a new session using the identity
map of the given Session
s = Session(trans=[trans1, trans2, trans3...]) # makes a new
Session using the given SQLTransactions (remember the objectstore can
commit across multiple engines....). this lets you start a session on
a nested transaction.
sess = nest_session(*args, **kwargs) # the equivalent of
push_session(Session(*args, **kwargs)). (nest a connection on this?
not sure)
session.close() # restores the previous session
the existing begin()/commit() within the objectstore package and the
Session object just go away; they are too confusing to nearly everyone.
I just came up with this in like 10 minutes, so nothing is decided
here...but something has to change with the current API. everyone
please +1, -1, comment, etc., how bad will this ruin your lives, etc.
also, read where im getting most of this from:
http://www.hibernate.org/hib_docs/v3/reference/en/html/
transactions.html#transactions-basics-uow
On Apr 3, 2006, at 7:10 PM, Gambit wrote:
Hey Michael,
Just looking at your usage there, but would it make any sense at
all to
have engine.begin() and engine.push_session() turn into one method?
Does it make sense to create multiple sessions if you're not going
to be
doing simultaneous transactions?
I think I'm looking for a way to somehow reduce the number of
begin()/commit()/push()/pop() steps involved to maybe eliminate
some of the
tendency users, including myself, will have towards confusion on this
issue.
Also, what happens if you pop a session without commiting
transactions made
within it?
And then try to commit the transaction afterwards? What about the
following case:
sess1 = objectstore.Session()
sess2 = objectstore.Session()
objectstore.push_session(sess1)
foo = MyObj()
objectstore.push_session(sess2)
bar = MyObj()
bar.refers_to = foo
objectstore.commit() # What, exactly, will this commit?
objectstore.pop_session()
objectstore.commit() # Or this?
objectstore.pop_session()
Mind you, I'm not insisting that this is valid behavior -- the
right answer
may be "it throws an exception somewhere around >< here".
Something to add
to the docs, at any rate.
-G
On Monday, April 3, 2006, 7:13:13 PM, you wrote:
Yah, ok , youre getting into features that were just written a few
weeks ago, if you want simultaneous transactions, theres a feature on
engine called "push_session"/"pop_session". you should not be
creating multiple engines for the same connection (i mean, you can,
but the experience will be very painful), since an engine doesnt
really represent a "connection", it represents "a database". So
your example isnt "wrong" but its possible that it wont go very far
since it wasnt designed to work that way.
To use engines with "nested" transactions, looks like this (ack,
havent documented on the site yet...)
# outer transaction
engine.begin()
sqlsess = engine.push_session()
try:
# inner transaction
engine.begin()
#commit inner transaction
engine.commit()
finally:
sqlsess.pop()
# commit outer transaction
trans.commit()
Now, you can do your transactions just like that above. there is a
set of unit tests that illustrate this in the file test/engine.py .
*Alternatively*, you can let the ORM do more of the work for you, by
using the "nest_on" argument to Session. An example of this is here:
http://www.sqlalchemy.org/docs/
unitofwork.myt#unitofwork_advscope_object_nested
Will try to document some more this week since your confusion is
entirely reasonable.
On Apr 3, 2006, at 4:08 AM, Vasily Sulatskov wrote:
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
-------------------------------------------------------
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
