SQLAlchemy、PostgreSQL和关系处于“空闲事务”中的困境

Question

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

class Profile(Base):
  ...

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

当运行时（current_user 是 Profile 类的实例）：

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

使用 SQLAlchemy 时，我在读取 PostgreSQL 中的配置文件时，所有选择 (select) 均会显示 idle in transaction。

编辑：

通过回显查询结果，我发现每个选择 (select) 都以相同的方式开始：

BEGIN (implicit)

另一个编辑：

添加后

pool_size=20, max_overflow=0

对于create_engine来说，当空闲连接数达到一定数量时，idle in transaction语句似乎被回滚了。你有什么想法吗？这样做是否是解决问题的不好方法？

我应该如何管理这个问题，并且如何去掉SELECT语句中的BEGIN？

- Asken

---

你尝试过例如 pool_timeout=90 吗？我在这里的文档中找到了它。 - user1593705

您没有正确结束事务。您必须使用 commit 或 rollback 来结束事务。begin 部分是隐含的（正如您所指出的），但需要正确地结束。另外：为什么您有一个空的过滤语句？这有意义吗？您处于 Web 上下文还是其他上下文中？最后，虽然极不推荐，但您可以使用 autocommit 模式（但真的不要这样做）。 - javex

@javex，谢谢。我已经阅读了很多内容，并从事务中获得了大部分收益，但仍有一些来自我的“关系”无法真正触及。Sqlalchemy会自动关闭它们吗？ - Asken

通常情况下，您应该在单个会话中操作。但是，当执行 current_user.roles.filter().all() 时，我不明白您试图实现什么。为什么不只是执行 current_user.roles？这将返回一个列表，并自动在 current_user 所属的会话上下文中执行（例如，如果您像这样查询：session.query(User).filter(User.id == some_id).one()，则所有这些都将自动处理）。 - javex

实际上，如果您查看关系，您会发现它是 lazy='dynamic'，这意味着它尚未查询，但需要进行查询。 - Asken

Answer 1

从SQLAlchemy 0.8.2开始，您可以在调用create_engine()时禁用隐式的BEGIN语句。

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

这个变化有一些微妙的影响。首先，在未终止的事务中没有悄悄隐藏的语句将被悄悄忽略。

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()仅在特定条件下有效：

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

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

- eradman

Answer 2

默认情况下，SQLA始终在事务中运行（一些信息在此处）。在Web上下文中，大多数框架会在请求结束时为您处理提交该事务（例如pyramid_tm）。如果您没有使用框架，或者这是另一种类型的应用程序，则应在完成或适当时刻提交或回滚。可能可以配置SQLA，使其不会自动开始事务，但据我所见，这不是其预期使用方式，因此最好不要试图与之抗争：）。

- Daniel