QueuePool limit reached for Flask application

Question

I am running an application with Flask and Flask-SQLAlchemy.

from config import FlaskDatabaseConfig
from flask import Flask
from flask import request
from flask_migrate import Migrate
from flask_sqlalchemy import SQLAlchemy

application = Flask(__name__)
application.config.from_object(FlaskDatabaseConfig())
db = SQLAlchemy(application)

@application.route("/queue/request", methods=["POST"])
    def handle_queued_request():
        stuff()
        return ""

def stuff():
    # Includes database queries and updates and a call to db.session.commit()
    # db.session.begin() and db.session.close() are not called
    pass

if __name__ == "__main__":
    application.run(debug=False, port=5001)

Now, from my understanding, by using Flask-SQLAlchemy I do not need to manage sessions on my own. So why am I getting the following error if I run several requests turn by turn to my endpoint?

sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30 (Background on this error at: http://sqlalche.me/e/3o7r)

I've tried using db.session.close() but then, instead of this error, my database updates are not committed properly. What am I doing incorrectly? Do I need to manually close connections with the database once a request has been handled?

score 5 · Accepted Answer · answered Jun 28 '18 at 16:05

5

I have found a solution to this. The issue was that I had a lot of processes that were "idle in transaction" because I did not call db.session.commit() after making certain database SELECT statements using Query.first()

To investigate this, I queried my (development) PostgreSQL database directly using:

SELECT * FROM pg_stat_activity

answered Jun 28 '18 at 16:05

Konrad

852
11
31

1

Why is there a need to commit after a select query? I have the same problem but don't understand why this is the case. Could you elaborate more? – Logan Yang Jul 27 '19 at 18:06
Hi, I think that this fix did not solve the root of the problem. There may be in an issue in how you create your db connections in Flask. Personally, I think I had circular imports which created two connections for each request and one of them was not closed. Another potential issue was that I directly used model entities to make queries (like `User.query.get` instead of `db.session.query(User).get`. So I'd suggest checking how you create your db (see https://stackoverflow.com/questions/54637324/shared-object-across-threads-in-flask for instance) and usage of queries from your model objects – Konrad Jul 28 '19 at 15:59

score 3 · Answer 2 · answered May 27 '19 at 02:22

3

Just remove connection every time you make a query session to db.

products = db.session.query(Product).limit(20).all()
db.session.remove()

answered May 27 '19 at 02:22

Minh DN

31
3

3

Hi Minh, thanks for your answer. Isn't this already handled by flask-sqlalchemy? – Konrad May 29 '19 at 10:07
1

@Konrad As of 04/12/2021, the answer is NO. Which is what I found out just now. – Jesus Zamora Dec 04 '21 at 23:56
Hi Minh, I have better experience with Flask now and this is not an error I face nowadays. As far as I can remember the issue was related to idle database sessions. We currently have an application in production and we have never needed to call db.session.remove() manually. – Konrad Dec 05 '21 at 13:32

QueuePool limit reached for Flask application

2 Answers2