On Oct 18, 2010, at 3:10 PM, Russell Warren wrote:
> 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
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(). So given that, and the thread-local connection
behavior, the conversation with sqlite looks like:
connection = sqlite3.connect(':memory:')
<execute SQL with the connection>
connection.commit()
<execute SQL with the connection>
connection.commit()
etc.
>
> After your (clearly frustrated...sorry?!?) response,
yikes, not intended at all, guess I don't have a clear view of how my emails
sound ....
> 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".
Well that's news to me that SQLIte supports SAVEPOINT. I just tried enabling
SQLite for our SAVEPOINT tests and got an error at the point at which it
emitted "SAVEPOINT", so this must be something extremely new in SQLite, not
supported by the stock version that comes with Python 2.7.
> 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.
yeah you need to use begin_nested(). We don't allow any implicit nesting at
the ORM level since it just confuses people.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
