strange behavior with pylons and sqlalchemy threadlocal
arashf
best place for this is:
I'm seeing some interesting behavior with threadlocal sqlalchemy/
pylons.
after a session.commit() the next connection used is different from
the original despite the fact that the it's on the same thread/
request. isn't threadlocal was supposed to prevent this? here is a
code sample I'm running: http://pastebin.com/m40d94ca7. for some
reason, the connection id's returned are different. if this is the
expected behavior, is there any straight forward way to keep the same
connection or reacquire it?
for those wondering why I need this: I'm using the mysql GET_LOCK()
method to create application level locks that span multiple servers
and the lock must be released by the same connection that acquired
it.
I also need to release the lock /after/ the transaction is committed
(or rolled back) which is why I can't simply release the lock within
the initial transaction.
any help would be greatly appreciated. thanks!
Michael Bayer
On Jun 1, 2008, at 8:11 AM, arashf wrote:
>
> sorry for those on both the pylons/sqlalchemy lists. not sure what the
> best place for this is:
>
> I'm seeing some interesting behavior with threadlocal sqlalchemy/
> pylons.
> after a session.commit() the next connection used is different from
> the original despite the fact that the it's on the same thread/
> request. isn't threadlocal was supposed to prevent this?
no. when the connection proxy is closed, if no other actors within
that thread have a grip on that object, the underlying DBAPI
connection is back in the pool. the next checkout will return any
connection available from the pool. This is described at: http://www.sqlalchemy.org/docs/04/dbengine.html#dbengine_implicit_strategies
.
> expected behavior, is there any straight forward way to keep the same
> connection or reacquire it?
yes. Bind the Session to a specific connection at the start of the
request, and tear it down at the end. There is an example of this in
the Pylons tutorial http://wiki.pylonshq.com/display/pylonsdocs/Using+SQLAlchemy+with+Pylons
- about 1/3rd into it, search for the string "to use just a single
database connection per request".
arashf
thanks for the response. at high traffic levels, the repeated
connecting/disconnecting have visible performance impact, no? are
there any other solutions available which still use the connection
pool and somehow still holding a grip to the original connection so
it's reused?
On Jun 1, 6:25 am, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On Jun 1, 2008, at 8:11 AM, arashf wrote:
>
>
>
> > sorry for those on both the pylons/sqlalchemy lists. not sure what the
> > best place for this is:
>
> > I'm seeing some interesting behavior with threadlocal sqlalchemy/
> > pylons.
> > after a session.commit() the next connection used is different from
> > the original despite the fact that the it's on the same thread/
> > request. isn't threadlocal was supposed to prevent this?
>
> no. when the connection proxy is closed, if no other actors within
> that thread have a grip on that object, the underlying DBAPI
> connection is back in the pool. the next checkout will return any
>
> > expected behavior, is there any straight forward way to keep the same
> > connection or reacquire it?
>
> yes. Bind the Session to a specific connection at the start of the
> request, and tear it down at the end. There is an example of this in
Michael Bayer
On Jun 1, 2008, at 6:39 PM, arashf wrote:
>
> hi mike,
> thanks for the response. at high traffic levels, the repeated
> connecting/disconnecting have visible performance impact, no?
Not at all. A checkout without any existing connection bound to
the thread takes something like 30 function calls (a checkout with an
already-thread-associated connection is like 5 function calls). It's
completely miniscule to do a full checkout once per request (or even
five or six times per request). It might add up to a second or two
per 10K requests.
> are
> there any other solutions available which still use the connection
> pool and somehow still holding a grip to the original connection so
> it's reused?
If you want to use the SingletonThreadPool with no size limit, that
will permanently bind each connection to the current thread. Its
designed for sqlite and would not allow you to open a second
connection on the same thread (which you'd want to do if you wanted to
have two transactions simultaneously, for example), but if you really
wanted to you could have a second engine for that purpose. You'd
definitely would want to ensure that the app environment you're using
has a fixed pool of threads which is never size-managed. I find this
approach to be fairly brittle myself and I'd opt for an explicit
connection setup/teardown per request.
arashf
getting sporadic exceptions that looks like this (i've never gotten
these before):
WebApp Error: <class 'sqlalchemy.exceptions.OperationalError'>:
(OperationalError) (2013, 'Lost connection to MySQL server during
query')
WebApp Error: <class 'sqlalchemy.exceptions.ProgrammingError'>:
(ProgrammingError) (2014, "Commands out of sync; you can't run this
command now")
WebApp Error: <class 'sqlalchemy.exceptions.InvalidRequestError'>:
This Connection is closed
any ideas?
Michael Bayer
On Jun 2, 2008, at 5:11 AM, arashf wrote:
>
> that sounds perfect. unfortunately, I tried this in production and am
> getting sporadic exceptions that looks like this (i've never gotten
> these before):
>
> WebApp Error: <class 'sqlalchemy.exceptions.OperationalError'>:
> (OperationalError) (2013, 'Lost connection to MySQL server during
> query')
>
> WebApp Error: <class 'sqlalchemy.exceptions.ProgrammingError'>:
> (ProgrammingError) (2014, "Commands out of sync; you can't run this
> command now")
>
> WebApp Error: <class 'sqlalchemy.exceptions.InvalidRequestError'>:
> This Connection is closed
>
> any ideas?
my initial guess is that you're sharing a connection between threads.
what specifically is "this" ? the recipe in the Pylons tutorial ? or
the threadlocal pool ? I really think you should go with what the
pylons tutorial has to say. SingletonThreadPool is not widely used
with MySQL so I'm not sure if there are caveats surrounding its usage
in that regard (though it has been used successfully).
arashf
code here:
http://pastebin.com/m1b47b4aa
thanks again for your help. also, is there a lower latency way I can
catch a hold of you today? irc perhaps? :)
arashf
shared between threads.
Michael Bayer
on a thread-local basis; we generally recommend that you perform ad-
hoc executions of SQL through session.execute() so that the current
Session is the single point of all access to the current connection/
transactional context. There *is* a version of MetaData called
"ThreadLocalMetaData" which would eliminate the threading issues
you're having, but it still is better for you to stick with the single
point of execution so that all executions are on the current
transaction.
arashf
I tried ThreadLocalMetaData() with similar results (primarily 'This
Connection is closed' and 'This transaction is inactive'). we do a mix
of session.connection style queries and ORM queries and we can't
really change this over night :P. is there anything else I can try?
arashf
http://pastebin.com/m45a6e938
any suggestions appreciated :)
Michael Bayer
errors ? have a stack trace ?