Thanks... I don't think my question stemmed from a lack of reading the extensive sqlalchemy documentation, but more of a limitation on my understanding of how sqlite works (or doesn't) with nested transactions. I know that you need the SingletonThreadPool or nested sessions completely block on the inner session.
My issue was that with the nested transaction (sess3 inside sess2) I thought that it should not have left 'jack' persisted after sess2 was rolled back. ie: I expect nesting behaviour like in MatthieuF's response here for SQLServer: http://stackoverflow.com/questions/527855/nested-transactions-in-sql-server After your (clearly frustrated...sorry?!?) response, I've been digging further and I do see that according to the Sqlite documentation "Transactions created using BEGIN...COMMIT do not nest. For nested transactions, use the SAVEPOINT and RELEASE commands". This explains why it did not work... it seems that the sess3 COMMIT also committed sess2. Relevant sqlite doc page here: http://www.sqlite.org/lang_transaction.html In short - what I observed has nothing fundamentally to do with SQLAlchemy, as I expected it might. But it does have me wondering about fiddling with SQLAlchemy and the sqlite dialect to try and automatically manage nested transactions using generated SAVEPOINTs and RELEASE. > so above, issue #1, SQLite doesn't support foreign key constraints by default. > They are accepted syntactically, but do nothing. My main issue was that "jack" remained after the rollback, not the fk constraint, although your comment certainly clarifies that (without enabling the new feature) I certainly can't have expected sqlite to complain that jack was not committed at the time of address storage. For future reference, the feature Mike mentioned that lets you change the foreign key constraint handling from the current default of "no constraints" is here: http://www.sqlite.org/foreignkeys.html On Oct 18, 12:57 pm, Michael Bayer <[email protected]> wrote: > On Oct 18, 2010, at 12:41 PM, Russell Warren wrote: > > > > > #sess2 will add a new user (jack), but we'll roll it back in the end > > sess2 = Session() > > jack = User('jack') > > jack.addresses = [Address(email_address='[email protected]'), > > Address(email_address='[email protected]')] > > sess2.add(jack) > > sess2.flush() #no commit > > so above, issue #1, SQLite doesn't support foreign key constraints by > default. They are accepted syntactically, but do nothing. Consult the > sqlite documentation (SQLite itself, not SQLAlchemy) for information on a > fairly recent feature that enables them to enforce the constraints. > > > #sess3 will create an address linked to jack (before he gets rolled > > back) > > sess3 = Session() > > addr3 = Address("[email protected]", jack_id) #but this id will be rolled > > back > > # adding to jack in another session succeeds even though jack is not > > commited... > > sess3.add(addr3) > > sess3.commit() > > > #Now rollback sess2, which should get rid of jack... but doesn't?? > > sess2.rollback() > > > print Session().query(User).all() > > issue #2, I've tried to emphasize this as much as possible in the > documentation, the SQLite dialect uses *one connection* for the thread, by > default. This default is being changed in 0.7 (so in 0.7, the common issue > will become "database is locked" issues, whereby I'll probably have to tell > people to switch back to SingletonThreadPool.... ). > > The "one connection per thread" default is discussed at the following > locations: > > http://www.sqlalchemy.org/docs/dialects/sqlite.html?highlight=sqlite#...http://www.sqlalchemy.org/docs/core/pooling.html?highlight=singleton#...http://www.sqlalchemy.org/docs/core/pooling.html#sqlalchemy.pool.Sing... > > and exactly what you are doing is mentioned at: > > http://www.sqlalchemy.org/trac/wiki/FAQ#Iamusingmultipleconnectionswi... -- 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.
