Flask and SQLAlchemy invoke many IDLEs in transactional relationships in PostgreSQL

Question

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

When I launch the application, PostgreSQL immediately creates a "in transaction" connection.

When an application is used for a while, some of these connections appear in pg_stat_activity.

After some time, it seems that deadlocks occur on some resources, and I have to restart the application for it to work again.

I read that this can happen if I return from the view function, which uses the database, before closing the db session. Therefore, to avoid this problem, I created the following decorator:

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

This should lead to the closure of all sessions after each request and effectively avoid connection problems "in transaction".

Unfortunately, this has no effect.

So how do I really solve this problem?

UPDATE:

I must add that I confirmed that my decorator function is actually running. I checked 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 also checked that it really starts AFTER the view function returns, adding print to the view function:

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

This creates 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 looked at the code and added a call to db.session.remove () before each return in each function using db.session. This eliminates the need for a transaction, but it also causes problems. I am passing SQLAlchemy model objects from the database along with the templates. Some templates then perform some operations on these model objects, which causes the application to crash because the object is no longer attached to the session.

EDIT 2014-12-08 :

Connection setup is given 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 saw this situation when you run Flask in debug mode . If your code throws an exception and the debugger is turned on, the transaction will never be rolled back or deleted. As a result, the session that was used for the failed request never returns to the pool.

The solution is to disable debugging mode.

EDIT:

There is one more circumstance when I saw how this happened. If you have code that runs autonomously (that is, is not part of the HTTP transaction — as an independent thread that starts and spawns when the Flask application starts), it usually includes hibernation. If you gain access to the session before bedtime, then during sleep you will end up with a frozen transaction.

Another possibility is access to the session from the application creation function. If you do, be sure to .remove () it. Otherwise, this session may remain in the main thread in the gevent application.

Answer 2

 from sqlalchemy.pool import NullPool 

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