> > ie: I expect nesting behaviour like in MatthieuF's response here for > > SQLServer: > >http://stackoverflow.com/questions/527855/nested-transactions-in-sql-... > > OK, that's a behavior specific to stored procedures in SQL server, not one I > was aware of. To my knowledge, a "nested transaction" is not really a normal > thing in SQL, usually if you want such a thing you use SAVEPOINT - which > SQLA's ORM session provides via "begin_nested()". > > The usual behavior of transactions with DBAPI is that there is only one > transaction at a time - if you were to call "BEGIN" repeatedly, the > subsequent calls have no effect. Incidentally, the BEGIN is implicit with > DBAPI. SQLAlchemy doesn't issue any kind of BEGIN, only > connection.rollback() or connection.commit().
Thanks for clarifying this. Among other things, I thought the BEGINs where being issued at a higher level. > > 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". > > 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. > > yeah you need to use begin_nested(). We don't allow any implicit nesting at > the ORM level since it just confuses people. What I've ended up doing here is severely limiting my use of .commit(). I now use .flush() instead in most cases. I originally had nested commits in several locations where the inner commits were in place solely to get access to things like the autoincrementing id in a table on the next line of code. With my thinking that commits *could* be nested I did not think it was an issue. Unit tests told me otherwise, though. Worth noting is that me doing these inner commits (when I knew full well i wasn't complete with a transaction I may roll back) is rooted in the fact that you have this direct demonstration in the ORM tutorial. You demonstrate that id == None until you do a commit. I was following this, although in hindsight I feel a bit silly since I now recognize the problem and that know that flush() is much more appropriate. -- 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.
