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

Answer 1

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.