sqlalchemy、postgresql 和关系陷入“事务空闲"中 [英] sqlalchemy, postgresql and relationship stuck in "idle in transaction"

问题描述

我有一个与 sqlalchemy 和 postgresql 相关的问题.

I have a problem related to sqlalchemy and postgresql.

class Profile(Base):
  ...

  roles = relationship('Role', secondary=role_profiles,
                       backref='profiles', lazy='dynamic')

运行时(current_user 是 Profile 类的实例):

When running (current_user is an instance of the Profile class):

roles = current_user.roles.filter().all()

使用 sqlalchemy 我得到 idle in transaction 所有选择以读取 postgresql 中的配置文件.

using sqlalchemy I get idle in transaction for all the selects for reading the profile in postgresql.

从回显查询中我看到每个选择都以:

From echoing the query I see that every select starts with:

BEGIN (implicit)

另一个

添加后

pool_size=20, max_overflow=0

对于 create_engine 似乎 idle in transaction 语句在空闲数量变大时正在回滚.对此有任何想法，这会是问题的糟糕解决方案吗?

to the create_engine it seems like the idle in transaction-statements are being rolled back when the number of idle are getting to big. Any idea on this and would this be a bad solution to the problem?

我如何管理这个以及我将如何摆脱选择的 BEGIN ?

How do I manage this and how would I go about getting rid of the BEGIN for selects?

推荐答案

从 SQLAlchemy 0.8.2 开始，您可以在调用 create_engine()<时禁用隐式 BEGIN 语句/p>

Starting with SQLAlchemy 0.8.2 you can disable the implicit BEGIN statements when calling create_engine()

engine = create_engine(uri, isolation_level="AUTOCOMMIT")

这种变化有一些微妙的影响.首先，那些没有悄悄隐藏在未终止交易中的语句将被悄悄忽略

There are some subtle implications to this change. First, there statements that were not quietly hid in unterminated transaction will be quietly ignored

session.execute("DELETE FROM department WHERE department_id=18")
sys.exit(0)

默认:

LOG:  statement: BEGIN
LOG:  statement: show standard_conforming_strings
LOG:  statement: DELETE FROM department WHERE department_id=18
LOG:  unexpected EOF on client connection with an open transaction

自动提交:

LOG:  statement: show standard_conforming_strings
LOG:  statement: DELETE FROM department WHERE department_id=18

其次，更新多个更新不再是自动的，而且rollback()只是有条件的有效:

Second, updating multiple updates are no longer automic, and rollback() is only conditionally effective:

department = Department(u"HR")
session.add(department)
session.flush()
employee = Employee(department.department_id, u'Bob')
session.add(employee)
session.rollback()

默认:

LOG:  statement: BEGIN
LOG:  statement: INSERT INTO department (name) VALUES ('HR') RETURNING department.department_id
LOG:  statement: ROLLBACK

自动提交:

LOG:  statement: INSERT INTO department (name) VALUES ('HR') RETURNING department.department_id

在 Engine 对象上设置 SQLAlchemy 的 isolation_level 对以下情况有效许多应用程序.不幸的是，Session.begin() 并不总是意味着 BEGIN TRANSACTION;

Setting SQLAlchemy's isolation_level on the Engine object is effective for many applications. It unfortunate that Session.begin() does not always mean BEGIN TRANSACTION;

这篇关于sqlalchemy、postgresql 和关系陷入“事务空闲"中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！