Threading and SQLAlchemy Sessions

Question

I know this question has been asked several times, but I'm still not sure about using scoped_session with streaming processing. Basically, I have an application with 10 workflows. I have one Engine with a connection pool size of 11. Each thread has its own session, and no information should be shared between thread sessions (if they could, that would be nice, but I created a workaround). Finally, I use SQLAlchemy Core is the main thread for complex SQL statements, so I have 11 threads in the connection pool.

I am using MySQL and my pool_recycle is 3600. I keep getting errors:

(OperationalError) (2013, 'Lost connection to MySQL server during query')

This never happened when I had only one worker thread, even without the pool_recycle set. I have a very common understanding of MySQL and SQLAlchemy, so I'm not sure if my problems are with using SQLAlchemy or MySQL (or none of the above).

Here is my setup:

 common = Common() class Common(object): def __init__(self): ... self.engine = create_engine( '%(type)s://%(username)s:%(password) s@ %(endpoint)s:%(port)s/%(name)s?charset=utf8' % { 'type': config.get('db_type'), 'username': 'foo', 'password': 'bar', 'endpoint': config.get('db_endpoint'), 'port': str(config.get('db_port')), 'name': config.get('db_name'), }, encoding='utf-8', pool_size=config.get('num_workers') + 1, pool_recycle=3600, ) self.session = sessionmaker(bind=self.engine) 

Each worker calls self.session = common.session() and uses this session everywhere.

Answer 1

According to the documentation, you should use different instances of the engine for each subprocess, in your case you have one engine for all subprocesses, because the connection pool between subprocesses cannot be shared (as I understand it).

http://docs.sqlalchemy.org/en/rel_0_8/core/connections.html