On Mar 1, 2011, at 11:41 PM, Romy wrote: > On Mar 1, 2:29 am, Michael Bayer <mike...@zzzcomputing.com> wrote: >> On Mar 1, 2011, at 1:42 AM, Romy wrote: >> >>> Getting some conflicting advice on autocommit and wrapping the request >>> in a try/except block on the Tornado mailing list, was wondering what >>> your thoughts are on the issues brought up in the following message >>> and its replies: >> >>> http://groups.google.com/group/python-tornado/msg/d06a7e244fc9fe29 >> >> I have never worked with async servers so I don't have much wisdom on the >> best usage patterns with relational databases, I think the suggestion to >> wrap individual queries in a try/except defeats the one of the purposes of >> using a transactional, relational database. It shouldn't be an exotic need >> to treat several SQL statements in succession as part of a single logical >> series of operations, that series of operations linked to the scope of a >> single HTTP request. It's of course optional, though I'd like to think I >> emit four INSERT statements in a request, then the fifth one fails, I can >> roll the whole thing back. Similarly that I can emit SELECT statements that >> will share the same isolated environment of one transaction, won't release >> row locks before I'm done, etc. > > Don't let the async nature throw you -- my code is 99% synchronous and > in the async calls I can easily handle the database behavior manually. > > WRT your 5 insert example, what's wrong w/ explicitly marking those > single logical units inside a BEGIN ... COMMIT while running > autocommit ?
If they are truly unrelated things, then yes there's nothing logically wrong with them being in separate transactions. The commit and/or rollback has overhead, as well as within SQLAlchemy itself there's overhead to the demarcation of transactions. If you have expire_on_commit=True, then the work involved between statements is greater as the Session can't even return to you any data from the previous transaction, it all has to be reloaded. Then there's just the basic nature of what using a transaction means. Your third operation fails, the request throws an error. What ever you changed in the first two operations succeeds and remains permanent. A lot of apps are not OK with that, certainly not any I write. > Same goes for wrapping SELECTs and anything else you > might need. I've found there to be nothing awkward about this approach > when I had autocommit on. Yeah everyone that's used MySQL for all these years with MyISAM tables, the default, has this behavior, there's no transactions. Its not awkward at all and is extremely common. Just not the way I like to write applications, I prefer what my requests do are contained within transactions and don't leave side effects if they fail. SQLAlchemy around version 0.4/0.5 was very specifically aimed at the latter use case. > > How do you feel about the effects on locking as it pertains to > elongated transactions ? I would never call the span of a single web request an "elongated transaction". As long as you're using an MVCC concurrency model, locking should only be down to rows that have been updated or deleted and should not be adding latency anywhere that it's not appropriate. > It looks like the more strict the isolation > mode, the more this hurts concurrency. Even at the repeatable-read > defaults this could lead to SELECTs issuing write-locks for as long as > they're in a transaction. PG's default is "Read committed". SELECTs don't lock. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@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. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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.
