Flask and SQLAlchemy causes a lot of IDLE in transaction connections in PostgreSQL

Question

I have a Flask web application that uses SQLAlchemy to access a PostgreSQL database.

When I start the application, there is instantly created an " in transaction" connection in PostgreSQL.

When the application has been used for some time, several of these connections appear in pg_stat_activity.

After some time, it appears that deadlocks occurs on some resources, and I have to restart the application to get it working again.

I have read that this can happen, if I return from a view function that uses database before closing the db session. So in order to avoid this problem, I have created the following decorator:

@app.teardown_appcontext
def shotdown_session(exception=None):
    db.session.remove()

This should cause all sessions to be closed after each request and effectively avoid the problem of having " in transaction" connections.

Unfortunately, it does not seem to have any effect.

So, how do I really solve this problem?

UPDATE:

I should probably add, that I have verified that my decorator function is actually run. I verified this by adding a print to it:

@app.teardown_appcontext
def shotdown_session(exception=None):
    print "@app.teardown_appcontext: shotdown_session()"
    db.session.remove()

I have also verified that it is indeed run AFTER return of the view function by adding a print to the view function as well:

[...]
products = db.session.query(...).all()
print "BEFORE RETURN"
return render_template("show_products.html", products=products)

This produces log lines like these:

 * Running on http://0.0.0.0:5000/
 * Restarting with reloader
BEFORE RETURN
@app.teardown_appcontext: shotdown_session()
10.0.0.100 - - [03/Dec/2014 13:41:30] "GET /product/51 HTTP/1.1" 200 -

I also went through the code and added a db.session.remove() call before each return in each function using db.session. This does get rid of the in transaction, however, it also causes problems. I pass SQLAlchemy model objects from the database along to the templates. Some templates then do some operations on these model objects that causes the application to fail since the object is no longer attached to a session.

EDIT 2014-12-08:

Connection set up can be seen here:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
from flask_sqlalchemy import SQLAlchemy

from config import cfg

engine = create_engine(cfg["db_uri"], echo=False, pool_size=10)
db = SQLAlchemy()
Base = db.Model
Session = scoped_session(sessionmaker(bind=engine))

The structure of the entire application can be found here: http://paste.yt/p3219.html

What does your sqlalchemy engine or connection code look like? Are you doing anything with autocommit or other options like that? — aezell
– aezell, Commented Dec 5, 2014 at 20:59
I have added SQL Engine setup above. I have also added exerpts from relevant files in order to show the entire structure of the application. If you have any general input or improvements to what I am doing, please let me know :) — Thomas
– Thomas, Commented Dec 8, 2014 at 10:45
Are you using any pooling for postgres itself, like pgbouncer or something? Combining tools like that with SQLAlchemy's pooling can lead to odd connection issues like this. — aezell
– aezell, Commented Dec 8, 2014 at 16:14
I'm at a loss then. Perhaps, session.remove() doesn't actually disconnect the DB connection but just removes the SA session from the current context. The connection may stick around waiting for a new session. Maybe you can explicitly call disconnect() on a connection object? — aezell
– aezell, Commented Dec 15, 2014 at 17:36

David K. Hess · Accepted Answer · 2018-07-30 20:40:52Z

5

I've seen this situation occur when you run Flask in Debug mode. If your code throws an exception and the debugger kicks in, the transaction will never get "rolled back" or "removed". As a result, the session that was used on the request that failed never gets returned to the pool.

The solution is to disable debug mode.

EDIT:

There's another circumstance where I've seen this happen. If you have code that runs autonomously (i.e. not part of an HTTP transaction – like an independent thread started and spawned off at launch of the Flask app), it will usually involve a sleep. If you access the session before the sleep, then you'll end up with a hung transaction like this during the sleep.

Another possibility is you are accessing a session from the create app function. If you do so, make sure to .remove() it. Otherwise, that session could remain hung on the main thread in a gevent app.

edited Jul 30, 2018 at 20:40

answered Mar 23, 2015 at 1:35

David K. Hess

17.3k2 gold badges53 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Thomas Over a year ago

I get this both in debug and non debug mode.

David K. Hess Over a year ago

Have you tried using the latest Flask-SQLAlchemy module? Looks like you are using an older version. Look at this point in the code to see what it automatically does for you: github.com/mitsuhiko/flask-sqlalchemy/blob/master/…

Thomas Over a year ago

I will try that and get back

Thomas Over a year ago

I just updated my venv to most recent version available through pip. The problem is still there :(

zerocog Over a year ago

I am having this problem too. Debug false and latest versions. I thought dispose may help. The answer given here is not helping.

echo · Accepted Answer · 2021-03-23 08:36:12Z

0

from sqlalchemy.pool import NullPool

use the NullPoll as poolclass solved the problem for me. Not sure why.

EDIT(Mar 23 2021):

Despite I got downvote, If using uWSGI, this probably the only choice. Check the sqlachemy official doc

https://docs.sqlalchemy.org/en/14/core/pooling.html#using-connection-pools-with-multiprocessing-or-os-fork

edited Mar 23, 2021 at 8:36

answered Oct 5, 2019 at 3:53

echo

3,1641 gold badge28 silver badges17 bronze badges

Comments

nikk wong · Accepted Answer · 2022-10-04 00:00:05Z

0

In my case I had async functions that started creating connections that would become idle or idle in transaction (based on whether I was using isolation_level=AUTOMATIC or not). They would continue to pile up until the # of db connections would reach the pool_size limit. When I removed the async nature from these handlers, the transactions pooled properly and the connection space no longer clogged itself. ¯\_(ツ)_/¯

@user.route("/<string:user_id>", methods=["GET"])
# async def user_get(user_id): // doesn't work
def user_get(user_id):
    user = db.session.query(User).filter_by(id=user_id).first()
    return jsonify({"data": prl.to_dict()}), 200

answered Oct 4, 2022 at 0:00

nikk wong

8,8386 gold badges60 silver badges72 bronze badges

Collectives™ on Stack Overflow

Flask and SQLAlchemy causes a lot of IDLE in transaction connections in PostgreSQL

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related