Flask-SQLAlchemy TimeoutError

Question

My backend configuration is :

• Ubuntu 12.04
• Python 2.7
• Flask 0.9
• Flask-SQLAlchemy
• Postgres 9.2

I've got this error message:

TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30

Do I need to close explicitly the db.session? Shouldn't be the connection back to pool when session goes out of scope?

Answer 1

This could happen if you are using debug=True in your application and you have loaded several pages or API endpoints which errored out.

The reason is that running the debug version of the app keeps a live debugger open in the error page. This live debugger keeps around all the resources from processing the request, so that you can examine them. This means that the database connection can't be recycled.

You shouldn't use debug mode for the production version of your app (apart from problems like this it is a security risk), and the debugger often won't work anyway (it is designed to work with the flask test server, not with gunicorn). Therefore in prod the solution is to turn off debug.

If you have this problem in dev using debug mode - this is a limitation. You shouldn't be hitting the dev server so hard, or you can increase the limit. Be aware that 15 connections are usually plenty to serve a large number of concurrent requests when they are being recycled properly. It's only in debug that they tend to run out.

Answer 2

Flask-SQLAlchemy manages the connection pool for you, so in general, it should not be necessary to do this. However, there are some instances in which it cannot control this, specifically if you are executing queries outside a request context or using with app.app_context() somewhere.

When I paired Flask-SQLAlchemy with apscheduler, I found myself having to explicitly close sessions in the jobs the scheduler executed, or after several hours of running I would get this error.

Answer 3

I had to add the @app.teardown_request method, as well:

@app.teardown_request
    def checkin_db(exc):
        user_store.db_session.remove()

I followed the "official" FlaskSecurity Basic SQLAlchemy Application with session quick start and after a couple of requests got the "sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30 error". Adding the above code seems to have fixed the issue:

• FlaskSecurity: version 3.0.0
• Flask: version 1.0.2