uwsgi + sqlalchemy - forking concern

[Jonathan Vanasco]

i'm using sqlalchemy in pyramid, under uwsgi.  

i've been reading up on forking issues that can arise in this stack.

from what I understand, issues arise because the "SqlAlchemy Connection" is established before the fork, resulting in each forked process potentially trying to use the same connection.  One of the fixes is to (re)connect after the fork.

This is where i'm confused...  I don't know what a "SqlAlchemy Connection" refers to.

I've read up on the uwsgi, sqlalchmy and pylons lists + docs -- I can't get any better info on what this SqlAlchemy connection is, or what component is having the issues.

If the issue is with the SqlAlchemy engine connecting to the database, and it's internal db connection, then I should be fine -- SqlAlchemy doesn't connect in my apps until the first request.  I verified this with postgresql logs.

If the issue is with creating a SqlAlchemy engine or sessionmaker, then I will need to rewrite some stuff.

Does anyone know the specifics of the "connection" that has fork issues?

[Jeff Dairiki]

A problem that can arise when using sqla connection pooling is (I'm not
sure that this happens under all conditions, but it has happened to me):

1. In the master process (e.g. in pyramid main()), engine is set up
(with pooling) a sqlconnection made, used, and closed. The connection,
in this case is not actually closed, but just returned to the pool.

2. Fork (e.g. uwsgi forks the workers)

3. Each child process thinks it's got a connection in its pool. The
problem is it's the same connection in each process. When multiple
process try to use the same connection, it's bad, obviously.

One fix, if you make connections in the master process, is, instead
of closing the connection (connection.close()) invalidate it
(connection.invalidate()). Then the underlying connection is
actually closed, rather than returned to the pool.

There some on this in this thread:
https://groups.google.com/forum/#!searchin/sqlalchemy/UnicodeEncodeErrors/sqlalchemy/Xf0fWsCqdCg/5-6aRpYcuecJ

Jeff

[Mike Bayer]

yeah what he said, but also, in a forking situation the easiest thing
you can do is, as soon as the fork starts, say engine.dispose(). Or
just engine = <some new engine>. all you need to do is make sure no DB
connections in the pool (or anywhere else) are allowed to continue in
the fork.

[Jonathan Vanasco]

Thanks Michael & Jeff.

I'm reading this as the issue is in the underlying dbconnection that sqlalchemy has , not creating a sqlalchemy engine.

If that is the case, then I don't have anything to worry about now -- but will add in an `engine.dispose()` hook after the fork just to be safe.