On Mar 31, 1:34 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > 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).
Ah, so the transaction commit was a total red herring. Whoops. > 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. OK, makes sense. Transactional memory people call this behavior "flat 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. I searched for "multiple databases" and ended up here: 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. Turns out I don't want transactional -or- autoflush enabled, then. 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). Yes, I think if I had been able to see the SQL being executed I would 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? --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
