Session/transaction questions
Nicholas Riley
I just tried out SQLAlchemy 0.4.4 on a simple project - the front end
for a vending machine. It uses two MySQL database connections. I've
used the session/transaction facilities so I can use SQLAlchemy's two-
phase commit to create a transaction spanning both databases. It's
working, but I'm having to do more things than I think I should be
doing, so I'm not sure if I'm misunderstanding SQLAlchemy or have
something set up incorrectly.
First, according to the SQLAlchemy 0.4 release notes, it should be
possible to commit the session and have the changes take effect, but
I'm still needing to manually commit the transaction. I'm doing it
like this:
transaction = session.begin()
... do stuff ...
transaction.commit()
session.commit()
It also works when the two commit calls are reversed. Without the
transaction.commit(), the data gets saved to the database but is not
visible outside the session; is the transaction not committing or is
it a nested transaction?
Second, I'm having to call session.close() to get updated data outside
an explicit transaction, when I thought creating the sessionmaker with
autoflush=True should cause an update to occur every time I make a
query. Calling session.flush() does not help, regardless of whether I
specify autoflush.
The code is pretty short. Please let me know if there's anything I'm
doing wrong here.
Definitions (yes, I realize there's a password in there):
http://www.acm.uiuc.edu/projects/SIGArch/browser/sucrose/trunk/python/mysql/db.py
Usage (see Sucrose.vend):
http://www.acm.uiuc.edu/projects/SIGArch/browser/sucrose/trunk/python/api/sucrose.py
Thanks,
--Nicholas
Michael Bayer
On Mar 31, 2008, at 12:40 AM, Nicholas Riley wrote:
>
> Hi,
>
> I just tried out SQLAlchemy 0.4.4 on a simple project - the front end
> for a vending machine. It uses two MySQL database connections. I've
> used the session/transaction facilities so I can use SQLAlchemy's two-
> phase commit to create a transaction spanning both databases. It's
> working, but I'm having to do more things than I think I should be
> doing, so I'm not sure if I'm misunderstanding SQLAlchemy or have
> something set up incorrectly.
>
> First, according to the SQLAlchemy 0.4 release notes, it should be
> possible to commit the session and have the changes take effect, but
> I'm still needing to manually commit the transaction. I'm doing it
> like this:
>
> transaction = session.begin()
> ... do stuff ...
> transaction.commit()
> session.commit()
You shouldn't be doing anything with the SessionTransaction
explicitly, this is a backwards compatibility thing from 0.3. Use
only begin()/commit() on the Session itself. When you commit on the
SessionTransaction explicitly, the second commit() on Session will
raise an error since ST asserts that the transaction is open when this
call comes in (though theres a reason why its not happening for your
case).
> It also works when the two commit calls are reversed. Without the
> transaction.commit(), the data gets saved to the database but is not
> visible outside the session; is the transaction not committing or is
> it a nested transaction?
a "nested" transaction is what we term for a "SAVEPOINT", which isn't
occuring unless you use "begin_nested()". The transaction has the
quality though that multiple calls to begin() will require that many
calls to commit() for the actual COMMIT to occur, this is so that code
which uses its own begin()/commit() pair may be enclosed within other
code which does the same. I think this is the problem you're having.
> Second, I'm having to call session.close() to get updated data outside
> an explicit transaction, when I thought creating the sessionmaker with
> autoflush=True should cause an update to occur every time I make a
> query. Calling session.flush() does not help, regardless of whether I
> specify autoflush.
if your session is still within a transaction, then you wont see data
from outside that transaction (depending on isolation levels) until
that transaction is committed/rolled back (or closed). To see the
behavior of flush(), turn on SQL echoing (always the first step when
anything is not working as expected).
>
> Definitions (yes, I realize there's a password in there):
>
> http://www.acm.uiuc.edu/projects/SIGArch/browser/sucrose/trunk/python/mysql/db.py
>
> Usage (see Sucrose.vend):
>
> http://www.acm.uiuc.edu/projects/SIGArch/browser/sucrose/trunk/python/api/sucrose.py
>
yes - the main thing is, the Session you've set up is "transactional",
which means the session is always going to be in a "begun"
transaction, when first constructed and after each commit - theres no
need to call begin() explicitly in that case. By calling begin() a
second time (since that's what it really is here), you are indicating
you'd like to call commit() twice, so that would explain why your
commit needs to be called twice, and also why no error is raised for
the double-commit. An example of "transactional" session usage is
here: http://www.sqlalchemy.org/docs/04/session.html#unitofwork_managing
. I can see that the docs later down which describe what you need
to know here might need some editing for brevity.
If you'd like to call begin() explicitly, you might be better off
leaving the "transactional" flag off - what this means is that when
you're outside of a transaction, connections are returned to the
connection pool after usage where their transactional state is rolled
back, so that subsequent query operations will get the most recently
available data. the only thing to be careful of is that when
autoflush=True, you will be getting those flushes after each query
which can't be rolled back if no transaction was begun.
Nicholas Riley
> On Mar 31, 2008, at 12:40 AM, Nicholas Riley wrote:
> You shouldn't be doing anything with the SessionTransaction
> explicitly, this is a backwards compatibility thing from 0.3. Use
> only begin()/commit() on the Session itself. When you commit on the
> SessionTransaction explicitly, the second commit() on Session will
> raise an error since ST asserts that the transaction is open when this
> call comes in (though theres a reason why its not happening for your
> case).
> a "nested" transaction is what we term for a "SAVEPOINT", which isn't
> occuring unless you use "begin_nested()". The transaction has the
> quality though that multiple calls to begin() will require that many
> calls to commit() for the actual COMMIT to occur, this is so that code
> which uses its own begin()/commit() pair may be enclosed within other
> code which does the same. I think this is the problem you're having.
nesting". I managed to get nesting errors from MySQL so I assumed
SQLAlchemy was not doing this. In retrospect I should have just
tested my assumption directly.
> An example of "transactional" session usage is
> here: http://www.sqlalchemy.org/docs/04/session.html#unitofwork_managing
> . I can see that the docs later down which describe what you need
> to know here might need some editing for brevity.
http://www.sqlalchemy.org/docs/04/session.html#unitofwork_contextual_partitioning_vertical
The docs you pointed to refer to "multiple engines", an unfamiliar
term. Perhaps you could mention both terms, or link from one to the
other. If I had seen the section you pointed to, I'd have understood
much better.
> If you'd like to call begin() explicitly, you might be better off
> leaving the "transactional" flag off - what this means is that when
> you're outside of a transaction, connections are returned to the
> connection pool after usage where their transactional state is rolled
> back, so that subsequent query operations will get the most recently
> available data. the only thing to be careful of is that when
> autoflush=True, you will be getting those flushes after each query
> which can't be rolled back if no transaction was begun.
The part that got by me was that autoflush=True and transactional=True
are the defaults; I removed them rather than explicitly setting them
to False and wondered why nothing changed.
> > Second, I'm having to call session.close() to get updated data outside
> > an explicit transaction, when I thought creating the sessionmaker with
> > autoflush=True should cause an update to occur every time I make a
> > query. Calling session.flush() does not help, regardless of whether I
> > specify autoflush.
>
> if your session is still within a transaction, then you wont see data
> from outside that transaction (depending on isolation levels) until
> that transaction is committed/rolled back (or closed). To see the
> behavior of flush(), turn on SQL echoing (always the first step when
> anything is not working as expected).
have discovered my mistakes a lot faster. :-)
Flushing was a no-op. Now I'm using transactional=False, when the
session is not in a transaction I still have to call session.close()
before I see updated data set from outside SQLAlchemy.
I tried setting the isolation level explicitly, but it didn't help:
In [29]: self.session.close()
In [30]: self.db._acm.execute('SET SESSION TRANSACTION ISOLATION LEVEL
READ COMMITTED')
In [31]: self.db._sucrose.execute('SET SESSION TRANSACTION ISOLATION
LEVEL READ COMMITTED')
In [32]: self.balance_from_uid(25482)
2008-03-31 14:44:12,023 INFO sqlalchemy.engine.base.Engine.0x..34
SELECT users.uid AS users_uid, users.netid AS users_netid, users.uin
AS users_uin
FROM users
WHERE users.uid = %s ORDER BY users.uid
LIMIT 0, 1
2008-03-31 14:44:12,023 INFO sqlalchemy.engine.base.Engine.0x..34
[25482]
2008-03-31 14:44:12,029 INFO sqlalchemy.engine.base.Engine.0x..34
SELECT vending.uid AS vending_uid, vending.balance AS vending_balance
FROM vending
WHERE vending.uid = %s
2008-03-31 14:44:12,029 INFO sqlalchemy.engine.base.Engine.0x..34
[25482L]
Out[32]: 99.5
-- change the balance to 100 elsewhere --
In [33]: self.balance_from_uid(25482)
2008-03-31 14:44:22,369 INFO sqlalchemy.engine.base.Engine.0x..34
SELECT users.uid AS users_uid, users.netid AS users_netid, users.uin
AS users_uin
FROM users
WHERE users.uid = %s ORDER BY users.uid
LIMIT 0, 1
2008-03-31 14:44:22,369 INFO sqlalchemy.engine.base.Engine.0x..34
[25482]
Out[33]: 99.5
Is this a MySQL issue (I tried READ UNCOMMITTED too) or is there some
other way to get updated data without closing the session?
Michael Bayer
The other thing you're probably hitting here is that the Session is
going to cache everything it loads until you clear out its data
(session.clear()) , or expire the data that it has loaded
(session.expire_all()). This is why it tends to work more nicely with
"transactional=True" in that regard. The pattern in your application
of creating one global session might be better be served by creating
sessions per unit of work, i.e. on an as-needed basis (or perhaps
within the scope of your Sucrose object). If its not a web
application, where the boundaries for creating sessions are very
simple (i.e. once per request), then you have to figure out what
boundaries you want to have amongst concurrent processes in your
application.
Nicholas Riley
> The other thing you're probably hitting here is that the Session is
> going to cache everything it loads until you clear out its data
> (session.clear()) , or expire the data that it has loaded
> (session.expire_all()). This is why it tends to work more nicely with
> "transactional=True" in that regard.
behavior.
> The pattern in your application
> of creating one global session might be better be served by creating
> sessions per unit of work, i.e. on an as-needed basis (or perhaps
> within the scope of your Sucrose object). If its not a web
> application, where the boundaries for creating sessions are very
> simple (i.e. once per request), then you have to figure out what
> boundaries you want to have amongst concurrent processes in your
> application.
a transaction is so I don't have to worry about what database I'm
accessing. The app is not a webapp, but creating a per-user session
would be pretty trivial.
Thanks again.