Thank you very much Michael ! It makes perfectly sense.
From: [email protected]
Subject: Re: [sqlalchemy] Questions about sessions & connections
Date: Wed, 22 Dec 2010 11:07:22 -0500
To: [email protected]
On Dec 21, 2010, at 5:03 AM, Franck Vonbau wrote:Dear all,
I'm starting a web project based on Python, and I've decided to rely on web.py
and SQLAlchemy.
My DB is for the moment a SQLite database, and I'm trying to figure out how the
framework deals with sessions / transactions. Your help would be greatly
appreciated !
Here's how I declare my global Session :
engine = create_engine('sqlite:///.....', echo = True, listeners=[MyListener()])
Session = scoped_session(sessionmaker(bind=engine))
Q1) The documentation recommends to use that kind of scoped_session for a web
project : why, actually ? What if I use "regular" sessions ? What would happen
in that case if different users of my site would get their own sessions ?
the scoped_session is a "regular" session, it just places them into a thread
local registry. This so that distinct web requests running in separate
threads each get a Session object dedicated to their local scope of work, while
allowing a global "Session" object that can be accessed from everywhere without
the need to explicitly pass it to all functions and methods.
I've made a simple test : I declare 2 actions, 'add' and 'commit'.
'add' deliberately doesn't commit the Session because I want to understand the
behavior of the framework.
class Add:
def GET(self, name=None):
print Session.connection()
print engine.pool.__dict__
for conn in engine.pool._all_conns:
print "[CONN] %s" %conn
if prenom is not None :
user = User(name)
Session.add(user)
# No commit here
class Commit:
def GET(self):
Session.commit()
I ran several tests : I triggered the 'Add' action from different browsers,
adding 'Jack' then 'John'....
Sometimes 'Jack' appears in Session.new, sometimes not.
Sometimes Session.new is empty, sometimes not.
It seems to depend on the underlying connection of the session.
I would imagine that web.py is using a pool of threads. As each request
enters, a thread is chosen randomly from the pool. If the thread is the one
that you happened to use previously for a particular operation, its previous
state is still left around. Its for this reason the documentation recommends
closing out sessions at the end of each request, see
http://www.sqlalchemy.org/docs/orm/session.html#lifespan-of-a-contextual-session
.
Q2) Why's that ? Shouldn't the session be somehow "global" ? Why doesn't it
encapsulate all my users ?
scoped_session provides a Session object that is local per thread. You
wouldn't want to use a single Session for all web requests, as the Session
represents a single transaction, a collection of objects and state local to a
particular operation - your web request. The metaphor used by the tutorial is
that having one session for all requests is like throwing a banquet and having
all your guests eat from the same plate.
Q3) Moreover, what happens to the connections I open when I add a user without
committing ?
if you leave the Session hanging open, it holds onto the connection/transaction
resources it has established.
Why are they recycled by the framework on the next call ? My listeners
indicates they're never checked-in (except on commit time)
you must explicitly close out the session using rollback(), commit(), or
close(), or with scoped_session you can also use remove(). web.py should have
some kind of hook so that this operation can be automated.
Q4) When I run the "commit" action, it seems to randomly pick a connection and
flush the objects. Why's that ?
again there's nothing "random" going on in the Session, this is probably a
function of your test which is choosing different sessions based on which
thread web.py is picking. Just a guess.
Besides , I'm trying to restrict pool_size to 1 in the engine.
Q5) It does not seem to change anything : more and more connections are open
when I run "add" multiple times. How could I actually restrict the pool size ?
the pool limits to 15 connections total by default, per engine. If you are
seeing more than 15 connections simultaneously, this would correspond to more
than one Engine being created, or subprocesses being created perhaps. I
haven't used web.py so perhaps they have some guidelines on ORM integration.
The configuration of the pool itself is controlled by the "pool_size" and
"max_overflow" parameters, see
http://www.sqlalchemy.org/docs/core/pooling.html#connection-pool-configuration .
Q6) What is the best strategy when using scoped_session in a web project ?
Forgetting a commit seems to have very dangerous consequences (delayed flush).
the guidelines at
http://www.sqlalchemy.org/docs/orm/session.html#lifespan-of-a-contextual-session
should be followed, so that the lifespan of the Session local to a thread is
linked to that of the web request.
Thanks very much for your help !
Franck
--
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.
--
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.
--
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.
