Wrapping request handlers in a try/except blanket (ExceptionStackContext ?)

[Romy]

So, it appears the recommended pattern for using SQLAlchemy w/ Tornado
would be something like this:

try:
RequestHandler()
session.commit()
except:
session.rollback()
raise
finally:
session.remove()

I have a few dozen request handlers in my code. What's the best way to
get all of them to abide by the above structure, preferably w/out
changing Tornado internals ? I've looked at ExceptionStackContext, but
am not certain that it's applicable to what I'm attempting.

[Ben Darnell]

You can use a StackContext for the 'except' clause but not for the try/finally parts because the StackContext will get evaluated multiple times.  The best way to do this is probably to override finish() and either commit or rollback (and then remove) depending on whether an error occurred.  You may be able to tell whether an error occurred by checking self.get_status() >= 400, or you may prefer to override _handle_request_exception() to set a flag on the object.

-Ben

[Ben Darnell]

Actually, scratch that.  Overriding finish() is the right way to do cleanup at the end of a possibly-asynchronous handler, but you really don't want to hold a database transaction open that long.  I would strongly recommend using explicitly-scoped transactions within synchronous blocks instead of a transaction that automatically spans the entire handler.

-Ben

[Romy]

I had the same concern and lengthy discussion on the subject w/
SQLAlchemy's Michael Bayer.

Can't find that thread (thanks google group search), so I'll quote him
from another: "I will comment that DBAPI has no begin() method. when
you use DBAPI with autocommit=False, youre in a transaction - always.
SQLAlchemy defines a "transaction" abstraction on top of this that
"pretends" to have a begin."

So, by default I'll be in a DB transaction whether or not I explicitly
scope my transactions in code. This is somewhat mind-boggling, TBH,
but appears to be related to transaction isolation levels.

With that in mind, whether or not I explicitly scope my transactions
appears to be irrelevant. The point is made further moot by the fact
that I'm currently using MyISAM, albeit reserving the right to switch
to something like InnoDB and rely on actual transactional behavior.

With that in mind, you still recommending your original approach ?

R

On Feb 26, 12:57 pm, Ben Darnell <b...@bendarnell.com> wrote:
> Actually, scratch that.  Overriding finish() is the right way to do cleanup
> at the end of a possibly-asynchronous handler, but you really don't want to
> hold a database transaction open that long.  I would strongly recommend
> using explicitly-scoped transactions within synchronous blocks instead of a
> transaction that automatically spans the entire handler.
>
> -Ben
>
>
>
>
>
>
>
> On Sat, Feb 26, 2011 at 12:35 PM, Ben Darnell <b...@bendarnell.com> wrote:
> > You can use a StackContext for the 'except' clause but not for the
> > try/finally parts because the StackContext will get evaluated multiple
> > times.  The best way to do this is probably to override finish() and either
> > commit or rollback (and then remove) depending on whether an error occurred.
> >  You may be able to tell whether an error occurred by checking
> > self.get_status() >= 400, or you may prefer to override
> > _handle_request_exception() to set a flag on the object.
>
> > -Ben
>

[Ben Darnell]

So why not just turn on autocommit?

-Ben

[Romy]

Whether autocommit's on or off, I still have to handle errors that get
thrown from any DB statement, including simple queries outside of
transactions that can fail when, say, MySQL server times out and
closes the connection.

FWIW I started with autocommit on, had a few issues and switched over
to non-autocommit, which is the default in SqlAlchemy, probably
because it's supposed to have less overhead.

[Ben Darnell]

On Sat, Feb 26, 2011 at 3:55 PM, Romy <romy.m...@gmail.com> wrote:

Whether autocommit's on or off, I still have to handle errors that get
thrown from any DB statement, including simple queries outside of
transactions that can fail when, say, MySQL server times out and
closes the connection.

Right, but you don't need to commit/rollback in that case, just close the connection and recreate it as needed.  It's much simpler because each query can have an independent try/except block instead of trying to do something scoped to the RequestHandler.  This logic can in fact be pushed down into your database query function (this is what the tornado.database module does, and I think sqlalchemy might do something similar).

 

FWIW I started with autocommit on, had a few issues and switched over
to non-autocommit, which is the default in SqlAlchemy, probably
because it's supposed to have less overhead.

Non-autocommit might reduce overhead for some workloads (but not by much on a good database), but if it causes transactions to last longer than strictly necessary I would expect locking issues to become a problem.

-Ben

[Romy]

On Feb 26, 9:44 pm, Ben Darnell <b...@bendarnell.com> wrote:
> > FWIW I started with autocommit on, had a few issues and switched over
> > to non-autocommit, which is the default in SqlAlchemy, probably
> > because it's supposed to have less overhead.
>
> Non-autocommit might reduce overhead for some workloads (but not by much on
> a good database), but if it causes transactions to last longer than strictly
> necessary I would expect locking issues to become a problem.

With SqlAlchemy, using autocommit adds additional overhead, such as
fetching / returning connections to the connection pool for every
query, etc..

I share your concern about the longer-than-necessary transactions and
effects on locking, however Michael seems pretty adamant on avoiding
autocommit, here are a few quotes:

- "autocommit" isn't the current "mainstream" way to do things,
usually for web apps we recommend one session per request, which
maintains a transaction until commit() or rollback() is called, using
an enclosure scheme like [http://www.sqlalchemy.org/docs/orm/
session.html%23lifespan-of-a-contextual-session]
- the way DBAPI works, there is always a transaction. The BEGIN is
happening no matter what. See "commit" in http://www.python.org/dev/peps/pep-0249/
which describes "any auto-commit provided by the DBAPI must be off
initially". DBAPI-level autocommit features are scattered and
inconsistent so SQLAlchemy doesn't use them.

I'm now somewhat torn on the issue, and probably spent too much time
on the subject at this point.

R

[Romy]

Fixing broken link:

http://www.sqlalchemy.org/docs/orm/session.html#lifespan-of-a-contextual-session

On Feb 28, 10:29 pm, Romy <romy.maxw...@gmail.com> wrote:
> On Feb 26, 9:44 pm, Ben Darnell <b...@bendarnell.com> wrote:
>
> > > FWIW I started with autocommit on, had a few issues and switched over
> > > to non-autocommit, which is the default in SqlAlchemy, probably
> > > because it's supposed to have less overhead.
>
> > Non-autocommit might reduce overhead for some workloads (but not by much on
> > a good database), but if it causes transactions to last longer than strictly
> > necessary I would expect locking issues to become a problem.
>
> With SqlAlchemy, using autocommit adds additional overhead, such as
> fetching / returning connections to the connection pool for every
> query, etc..
>
> I share your concern about the longer-than-necessary transactions and
> effects on locking, however Michael seems pretty adamant on avoiding
> autocommit, here are a few quotes:
>
> - "autocommit" isn't the current "mainstream" way to do things,
> usually for web apps we recommend one session per request, which
> maintains a transaction until commit() or rollback() is called, using
> an enclosure scheme like [http://www.sqlalchemy.org/docs/orm/
> session.html%23lifespan-of-a-contextual-session]
> - the way DBAPI works, there is always a transaction.  The BEGIN is

> happening no matter what.   See "commit" inhttp://www.python.org/dev/peps/pep-0249/

[Artemi Krymski]

just use tornado.database and plain sql, alchemy just causes pain down the road :)

[Romy]

Do you speak from experience ? If so I'd like to hear more details.

Never been a fan of extra layers of abstraction, so I've certainly
been considering this option. Would require rewriting a bunch of code
at this point, though..

On Mar 2, 2:15 am, Artemi Krymski <akrym...@gmail.com> wrote:
> just use tornado.database and plain sql, alchemy just causes pain down the
> road :)
>
>
>
>
>
>
>
> On Tue, Mar 1, 2011 at 6:32 AM, Romy <romy.maxw...@gmail.com> wrote:
> > Fixing broken link:
>

> >http://www.sqlalchemy.org/docs/orm/session.html#lifespan-of-a-context...

[Artemi Krymski]

yep, we've found that you quickly outgrow alchemy and it begins to get in the way of getting mysql to perform how you want (using the indices you want, etc).  to get decent performance from mysql you need to use it "raw".  there are lots of little things that can make a 1ms query take 1s, i've seen it happen.  you must have fine grained control over the sql - joining, insert ignore, on duplicate key update, etc.

alchemy is a huge abstraction layer - and for what?  we've rewritten the tornado.database module slightly:

https://gist.github.com/850758

to contain basic insert and update functions for auto-generating those tedious sql queries that you dont want to write by hand, and adding "debug" mode that prints the actual SQL that gets run (nicely formatted and in colour ;)  so you can do: db.insert("Table", column1=v1, column2=v2, **row)

personally i avoid joins whenever i can, and do them in code instead, eg - pull out a few rows, put the IDs in a set and pull out a few more rows with SELECT FROM X WHERE id IN ( .. )

this is what facebook and other big sites do - mysql performance much better and more consistently as a basic key-value store.

my point is there will be times when you want to control how data gets retrieved, whether fine tuning sql, or performing client side joins, etc.  alchemy gets in the way of this without adding much, never mind all the strange issues with transactions you seem to be encountering.

if you know sql why not write it by hand? it's really not as hard as people think :)  

[Cliff Wells]

On Wed, 2011-03-02 at 10:56 +0000, Artemi Krymski wrote:

>
> personally i avoid joins whenever i can, and do them in code instead,
> eg - pull out a few rows, put the IDs in a set and pull out a few more
> rows with SELECT FROM X WHERE id IN ( .. )
>
>
> this is what facebook and other big sites do - mysql performance much
> better and more consistently as a basic key-value store.

Why use a relational database as a basic key/value store at all? There
are other options that are better suited to this type of data:

http://en.wikipedia.org/wiki/NoSQL#Key.2Fvalue_store

I know a lot of big sites (ab)use MySQL as a "hash in the sky", but that
probably has more to do with the fact that they were designed before
NoSQL options were widely accepted than any hard technical reasons.

If you have relational data, I'd suggest PostgreSQL. If you just need
key/value, I'd suggest researching a NoSQL database instead.

Cliff

[Artemi Krymski]

Cliff, we've looked at many options.  NoSQL solutions aren't nearly as mature as MySQL.  I don't know of a single large deployment, so there's no way I'm betting my whole backend on some new engine.

I'm not using MySQL as just a key-value store mind you, I use everything else too (indices, groups, replication, etc).  I just try to avoid joins whenever i can

[Cliff Wells]

On Wed, 2011-03-02 at 12:47 +0000, Artemi Krymski wrote:
> Cliff, we've looked at many options. NoSQL solutions aren't nearly as
> mature as MySQL. I don't know of a single large deployment, so
> there's no way I'm betting my whole backend on some new engine.

Hi Artemi,

Google and Amazon both use NoSQL solutions, so that's two. The BBC is
using CouchDB. meebo.com (alexa rank 1132) also uses CouchDB.
businessinsider.com (alexa rank 786) uses MongoDB for everything,
including serving images. I'm sure 30 minutes on Google would turn up
plenty more.

http://www.couch.io/case-study-bbc

As an aside, I do find a bit of humor in the idea that MySQL is more
trustworthy than well... almost anything. The only widely-deployed
database I trust less would be BerkeleyDB.

> I'm not using MySQL as just a key-value store mind you, I use
> everything else too (indices, groups, replication, etc). I just try
> to avoid joins whenever i can

Joins are central to the relational paradigm (hence the name). Indices
and replication are not (in fact, replication is usually easier with
most NoSQL databases). Grouping and aggregation are also strong points
of the map/reduce paradigm.

In any case, you know your own data best, but it sounds to me like you
haven't really given NoSQL fair consideration.

Regards,
Cliff

[Artemi Krymski]

No reason to have a NoSQL debate here, check out the quora post:

http://www.quora.com/Why-does-Quora-use-MySQL-as-the-data-store-rather-than-NoSQLs-such-as-Cassandra-MongoDB-CouchDB-etc

Google and Amazon have built their own solutions.  Simple DB is alright, but too restrictive.

I agree that BerkeleyDB is great, I've used it before, but we're working with amazon rds now.

Will look into couch db when i have some time, but prefer to stick with "tried and tested" :)

[notedit]

hi  romy:

i use tornado and sqlalchemy with autocommit=True, but i blocks my query.  do you knows the reason? 

在 2011年2月27日星期日UTC+8下午1时44分52秒，Ben Darnell写道：