Flask和SQLAlchemy在PostgreSQL中的事务连接中导致很多IDLE [英] Flask and SQLAlchemy causes a lot of IDLE in transaction connections in PostgreSQL
问题描述
我有一个使用SQLAlchemy访问PostgreSQL数据库的Flask Web应用程序。
I have a Flask web application that uses SQLAlchemy to access a PostgreSQL database.
启动该应用程序时，立即在其中创建了一个事务中连接PostgreSQL。
When I start the application, there is instantly created an " in transaction" connection in PostgreSQL.
当应用程序使用了一段时间后，其中一些连接会出现在pg_stat_activity中。
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.
我已经读到，如果我在关闭db会话之前从使用数据库的视图函数返回，则会发生这种情况。因此，为了避免出现此问题，我创建了以下装饰器：
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)
这会生成如下日志行：
* 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 -
我还遍历了代码，并在每次返回之前添加了db.session.remove（）调用。使用db.session函数。
这确实摆脱了in事务，但是，这也会引起问题。我将SQLAlchemy模型对象从数据库传递到模板。然后，某些模板会对这些模型对象执行某些操作，导致该应用程序失败，因为该对象不再连接到会话。
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.
编辑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))
整个应用程序的结构可以在以下位置找到： http://paste.yt/p3219.html
The structure of the entire application can be found here: http://paste.yt/p3219.html
推荐答案
我已经看到当您在调试模式。如果您的代码引发异常并且调试器启动，则该事务将永远不会回滚或删除。结果，用于失败请求的会话永远不会返回到池中。
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.
编辑：
在另一种情况下，我看到了这种情况。如果您具有自主运行的代码（即不属于HTTP事务的一部分-就像启动了独立的线程并在启动Flask应用程序时产生了该代码一样），则通常会涉及睡眠。如果您在睡眠之前访问会话，那么在睡眠期间您将最终陷入这样的挂起事务。
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.
另一种可能性是您正在从创建应用程序功能。如果这样做，请确保对其进行.remove（）。否则，该会话可能会挂在gevent应用程序的主线程中。
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.
这篇关于Flask和SQLAlchemy在PostgreSQL中的事务连接中导致很多IDLE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
